Passing variables to stored procedure with Classic ASP

Question

Ok so I am trying to pass some string variables from a classic ASP page to an MSSQL2000 db thusly:

strSQL = "exec UpdateEvent " & xID & ",'" & xEventID & "'," & xEventDisplayName & "," & xEventType & "," & xEventStatus & "," & xStartDate & "," & xEndDate & "," & xSurveyTemplateID & ""

Yet I end up with the error (including writing out the strSQL contents):

exec UpdateEvent 1,'1-44KTDL',,,,,,

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near ','.

/eventedit.asp, line 225

Now I am not sure if it is the dash in the EventID variable that is causing my problems (or why all the other variables are coming up with null values when there is data there...) . I have tried many many combinations of quotes and tics to appease the syntax interpreter but to no avail. What am I doing wrong? Is there a better way to do this simple stored procedure call?

Answer 1

If no data exists for those variables you need to at least put NULLs in there otherwise the SQL will fail.

For example if you paste

exec UpdateEvent 1,'1-44KTDL',,,,,, 

directly into Query Analyzer, you will get the same error.

Ideally you will need to load them into a parameter array, and create defaults for each parameter (e.g. NULL) in case no data is present for it.

This will ensure your Stored Procedure is built correcly.

e.g.

 exec UpdateEvent 1,'1-44KTDL', NULL, NULL, NULL, NULL, NULL, NULL

Answer 2

That's very VERY bad; your code is subject to SQL injection attacks and needs to be fixed as soon as possible.

<!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->
<%
Set cmd = Server.CreateObject("ADODB.Command")
' ... open connection and stuff ... '
cmd.CommandText = "UpdateEvent"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

cmd.Parameters(1) = xID 
cmd.Parameters(2) = xEventID 
cmd.Parameters(3) = xEventDisplayName 
cmd.Parameters(4) = xEventType 
cmd.Parameters(5) = xEventStatus 
cmd.Parameters(6) = xStartDate 
cmd.Parameters(7) = xEndDate 
cmd.Parameters(8) = xSurveyTemplateID
cmd.Execute
%>

Answer 3

How about this:

http://support.microsoft.com/kb/164485

Answer 4

I would suggest that you capture the SQL being generated in your code > run that SQL manually in the database > see if you can spot the problem.

Set a breakpoint in your code where strSQL is populated. Step over that line. Get the value of strSQL at that point.

This may help you to identify the problem, which may be in your syntax or may arise from unexpected values in the parameter variables.