I passed a variable thru the URL to 1

[demopro]

I passed a variable thru the URL to another asp page and now want to insert it into a SQL 7 stored procedure and I continue to receive this message:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'NewVariable'.

I use the normal:
NewVariable = Request.QueryString("VariableBeingPassed&quot
to capture it and assign it to another variable that I then insert in a stored procedure:

rs.Open "sp_CustomQuery ('" & NewVariable & "')", cn

I am lost for answers on this for I have also tried to use the trim and replace functions on this and it continues to give me headaches.

Any help would greatly be appreciated.
Thanks,
Jim

 

[forecasting]

Excuse if I am off target, but I don't think you are using the correct syntax to execute a stored procedure

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

cn.Provider = "sqloledb"
cn.open session("ConnectString&quot

Set cmd.ActiveConnection = cn
cmd.CommandText = "myProc"
cmd.CommandType = adCmdStoredProc

Set param = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append param
param.Value = NewVariable

Set rs = cmd.Execute

 

[demopro]

Thanks for the response but I Reference the Session connection variable as well as the record set variable thru the global.asa file and on the individual pages (.asp) I just make a reference to them so I would not have to use redundant code.

I am not having a problem with the way I am using the stored procured rather, the variable I am trying to pass it.

Any help is greatly appreciated.

 

[forecasting]

Just a guess.
cn.execute "sp_CustomQuery ('" & NewVariable & "')"

 

[JuanitaC]

Have you written out your variable to ensure that it is the value that you expect it to be?

Try this:

NewVariable = Request.QueryString("VariableBeingPassed&quot
Response.write(&quot;var is &quot; & NewVariable & &quot;<br>&quot

Response.write (&quot;Query is sp_CustomQuery ('&quot; & NewVariable & &quot;')<br>&quot
rs.Open &quot;sp_CustomQuery ('&quot; & NewVariable & &quot;')&quot;, cn

 

[demopro]

Thanks forecasting but that did not work.

JuanitaC I have done that and it show on the page as getting the variable that I am sending it but for some reason it continues to error on me. This is not the first time that I have used this method and I have checked it against the others and the syntax is correct but for some reason it does not like the variable being passed no matter what I pass it.

 

[JuanitaC]

When you print out the query line, copy that and see if it will run in your database (exactly as it prints on the screen).

Also, try taking the () out:
rs.Open &quot;sp_CustomQuery '&quot; & NewVariable & &quot;'&quot;, cn

 

[demopro]

You rock, for some reason it worked by taking off the () around the call. Thank you, thank you and thank you.

Now I just have to figure out why this worked out this way and the others worked the old way...


Again you have my thanks. If you every need my help just drop me a line.

Jim

 

[JuanitaC]

No prob... glad I could help.