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