Passing of 2 or More Parameters to Store Proc. From ASP 1

[Creeder]

Hi,<br><br>I've created a SP on SQL server which requires 2 parameters.<br><br>Here is part of the code in ASP<br>&nbsp;<br>&nbsp;objComm.CommandText = &quot;select_test&quot;<br>&nbsp;objComm.CommandType = adCmdStoredProc<br><br>&nbsp;&nbsp;Set objParam = _ <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objComm.CreateParameter(&quot;@Parameter1&quot;, adVarChar, adParamInput, 50)<br><br>&nbsp;&nbsp;Set objParam2 = objComm.CreateParameter(&quot;@Parameter2&quot;,adVarChar,adParamInput,50) <br>&nbsp;&nbsp;<br>&nbsp;&nbsp;objComm.Parameters.Append objParam<br>&nbsp;&nbsp;objComm.Parameters.Append objParam2<br><br>&nbsp;&nbsp;str1 = &quot;Test&quot;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;str2 = &quot;Test 2&quot;<br>&nbsp;&nbsp;<br>&nbsp;&nbsp;objComm.Parameters(&quot;@Parameter1&quot = str1 <br>&nbsp;&nbsp;objComm.Parameters(&quot;@Parameter2&quot = str2<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;Set objRS = objComm.Execute&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br><br>And everything works fine.<br><br>My questions : Is there another more efficient way of doing this? It's fine if i am passing 2 parameters, but let's say if i am passing 10 parameters? <br><br>I am new to ASP and any help is much appreciated. <br><br>Thank You.<br><br>Yue Jeen

 

[jfriestman]

Nice code for someone new...<br><br>You really only <i>need</i> to use the Parameter object when you need OUTPUT parameters.&nbsp;&nbsp;If you are just sending info into the Stored Procedure you can do it a little something like this...<br><br><br>Dim Rs<br>Set Rs = Server.CreateObject (&quot;ADODB.Recordset&quot<br>Rs.Open &quot;EXEC UP_MyProcedure '&quot; & strName & & &quot;', &quot; & intVar<br><br>That's it!<br><br>Note, I enclosed any string variables in single quotes.&nbsp;&nbsp;Numeric variables don't have them.&nbsp;&nbsp;This would be the same as if you were in QueryAnalyzer and decided to run your stored procedure like this:<br><br>EXEC UP_MyProcedure 'John Doe', 101<br><br>Essentially, thats all you are doing with the code I included above.<br><br>Hope this helps. <p> Jeff Friestman<br><a href=mailto: > </a><br><a href=

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

View my Brainbench transcript</a><br>Brainbench 'Most Valuable Professional' for ASP<br>
Brainbench 'Most Valuable Professional' for JavaScript<br>

http://www.brainbench.com

 

[jfriestman]

Maybe you noticed, but I forgot to put a DSN on the Rs.Open statement, it should read <br><br>Rs.Open &quot;EXEC UP_MyProcedure '&quot; & strName & & &quot;', &quot; & intVar, myDSN<br><br>I'm sure you figured it out but I just wanted to be clear. <p> Jeff Friestman<br><a href=mailto: > </a><br><a href=

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

View my Brainbench transcript</a><br>Brainbench 'Most Valuable Professional' for ASP<br>
Brainbench 'Most Valuable Professional' for JavaScript<br>

http://www.brainbench.com

 

[Creeder]

Hi Jeff,<br><br>First of all thank you for taking the trouble to answer my question. However i am still having some problems.<br><br>I've ran this:<br>&nbsp;<br>Rs.Open &quot;EXEC select_test 'TestParam','TestParam (1234)'&quot;,strConnect<br><br>And I get an <br><br>Microsoft VBScript runtime error '800a000d' <br><br>Type mismatch <br><br>In the SP i've declare the Parameter type as VarChar(50). Could that be the problem?<br><br>Thanks.<br>Yue Jeen <br>&nbsp;<br>

 

[Creeder]

Hi,<br><br>I've solve the problem i have already. Made some mistakes.<br><br>Thank for help.<br><br>Yue Jeen