[AccessD] Sql 7/Access 2K - Stored Procedure Problem ...

Tom Adams tomadatn at bellsouth.net
Fri Feb 7 18:20:00 CST 2003


Thanks to all who've questioned and answered Stored Procedure Questions over the past year.  I've just finished a day of fighting to
do the simplest thing, get a recordset from a parametized stored procedure and display it in a form.  A long - hard - tiring day.

It's amazing how many little things trip you up.  A lot of the advice reminded me of snow skiing.  A new skier asks where he can
find an easy slope, the experienced skiier tells him.  After about 20 crash and burns the new skier gets to the bottom with fire in
his eyes.  The experienced skier doesn't understand the broken arm, collar bone, twisted knee and scrapes and bruises.  It was just
an easy slope.

Anyway here's my final code on the Access 2000 side to display records in datasheet view in a subform, with the form having fields
for the parameters.  Hope this helps someone else to do in  15 minutes what took me all day.


Tom (Now where's that ambulance???) Adams



'------------------------------------------ form code
Private Sub cmdTest_Click()
'on error resume next  'finish no

'this is to test calling a stored procedure

' Open connection.
Dim jcnn As ADODB.Connection
Set jcnn = New ADODB.Connection

jcnn.Open CurrentProject.Connection
jcnn.CursorLocation = adUseClient

Dim jAdoRs As ADODB.Recordset

Dim cmd As ADODB.Command

If 1 = 2 Then
   DoCmd.OpenStoredProcedure "tsp_Test3_Lev1_sp", acViewNormal, acReadOnly
Else
   Set cmd = New ADODB.Command

   Set cmd = New ADODB.Command
   With cmd
       .CommandText = "tsp_Test3_Lev1_sp"
       .CommandType = adCmdStoredProc

       .Parameters.Append .CreateParameter("@ViewTable", adVarWChar, adParamInput, 200, Trim(Me.scnViewTableName))
       .Parameters.Append .CreateParameter("@Where", adVarWChar, adParamInput, 200, Trim(Me.scnWhere))
       .Parameters.Append .CreateParameter("@Order", adVarWChar, adParamInput, 200, Trim(Me.scnOrder))

       .ActiveConnection = jcnn
       Set jAdoRs = .Execute

       Set Me.subfrm_Detail.Form.Recordset = jAdoRs

       .ActiveConnection = Nothing
   End With

   Set cmd = Nothing

End If

Beep


End Sub



'-------------------- sql stored procedure
CREATE Procedure tsp_Test3_Lev1_sp
@ViewTable     nvarchar(200),
@Where  nvarchar(200),
@Order  nvarchar(200)

output As

DECLARE @SQLString NVARCHAR(200)

set @SqlString = N'Select * from ' + @ViewTable + N' ' + @Where + N' ' + @Order

Exec sp_ExecuteSQL @SQLString

return


----- Original Message -----
From: "James Barash" <James at fcidms.com>
To: <accessd at databaseadvisors.com>
Sent: Friday, February 07, 2003 4:50 PM
Subject: RE: [AccessD] Sql 7/Access 2K - Stored Procedure Problem ...


> Tom,
> There are a few things I would check. First, to pass a varchar
> parameter, you need to declare the length as part of the parameter. You
> can also initialize the parameters when you create them so try:
>
> If 1 = 2 Then
>    DoCmd.OpenStoredProcedure "tsp_Test3_Lev1_sp", acViewNormal,
> acReadOnly Else
>    Set cmd = New ADODB.Command
>     With cmd
>         .CommandText = "tsp_Test3_Lev1_sp"
>         .CommandType = adCmdStoredProc
>         .ActiveConnection = CurrentProject.Connection
>         .Parameters.Append .CreateParameter("@ViewTable", adVarChar,
> adParamInput,200,Trim(Me.scnViewTableName))
>         .Parameters.Append .CreateParameter("@Where", adVarChar,
> adParamInput,200,Trim(Me.scnWhere))
>         .Parameters.Append .CreateParameter("@Order", adVarChar,
> adParamInput,200,Trim(Me.scnOrder))
>         .Execute
>         .ActiveConnection = Nothing
>     End With
>     Set cmd = Nothing
> End If
>
>
> Also, in the procedure, you declare @Order as an output parameter but
> you are passing it as an input parameter. Since you don't return @Order,
> change the procedure declaration to:
>
> CREATE Procedure tsp_Test3_Lev1_sp @ViewTable  nvarchar(200), @Where
> nvarchar(200), @Order  nvarchar(200) As ...
>
>
> Hope that helps.
>
> James Barash
>
>
> -----Original Message-----
> From: accessd-admin at databaseadvisors.com
> [mailto:accessd-admin at databaseadvisors.com] On Behalf Of Tom Adams
> Sent: Friday, February 07, 2003 4:56 PM
> To: AccessD at databaseadvisors.com
> Subject: [AccessD] Sql 7/Access 2K - Stored Procedure Problem ...
>
>
> I'm trying to run the code below in a form sub after inputting the
> parameter values in text boxes. I keep getting the error message:
> "Parameter object improperly defined, Inconsistent or Incomplete info
> provided."
>
> 1.  I can double click on the SP in an Access 2K ADP and it works 2..
> The docmd action works 3.  The command object doesn't work.
>
> I've fought this beast for hours.  Any help greatly appreciated.
>
> Thanks - Tom
>
>
> Here's my code ------------------------------
> Dim cmd As ADODB.Command
>
> If 1 = 2 Then
>    DoCmd.OpenStoredProcedure "tsp_Test3_Lev1_sp", acViewNormal,
> acReadOnly Else
>    Set cmd = New ADODB.Command
>     With cmd
>         .CommandText = "tsp_Test3_Lev1_sp"
>         .CommandType = adCmdStoredProc
>         .ActiveConnection = CurrentProject.Connection
>
>         .Parameters.Append .CreateParameter("@ViewTable", adVarChar,
> adParamInput)
>         .Parameters.Append .CreateParameter("@Where", adVarChar,
> adParamInput)
>         .Parameters.Append .CreateParameter("@Order", adVarChar,
> adParamInput)
>
>         .Parameters("@ViewTable") = Trim(Me.scnViewTableName)
>         .Parameters("@Where") = Trim(Me.scnWhere)
>         .Parameters("@Order") = Trim(Me.scnOrder)
>
>         .Execute
>         .ActiveConnection = Nothing
>     End With
>     Set cmd = Nothing
> End If
>
> '--------------------------------------- here's the stored procedure
> CREATE Procedure tsp_Test3_Lev1_sp @ViewTable  nvarchar(200), @Where
> nvarchar(200), @Order  nvarchar(200)
>
> output As
>
> DECLARE @SQLString NVARCHAR(200)
>
> -- -----------------------------------------------------------
>
> set @SqlString = N'Select * from ' + @ViewTable + N' ' + @Where + ' ' +
> @Order
> --  test 1 -----------------------------------------
> if 1 = 2
> if  @Where    > ''
>    if @Order > ''
>       SET @SQLString = N'SELECT * from W01_03a1_CostOfRevenue_Detail_vw
> ' +  @Where + N'  ' + @Order
>    else
>       SET @SQLString = N'SELECT * from W01_03a1_CostOfRevenue_Detail_vw
> ' +  @Where + N'  order by summary_code,  seg1_code, seg3_code Desc,
> seg4_code'
>    -- endif order > '' --------------------------------
>
> else  -- sum cd = '' --------------------------
>    SET @SQLString = N'SELECT * from W01_03a1_CostOfRevenue_Detail_vw  '
> + N' order by  summary_code Desc, seg1_code,  seg2_code, seg3_code,
> seg4_code'
> --  endif  @Where = ''  --------------------------
>
> Exec sp_ExecuteSQL @SQLString
>
> return
> '-----------------------------------------------------------------------
> -
>
>
>
>
>
>
>
> ----- Original Message -----
> From: "Mike and Doris Manning" <mikedorism at ntelos.net>
> To: <AccessD at databaseadvisors.com>
> Sent: Tuesday, January 21, 2003 8:58 AM
> Subject: RE: [AccessD] Calling A SQL Server Stored Procedure from VB6
>
>
> You need to work with an ADO Connection object and an ADO Command
> object. Here is a sample of how I call a stored procedure from Access
> using ADO 2.7:
>
>     Set cmd = New ADODB.Command
>     With cmd
>         .CommandType = adCmdStoredProc
>         .CommandText = "ap_TM_TaskRead"
>         .Parameters.Append .CreateParameter("@TaskID", adInteger,
> adParamInput)
>         .Parameters.Append .CreateParameter("@TaskRead", adBoolean,
> adParamInput)
>         .Parameters("@TaskID") = oTM.TaskID
>         .Parameters("@TaskRead") = True
>         .ActiveConnection = CurrentProject.Connection
>         .Execute
>         .ActiveConnection = Nothing
>     End With
>     Set cmd = Nothing
>
> Doris Manning
> Database Administrator
> Hargrove Inc.
> www.hargroveinc.com
>
>
> -----Original Message-----
> From: AccessD-owner at databaseadvisors.com
> [mailto:AccessD-owner at databaseadvisors.com] On Behalf Of
> paul.hartland at fsmail.net
> Sent: Tuesday, January 21, 2003 09:37 AM
> To: accessd at databaseadvisors.com; dba-VB at databaseadvisors.com
> Subject: [AccessD] Calling A SQL Server Stored Procedure from VB6
>
>
> To all,
>
> Please forgive me not cross-posting this to the SQL Group,but I keep
> getting rejected from it and can't seem to log onto
> www.databaseadvisors.com, to re-enlist.
>
> I have a (INSERT) Stored Procedure in SQL Server 7.0 which requires
> about 5 or 6 parameters to be passed to it.  As I have never called a
> Stored Procedure before from Visual Basic 6.0, I wonder if someone had
> some sample code on how to Call and pass the parameters to the Stored
> Procedure....
>
> Thanks in advance.....
>
> Paul Hartland
>
> ________________________________________________________________________
> __
> Freeserve AnyTime - Go online whenever you want for just £6.99 a month
> for your first 3 months, that's HALF PRICE! And then it's just £13.99 a
> month after that.
>
> For more information visit http://www.freeserve.com/time/ or call free
> on 0800 970 8890
>
>
> This list sponsored by Database Advisors Inc., a worldwide association
> of database developers. Visit http://www.DatabaseAdvisors.com, the
> database developers' list portal and support site.
>
> This list sponsored by Database Advisors Inc., a worldwide association
> of database developers. Visit http://www.DatabaseAdvisors.com, the
> database developers' list portal and support site.
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com




More information about the AccessD mailing list