Stored procedure error using ADO/ODBC connection - help? 
Author Message
 Stored procedure error using ADO/ODBC connection - help?

Hello Vic,

when using ADO and stored procedures with parameters, you want to use the
ADO command object. Please see the MSDN ADO reference for details. Here's
how you could modify your code:

    Dim cn As ADODB.Connection
    Dim co As ADODB.Command
    Dim params(3) As ADODB.Parameter

    Dim s As String

    Set cn = New ADODB.Connection
    Set co = New ADODB.Command

    cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=Address"

    Set co.ActiveConnection = cn

    Set params(0) = co.CreateParameter("CustomerID", adInteger,
adParamInput, , 1)
    co.Parameters.Append params(0)
    Set params(1) = co.CreateParameter("CustomerName", adChar, adParamInput,
10, "Peter Miller")
    co.Parameters.Append params(1)
    Set params(2) = co.CreateParameter("AreaCode", adChar, adParamInput, 10,
    co.Parameters.Append params(2)

    co.CommandText = "UpdateCustomer"
    co.CommandType = adCmdStoredProc



This would call the following SP:



The ADO command object provides better capabilities to call stored
procedures with both input and output parameters. It requires a bit more
coding, though.

Uwe Baumann

> Hi -

> I'm new to ADO/VB and have had great success getting data _out_ of my
> database, but I'm now trying to put it back in via stored procs and am
> getting
> syntax errors I can't seem to debug. The code snippet I am calling is

> Dim sAC As String

>   If (bEditMode = True) Then
>     bEditMode = False
>     If txtAreaCode.Text = "" Then
>       sAC = "NULL"
>     Else
>       sAC = txtAreaCode.Text
>     End If

>      strSQL = "SP_UpdateCustomer " & lblCustID.Caption & ", '" &
>                      txtCoName.Text & "'," & sAC
>      cn.Execute strSQL, , adCmdStoredProc

> The strSQL reads as: SP_UpdateCustomer 1, 'Boeing North America,
> Inc.aaaaasdfsdf',NULL

> This command _works fine_ when I execute it in ISQL/w but bombs when I
> it from my code. It also works if I only pass one parameter (company
> When
> I add the second parameter, it dies! Any information very much
> I don't
> have much hair left! TIA -

> Vic Bachulis
> (sm)

> remove the "nospam_" to reply, please.

Fri, 14 Jun 2002 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Error calling Oracle store procedures using ADO

2. Handling Messages from Stored Procedure Using ADO Error Object

3. ADO error -2147217871 when using stored procedure

4. Help How To Sort A Stored Procedure Using The Ado Data Control

5. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

6. Help in calling JDE stored procedures from VB using ADO

7. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

8. ado recordsets from stored procedures, stored procedures have input parameters

9. HELP ==>Oracle Stored Procedure using ADO(VB)

10. HELP ==>Oracle Stored Procedure(PACKAGE) using ADO(VB)

11. Stored procedure with VB6, ADO, ODBC, and Informix 7.2

12. ADO Connection and Stored Procedures


Powered by phpBB® Forum Software