0

There is a stored procedure in SQL server which takes four values (2 datetimeand 2 varchar) as input and returns int value as output. This stored Procedure is working fine in SQL Server. In VBScript code of Classic ASP application, I've made some connections and trying to get the int output value of Stored procedure and Print on web page. The below is the code..

 dim DD 'This variable stores output of the Stored Procedure date_diff
 dim CS
 CS = "Provider=SQLOLEDB.1; Data Source=BLAHBLAH; InitialCatalog=BLAH; User Id=BLAH; Pwd=BLAH"
 dim objConn
 dim objRS1
 dim StoredProc1
 storedProc1 = "exec date_diff ' "& R1("LEAVE_FROM") &" ' , ' "& R1("LEAVE_TO") &" ' ,'"R2("COMPANY_CODE")"','"R2("LOCATION_CODE")"' ,'" & DD &"' output"

 Set objConn = Server.CreateObject("ADODB.Connection")
 objConn.CommandTimeout = 60
 objConn.Open CS

 objRS1.CursorLocation = 3
 objRS1.CursorType = 3 
 objRS1.Open StoredProc1, objConn

 If  not objRS1.EOF Then
    response.write  DD 'Printing the output of SP here
 end if

I am not getting 500 - Internal Server error on the web page. Can you Please guide where am I going wrong..?

user692942
  • 16,398
  • 7
  • 76
  • 175
METALHEAD
  • 2,734
  • 3
  • 22
  • 37
  • There are plenty of resources on [so] about this exact problem already. See [Using Stored Procedure in Classical ASP .. execute and get results](http://stackoverflow.com/a/21944948/692942) and [How to use ASP variables in SQL statement](http://stackoverflow.com/a/20702205/692942) and [Change stored proc to inline sql](http://stackoverflow.com/a/36127333/692942). Key thing here is **don't use the `ADODB.Connection` object to execute Stored Procedures** use the `ADODB.Command` object that it what it is there for. – user692942 Mar 23 '16 at 09:45
  • That way you don't leave yourself open to [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection) because the `ADODB.Command` object builds [parametrised queries](https://blogs.msdn.microsoft.com/sqlphp/2008/09/30/how-and-why-to-use-parameterized-queries/) that provides a level of protection that `objRS1.Open StoredProc1, objConn` doesn't when you pass variables directly into the SQL statement. – user692942 Mar 23 '16 at 09:49
  • Hi Lankymart...in the post I've seen your reply.. `.Parameters.Append .CreateParameter("@userinumber ", 200, 1, 10, inumber)` what does the `200`, `1`,`10`, `inumber` refer to.. – METALHEAD Mar 23 '16 at 09:56
  • They refer to the numeric values of [ADO constants](https://msdn.microsoft.com/en-us/library/ms678353(v=vs.85).aspx) that are not accessible by name until you `#include` `adovbs.asp` *(not recommended)* or you use `METADATA` tag to add a reference to the Type Library allowing you to call the actually named constants. For example `200` is `adVarChar`. See [Using METADATA to Import DLL Constants](http://www.4guysfromrolla.com/webtech/110199-1.shtml). [This](http://stackoverflow.com/a/26776169/692942) explains how to implement it. – user692942 Mar 23 '16 at 10:01
  • Hi Lankymart...I am getting error for this small code...Do you find any error in this..?? `Dim conn_string` `conn_string = "Provider=SQLOLEDB;server=SCRBNGADK001988;database=SMART2UAT;UId=conapt;Pwd=conapt"` `cmd = CreateObject("ADODB.Command")` `cmd.ActiveConnection = conn_string` – METALHEAD Mar 23 '16 at 11:27
  • `cmd` is an object so has to be `Set`, should be `Set cmd = CreateObject("ADODB.Command")` you probably got an error similar to `Object not found or With block variable not set`. – user692942 Mar 23 '16 at 11:43
  • U r exactly correct bro..... – METALHEAD Mar 23 '16 at 12:18

0 Answers0