ADO stored procedure problems

[SIG357]

I'm trying to execute a SQL Server 7 stored procedure from my ASP script but I keep getting the following error msg -"(0x80040E14)
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation"
The stored procedure returns an integer status code that I'm trying in vain to retrieve.

Here's the script I'm trying to run:

objCmd.ActiveConnection = objConn
objCmd.CommandText = "sp_insert_ResumeDetail " & strParamList
objCmd.CommandType = 4 'adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("RETURN_VALUE", 3, 4, 0)

Set objRec = objCmd.Execute '<----- Error's here
If Not objRec.EOF Then
For Each adoField In objRec.Fields
Response.Write adoField.Name & &quot;=&quot; & adoField.Value & &quot;<br />&quot;
Next
End If

If I use the Execute method of the objConn Connection object, the SP runs fine, so I know it's not a syntax error, but then I can't get the return value.

Anything obvious that I'm doing wrong?

 

[harmmeijer]

Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim intInteger As Integer
conn.ConnectionString = &quot;...&quot;
conn.Open
With comm
.ActiveConnection = conn
.CommandText = &quot;mysproc&quot;
.CommandType = adCmdStoredProc
'Input parameters
.Parameters.Append .CreateParameter(&quot;@number&quot;, adInteger, adParamInput, , 22)

'Output parameters
.Parameters.Append .CreateParameter(&quot;@outnum&quot;, adInteger, adParamOutput)
' I am not sure what the asp equivilant is of execurenorecords but you shouldn't try to fill a recordset because executing this command does not return a recordset
.Execute , , ADODB.adExecuteNoRecords
' now return the output parameter
intInteger = .Parameters(&quot;@outnum&quot
MsgBox &quot;The out parameter is: &quot; & intInteger
End With
Set comm = Nothing
conn.Close
Set conn = Nothing

 

[SIG357]

harmmeijer -
Thanks for the tip, that makes more sense (all the examples I had seen used the recordset method I was trying), but I'm still getting the same error. I used your method trying to retrieve return values and, alternately, output parameters, but I get the same error. Any more ideas?
Thanks

 

[harmmeijer]

Wel, the only thing I could find on the Internet is that it could be a syntax error.
Is the strParamList filled out with a submitted form? and if so could you make that a string to test if executing on the connection realy doesn't produce the same error.

You do not need to provide the output parameter with a value, allthough I do not think this caused the error.
objCmd.CreateParameter(&quot;RETURN_VALUE&quot;, 3, 4, 0)

 

[harmmeijer]

Can you use the sql server profiler to start a trace (SQLProfilerTSQL).
when you start the trace you are able to see how/if the command is executed BY SQL SERVER when you execute it with a command object and when you execute it with a connection object.

 

[SIG357]

When I execute it with a connection object, the trace shows the SP executing as it should, no errors. However, when I try it using the Command object, it doesn't even show up in the trace log, so it's not even getting to the database...
Thanks again, any other ideas?

 

[SIG357]

I tried playing with the code a bit, and put in a objCmd.Parameters.Refresh before the execute, and now I get the same 'Syntax error or access violation' on the Refresh command, so I'm guessing there's nothing wrong with the actual stored procedure, but with the command object itself...

- Stuck

 

[SIG357]

Figured it out -
I was trying to pass the parameters in the the command text of the command object rather than using the parameters collection. This worked fine for the connection object, as it just passes the text straight to the SQL engine, but it didn't work for the command object, because I needed to populate the parameters collection. Thanks for the helpm though