Struggling with A2002 and SQL Server Stored Procedures

[Kevin_S]

OK - now I'm confused...

See the attached screenshot. I have nVarChar as a datatype setting in SQL Server 2000. Here is my code:

Private Sub Command4_Click()
Call EstablishConnection

Set objCmd = New ADODB.Command
objConn.CursorLocation = adUseClient

With objCmd
.ActiveConnection = objConn
.CommandText = "sproc_TEST" 'name of sproc
.CommandType = adCmdStoredProc 'its a stored procedure
.Parameters.Append .CreateParameter("@SubmissionID", nVarChar, adParamInput, 8, Me.txts) 'some parameters
.Parameters.Append .CreateParameter("@Project_Name", nVarChar, adParamInput, 50, Me.txtp) 'some parameters
Set rst = .Execute
Set rst.ActiveConnection = Nothing
End With
Call ReleaseConnection 'release the connection to the db
End Sub

This can't be THIS hard can it...?

Thanks,
Kev

 

[mission2java_78]

Try this:

Private Sub Command4_Click()
Call EstablishConnection

Set objCmd = New ADODB.Command
objConn.CursorLocation = adUseClient

With objCmd
.ActiveConnection = objConn
.CommandText = "sproc_TEST" 'name of sproc
.CommandType = adCmdStoredProc 'its a stored procedure
.Parameters.Append .CreateParameter("SubmissionID", adVarChar, adParamInput, 8, Me.txts) 'some parameters
.Parameters.Append .CreateParameter("Project_Name", adVarChar, adParamInput, 50, Me.txtp) 'some parameters
Set rst = .Execute
Set rst.ActiveConnection = Nothing
End With
Call ReleaseConnection 'release the connection to the db
End Sub

Dont store the @ symbols in the parameters..thats in SQL Server not your client.

Jon

 

[Kevin_S]

OK! Now we're gettin' somewhere!!!

I implemented your code as you had it and it successfully ran the sproc but it errored out on the line:

Set rst.ActiveConnection = Nothing

and gave me the following error:

Error 3707
Cannot change the ActiveConnection property of a recordset object which has a command object as its source

Soo... I changed the code to this:

Call EstablishConnection

Set objCmd = New ADODB.Command
objConn.CursorLocation = adUseClient

With objCmd
.ActiveConnection = objConn
.CommandText = "sproc_TEST" 'name of sproc
.CommandType = adCmdStoredProc 'its a stored procedure
.Parameters.Append .CreateParameter("SubmissionID", adVarChar, adParamInput, 8, Me.txts) 'some parameters
.Parameters.Append .CreateParameter("Project_Name", adVarChar, adParamInput, 50, Me.txtp) 'some parameters
.Execute
End With
Call ReleaseConnection 'release the connection to the db

and it ran/inserted without error My question now is:
1) did the error originally occur because rst wasn't dimentioned in the code?
2) Do I use adParamOutput to return results from the sproc and can I use this result to populate a recordset?

I think once I get these two question under my belt I'll have a solid foundation and be able to start experimenting/continue learning on my own...

You rock Jon! I appretiate you stickin' with me though this...
Kev

 

[mission2java_78]

.Execute by itself will work fine...on insertions...

However,

how are you going to handle selects?
No an output parameter is not used for the return..your record set is used for your return.

You have to distinguish between what your doing in a stored procedure.

For instance, if all you want to do is a DELETE, UPDATE, Or an INSERT..than you dont need to retreive a recordset right? In that case your code should just apply .Execute.

If you need a recordset than you need the SET RST = .EXECUTE...

Go ahead try my example on a SELECT SPROC.



Jon

 

[mission2java_78]

For UPDATES / DELETES / INSERTIONS...

use code like this:

   Screen.MousePointer = vbHourglass
   Call EstablishConnection
   Set objCmd = New ADODB.Command
 
    With objCmd
        .ActiveConnection = objConn
        .CommandText = "update_customer"       'our stored procedure is good_login
        .CommandType = adCmdStoredProc         'its a stored procedure
        .Parameters.Append .CreateParameter("CustomerID", adBigInt, adParamInput, , Me.txtCustomerID.Text)
        .Parameters.Append .CreateParameter("Name", adVarChar, adParamInput, 50, Me.txtCustomerName.Text)
        .Parameters.Append .CreateParameter("Abbreviation", adVarChar, adParamInput, 20, Me.txtCustomerAbbreviation.Text)
        .Parameters.Append .CreateParameter("CustomerDescription", adVarChar, adParamInput, 500, Me.txtCustomerDescription.Text)
        .Execute
    End With

because you dont need a recordset

For SELECTS use code like this:

Dim objCmd As ADODB.Command
Dim rst As ADODB.Recordset

Call EstablishConnection

Set objCmd = New ADODB.Command
objConn.CursorLocation = adUseClient

    With objCmd
        .ActiveConnection = objConn
        .CommandText = "select_customers_by_customer_id"
        .CommandType = adCmdStoredProc         'its a stored procedure
        Set rst = .Execute
        Set rst.ActiveConnection = Nothing
        Call ReleaseConnection
    End With

Now you have rst .. some records..now use your normal

if rs.bof then
'no records
else
'with records
while not rs.eof
'blah
wend
rs.movenext
end if

 

[Kevin_S]

one word... NICE

I'm off and running... if your ever in Mid-Michigan I'm buyin'!!!

Thanks a ton,
Kev

 

[mission2java_78]

Knew you could do it.

Good job