Chief Delphi - ASP / HTML Links

Chief Delphi (http://www.chiefdelphi.com/forums/index.php)

- Website Design/Showcase (http://www.chiefdelphi.com/forums/forumdisplay.php?f=64)

- - ASP / HTML Links (http://www.chiefdelphi.com/forums/showthread.php?t=21037)

ASP / HTML Links

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)?

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

Quote:

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.

Quote:

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:

Code:

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.

Quote:

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:

Code:

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:

Code:

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:

Code:

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

Code:

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:

Code:

<%'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

%>