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:
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MdbFilePath = Server.MapPath("directory o\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)?
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
*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.
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.
*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
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 odatabasefile.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.
*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 odatabasefile.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?
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
%>