ASP / HTML Links

[Raven_Writer]

Is there a way to determine if a user clicks on a link, and if so, update a file / database?

Also, this is more geared to ASP: If you already have an SQL query executed, how can you do another?

Example:

PHP Code:

<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MdbFilePath = Server.MapPath("directory\to\database\file.mdb")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
SQL_query = "SELECT * FROM files"
Set RS = MyConn.Execute(SQL_query)
%> 


How would I be able to run another SQL query (like UPDATE)?

[Trashed20]

well, since you already have your connection open couldn't you just make another query and execute it? I'm assuming this sorta behaves like php, but i may be wrong

[Raven_Writer]

Originally posted by Trashed20
well, since you already have your connection open couldn't you just make another query and execute it? I'm assuming this sorta behaves like php, but i may be wrong

Already tried that before, the error that comes up is this:

"Cannot execute RS, it's already opened"

Not exactly like that, but close to it.

[Trashed20]

have you tried to change the variable name to RS1 or something of that nature? I'm not exactly sure how ASP handles queries. It may still be trying to get things and continues with the code. THe only other thing i could suggest is close the connection and then open it again, but that seems like a really stupid way to do it.

[Raven_Writer]

Originally posted by Trashed20
have you tried to change the variable name to RS1 or something of that nature? I'm not exactly sure how ASP handles queries. It may still be trying to get things and continues with the code. THe only other thing i could suggest is close the connection and then open it again, but that seems like a really stupid way to do it.

Tried both. I know there's a way to do it, I found a tutorial on it before, but lost the link...*ashamed* I should check my History */ashamed*

[seanwitte]

It might be that the connection opened using the Jet provider can't handle a recordset and an update query at the same time. Just use an implicit connection like this:

Set MyConn = Server.CreateObject("ADODB.Connection")
	Set RS = Server.CreateObject("ADODB.Recordset")
	
	connectString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
	MdbFilePath = Server.MapPath("directory\todatabasefile.mdb")
	
	'open the recordset
	SQL_query = "SELECT * FROM files"
	RS.Open SQL_query, connectString
	
	'run the update query
	SQL_query = "UPDATE myTable SET myField = 'foo'"
	MyConn.Open connectString
	MyConn.Execute SQL_Query

If you're running SQL you should ALWAYS use parameterized queries or stored procedures. SQL injection is the easiest way for people to completely screw up your database.

[Raven_Writer]

Originally posted by seanwitte
It might be that the connection opened using the Jet provider can't handle a recordset and an update query at the same time. Just use an implicit connection like this:

Set MyConn = Server.CreateObject("ADODB.Connection")
	Set RS = Server.CreateObject("ADODB.Recordset")
	
	connectString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
	MdbFilePath = Server.MapPath("directory\todatabasefile.mdb")
	
	'open the recordset
	SQL_query = "SELECT * FROM files"
	RS.Open SQL_query, connectString
	
	'run the update query
	SQL_query = "UPDATE myTable SET myField = 'foo'"
	MyConn.Open connectString
	MyConn.Execute SQL_Query

If you're running SQL you should ALWAYS use parameterized queries or stored procedures. SQL injection is the easiest way for people to completely screw up your database.

Ok, that works decent...except this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e4e' 

Operation was canceled. 

/qtheory/conn.asp, line 9

All the paths are correct, it just deals with this line:

RS.Open SQL_query, connectString

Also, does anyone know the answer to my first question?

[seanwitte]

I tried it again quickly and it works fine using only one connection. When you use connection.Execute() it returns a forward-only read-only cursor, so the connection should still be able to service other calls. I'm pretty sure its just with Jet though, it works ok here on SQL Server.

Back to your first question. Say you want to let people vote in a poll. You have a table called VOTE with a poll_id pointing to the poll and a code for what they voted for, names vote_code. When you write the links to the screen, use querystrings appended to the URL and point them back to the same page:

page filename: poll.asp

Poll Response Links:
<A HREF="poll.asp?poll_id=1&vote_code=A">Vote for A</A>
<A HREF="poll.asp?poll_id=1&vote_code=B">Vote for B</A>
<A HREF="poll.asp?poll_id=1&vote_code=C">Vote for C</A>

In the header of poll.asp you need to intercept and handle that request. the querystring values are part of a collection hanging off of the Request intrinsic object. This is bad, but it will work:

<%'local functions
	sub SaveVote(conn, poll, vote, userIP)
		dim sql
		sql = "INSERT INTO VOTE (poll_id, vote_code, user_ip) " & _
		      "VALUES(" & poll & ", '" & vote & "', '" & userIP & "')"
		conn.Execute sql		
	end sub
	
	'variable declarations
	dim connString
	dim connection
	dim pollID
	dim voteCode
	dim IP
	
	'grab any incoming data
	pollID = Request.QueryString("poll_id")
	voteCode = Request.QueryString("vote_code")
	IP = Request.ServerVariables("REMOTE_ADDR")
		
	'validate the data. the poll_id is an integer
	'and the voteCode is a one character value
	if not isNumeric(pollID) then pollID = ""
	if len(voteCode) > 1 then voteCode = ""
	
	'if both the poll_id and vote_code are empty then 
	'redirect to the poll selection page
	if pollID = "" and voteCode = "" then 
		Response.Redirect "poll_list.asp"
		Response.End
	end if
	
	'open a database connection
	connection.Open connString
		
	'if the vote_code is not empty then log the vote	
	if  voteCode <> "" then
		SaveVote connection, pollID, voteCode, IP			
	end if
	
	'get the poll info and render the page
%>