New SQL query, don't know where to put an "update" line

Can someone help?

[dickbaker]

I've had a page on my site that shows the information from the database fields based on ID. I also had a line that someone wrote for me that updated a field in the database ("pageviews") every time a visitor looked at the particular page.

The old code is wide open for SQL injection, though, so I have to change it. Problem is, I can't figure out how to get the new code to update the pageviews field.

Below is the old code with the update pageviews line, then the new code without it. If anyone could point me in the right direction to get the update line to work, I'd really appreciate it.

Old:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/myconnection.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("ID") <> "") Then
Recordset1__MMColParam = Request.QueryString("ID")
End If
dim theId
theID = Request.QueryString("ID")
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_myconnection_STRING
Recordset1.Source = "SELECT * FROM dbo.mydatabase WHERE ID = " + Replace(Recordset1__MMColParam, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0

If (Recordset1.EOF or Recordset1.BOF) then
Response.Redirect("no_record_sorry.html")
Else
dim sql
sql = "Update dbo.mydatabase set pageviews =(" & Recordset1("pageviews") & " + 1) where id=" & theID


dim Cmd
set cmd = Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection = MM_myconnection_STRING
Cmd.CommandText = sql
Cmd.Execute()
Cmd.ActiveConnection.Close()
Set Cmd = Nothing
End If
%>


The new:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/myconnection.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("ID") <> "") Then
Recordset1__MMColParam = Request.QueryString("ID")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_myconnection_STRING
Recordset1_cmd.CommandText = "SELECT * FROM dbo.mydatabase WHERE ID = ?"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 5, 1, -1, Recordset1__MMColParam) ' adDouble

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0

If (Recordset1.EOF or Recordset1.BOF) then
Response.Redirect("no_record_sorry.html")
End if
%>

[topr8]

imho, i think you should 'clean' the querystring ID ... eg build a function to check that it is the type of value you expect, this will also protect you from sql injection.
eg if ID should be an integer, check that it's an integer.

also i would rethink your query altogether.
i'd write it as a stored procedure and run both the query and the update at the same time (with appropriate logic) - this way you only make one database connection instead of 2, much more efficient and faster.

you'd need to write the stored procedure then you can call it is a similar way using the parameters

[dickbaker]

You might as well be speaking Greek. ;)