jgbader
asked on
Problem calling SQL Server stored procedure from classic ASP
Hi, I'm trying to call a SQL Server 2005 stored procedure from a classic ASP page using ADO. I have verified that the connection string is correct, the user login has permissions to execute the procedure in question, and that the procedure itself functions properly. I can reproduce the results in the ASP page by passing in hard-coded SQL. However, when I call .Execute, I get this error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
The relevant code snippet is below:
Dim Cmd : Set Cmd = Server.CreateObject("ADODB .Command")
Dim root
With Cmd
.ActiveConnection = Cn
.CommandType = adCmdStoredProc
.CommandText = "getNAIC-ID"
.Parameters.Append .CreateParameter("@naic_co de", adVarChar, adParamInput, 15, Session("SalesImaging_Comp anyNum"))
.Parameters.Append .CreateParameter("@naic_id ", adInteger, adParamOutput, ,0)
.Execute ,,adExecuteNoRecords
root = .Parameters("@naic_id")
End With
Sorry if I'm missing the obvious - thanks!
Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation
The relevant code snippet is below:
Dim Cmd : Set Cmd = Server.CreateObject("ADODB
Dim root
With Cmd
.ActiveConnection = Cn
.CommandType = adCmdStoredProc
.CommandText = "getNAIC-ID"
.Parameters.Append .CreateParameter("@naic_co
.Parameters.Append .CreateParameter("@naic_id
.Execute ,,adExecuteNoRecords
root = .Parameters("@naic_id")
End With
Sorry if I'm missing the obvious - thanks!
What line do you get the error on?
.CommandText = "getNAIC-ID"
I think the error is due to the " - " in your stored porcedure name . Please change the SP name, as for eg. "getNAIC_ID" and check it.
I think the error is due to the " - " in your stored porcedure name . Please change the SP name, as for eg. "getNAIC_ID" and check it.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
angellll,
That worked perfectly. As a matter of curiosity, is there a particular reason why those brackets should matter? I've never needed them before. Of course, all I've used before is .NET web apps, not classic ASP.
Thanks!
That worked perfectly. As a matter of curiosity, is there a particular reason why those brackets should matter? I've never needed them before. Of course, all I've used before is .NET web apps, not classic ASP.
Thanks!
savitha actually gave you the reason. the "-" would be interpreted, without the [] around the name, as math operation...
ASKER
Indeed. A math operation? I hadn't thought of that. Thanks again!