Converting data connections to use parameters

[rahlquist]

Hello,

Currently in process of updating a site and fixing some quick security loopholes prior to a much more thorough sweep. ASP isnt my strong point and I was wondering if someone could show me the proper changes to make to this connection to make it call its stored procedure using parameters?

<% Set conn = Server.CreateObject("ADODB.Connection")
conn.CursorLocation = adUseServer
conn.open cStr_Tank
sql = "EXECUTE ""dbo"".""Rpt_TanksQuery"" " & Request.QueryString("TankID")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, adOpenForwardOnly, adLockReadOnly
%>

 

[ChrisHirst]

And the database in use would be ???

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[rahlquist]

Chris,

SQL Sever 2008?

Here is another example


Original;
<% Set conn = Server.CreateObject("ADODB.Connection")
conn.CursorLocation = adUseServer
conn.open cStr_Local
sql ="dbo.MyServiceHist @MyID='" & Request.QueryString("My") & "',@BeginDate='" & Request.QueryString("BeginDate") & "',@EndDate='" & Request.QueryString("EndDate") & "'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, adOpenForwardOnly, adLockReadOnly
%>


Updated;
<% Set conn = Server.CreateObject("ADODB.Connection")
conn.CursorLocation = adUseServer
conn.open cStr_Local
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "dbo.MyServiceHist"
cmd.CommandType = adCmdStoredProc
set Myid = cmd.CreateParameter("MyID",adVarChar, adParamInput, 20,Request.QueryString("My") )
set BeginDate = cmd.CreateParameter("BeginDate",adDateTime, adParamInput, Request.QueryString("BeginDate") )
set EndDate = cmd.CreateParameter("EndDate", adDateTime , adParamInput, Request.QueryString("EndDate") )
Set rs = cmd.Execute

%>

Getting type mismatch on this.

 

[rahlquist]

Nm. I got it.

<% Set conn = Server.CreateObject("ADODB.Connection")
conn.CursorLocation = adUseServer
conn.open cStr_Local
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "dbo.MyServiceHist"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value=Request.QueryString("My")
cmd.Parameters(2).Value=Request.QueryString("BeginDate")
cmd.Parameters(3).Value=Request.QueryString("EndDate")
Set rs = cmd.Execute

%>

This works for the most part. For some reason the initial two records in the result set are getting suppressed.