Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Converting data connections to use parameters

Status
Not open for further replies.

rahlquist

Programmer
Mar 18, 2003
4
US
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
%>
 
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
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top