Link to home
Create AccountLog in
Avatar of jgbader
jgbaderFlag for United States of America

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_code", adVarChar, adParamInput, 15, Session("SalesImaging_CompanyNum"))
  .Parameters.Append .CreateParameter("@naic_id", adInteger, adParamOutput, ,0)
  .Execute ,,adExecuteNoRecords
  root = .Parameters("@naic_id")
End With

Sorry if I'm missing the obvious - thanks!
Avatar of slamhound
slamhound

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.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of jgbader

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!
savitha actually gave you the reason. the "-" would be interpreted, without the [] around the name, as math operation...
Avatar of jgbader

ASKER

Indeed.  A math operation?  I hadn't thought of that.  Thanks again!