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

Fri Feb 7 16:51:01 CST 2003

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.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com