Need help passing multiple parameters to SQL stored procedure.

[uncleroydee]

I'm trying to execute a stored procedure with multiple input parameters on a SQL database, but it keeps returning errors.

The problem lies in the parameter values, I need to use values submitted from a preceding form page. I know the preceding page is submitting the values correctly because later in the page I have a verification message that "writes" the values in a confirmation message.

My actual command text follows:


<% SET Con=Server.CreateObject(&quot;ADODB.Connection&quot
SET Cmn=Server.CreateObject(&quot;ADODB.Command&quot
Con.Open &quot;DSN=S_S;UID=sa;Password=;&quot;
Cmn.ActiveConnection=Con
Cmn.CommandType=adCmdStoredProc
Cmn.CommandText=&quot;SP_SURGEDB_SOL_TST_INS&quot;
Cmn.Parameters.Append
Cmn.CreateParameter(&quot;SNBRAGENCY&quot;,adChar,adParamInput,6)
Cmn.Parameters.Append Cmn.CreateParameter (&quot;SNBRYr&quot;,adChar,adParamInput,2,&quot;&amp; SNBRYr &amp;&quot
Cmn.Parameters.Append Cmn.CreateParameter(&quot;SNBRRFQTYPE&quot;,adChar,adParamInput,1,&quot;&amp; SNBRRFQTYPE &amp;&quot
Cmn.Parameters.Append Cmn.CreateParameter(&quot;SNBRRFQISSUEORDER&quot;,adChar,adParamInput,4,&quot;&amp; SNBRRFQISSUEORDER &amp;&quot Cmn.Parameters&quot;SNBRAGENCY&quot;= Value=&quot; &amp; SNBRAGENCY &amp; Cmn.Parameters&quot;SNBRYr&quot;= Response.Write &amp; SNBRYR &amp;
Cmn.Parameters&quot;SNBRRFQTYPE&quot;= Response.Write &amp; SNBRRFQTYPE &amp;
Cmn.Parameters&quot;SNBRRFQISSUEORDER&quot;= Response.Write &amp; SNBRRFQISSUEORDER &amp;
Cmn.Execute %>

I'd be greatful for any insight you can provide and I wish you a very happy holiday (of your choice) ;-)

 

[culshaja]

In the CreateParameter statement you issue:

Cmn.Parameters.Append Cmn.CreateParameter    ("SNBRYr",adChar,adParamInput,2,"& SNBRYr &")

You don't need the &quot;&amp; bits. Issue the following:

Cmn.Parameters.Append Cmn.CreateParameter    ("SNBRYr",adChar,adParamInput,2,SNBRYr)

This assumes that SNBRYr is a variable.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[uncleroydee]

Thanks, James.

What about the following parameter syntax??

Cmn.Parameters&quot;SNBRAGENCY&quot;= Value= &amp; SNBRAGENCY &amp; Cmn.Parameters&quot;SNBRYr&quot;= Response.Write &amp; SNBRYR &amp;
Cmn.Parameters&quot;SNBRRFQTYPE&quot;= Response.Write &amp; SNBRRFQTYPE &amp;
Cmn.Parameters&quot;SNBRRFQISSUEORDER&quot;= Response.Write &amp; SNBRRFQISSUEORDER &amp;

 

[culshaja]

I think you have got a bit confused. Could you spell out what it is you are trying to do - where is the data coming from and what you are trying to do with it?

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305