Forum Moderators: open
I am passing the patronID and able to see it but I cannot get the data to display.
Here is the code for the page that is supposed to process and display the data:
<%@ Language=VBScript %>
<%Option explicit%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Service Request Management System 1.1</title>
<% 'In order to work the .asp must be opened using an http reference
%>
<%
'define variables
Dim conn, rsQuery, connect, patron, actionPerformed
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adVarChar = 200
patron = Request.Form("patronID")
actionPerformed = Request.Form("btn")
Set conn = Server.CreateObject ("ADODB.Connection") 'create connection
conn.ConnectionString = "DSN=signup;Database=signup;" 'system DSN name; database name
conn.open 'open connection
set rsQuery = Server.createObject("ADODB.Recordset") 'create record set
rsQuery.open "SELECT * FROM signup " _
& "WHERE patronID = " _
& Request.Form("patronID") , conn 'define data for record set, which connection used.;
Set rsQuery = Server.CreateObject ("ADODB.Recordset") ' create the record set
Set rsQuery = conn.Execute
'create the table for the values
%>
</head>
<body>
<center>
<br>
<table border=0>
<td align=center><font face="arial black">Patron ID</font></td>
<% do while not rsQuery.EOF %>
<form method="post" action="http://epl/SRsite/srEdit2.asp" valign="middle">
<tr>
<td align="center">
<% response.write patron %><input type="HIDDEN" name="patronID" value="<% Request.Form patron %>"><BR>
</td>
</table>
Has used: <% response.write rs.Fields.Item("diff") %> minutes today<BR>
<BR>
Date:<% RESPONSE.WRITE(DATE) %><BR>
<input type="Submit" name="OK" value="OK">
</form>
<%
rsQuery.MoveNext
loop
rsQuery.close 'close the recordset
Set rsQuery = nothing
set conn = nothing
%>
</center>
</body>
</html>
All I get is: HTTP 500 - Internal server error
Internet Explorer
If I change: rsQuery.open "SELECT * FROM signup " _
& "WHERE patronID = " _
& Request.Form("patronID") , conn
to
rsQuery.open "SELECT * FROM signup " _
& "WHERE patronID = '" _
& Request.Form("patronID") , conn
Then I get:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression 'patronID = '1000702611871'.
/SignupSite/logintester2.asp, line 44
The patronID field is a text field in the table.
Please help.
Thanks.
Kyje
If the first statement works and the second statement doesn't then I would say look at the database again. Is the patronID that the ASP page is actually talking to, really a text field?
rsQuery.open "SELECT * FROM signup " _
& "WHERE patronID = '" _
& Request.Form("patronID") & "'", conn
1. Put the sql string into a variable:
Dim str
strSql = "SELECT * FROM signup " _
& "WHERE patronID = '" _
& Request.Form("patronID")
rsQuery.open strSql, conn
'rsQuery.open strSql, conn
Response.write("<p>" & strSql & "</p>")
Iguana - Yes the field is text in the table. It works because when I create another page with response.write Request.Form("patronID") it displays the correct #.
mattur- I changed the code as you suggested but I get a different error. *Note* - I did change your suggestion from Dim str to Dim strSql.
Dim strSql
strSql = "SELECT * FROM signup " _
& "WHERE patronID = " _
& Request.Form("patronID")
'rsQuery.open strSql, conn
Response.write("<p>" & strSql & "</p>")
'set rsQuery = Server.createObject("ADODB.Recordset") 'create record set
'rsQuery.open "SELECT * FROM signup " _
'& "WHERE patronID = '" _
'& Request.Form("patronID") & "'", conn 'define data for record set, which connection used.;
Set rsQuery = Server.CreateObject ("ADODB.Recordset") ' create the record set
Set rsQuery = conn.Execute
Here is the new error:
SELECT * FROM signup WHERE patronID = 1000702611871
Microsoft VBScript runtime error '800a01c2'
Wrong number of arguments or invalid property assignment: 'Execute'
/SignupSite/logintester2.asp, line 57
Line 57 is the last line of code that I posted above the error.
Any ideas?
Thanks,
Kyle
SELECT * FROM signup WHERE patronID = '1000702611871'
I either get an error message about a problem with a line of code or I get:
HTTP 500 - Internal server error
Internet Explorer
Thanks,
Kyje69
Change your code to
strSql = "SELECT * FROM signup " _
& "WHERE patronID = '" _
& Request.Form("patronID") & "'"
It should be dim strSql as you noticed kyjeasp, and with a second apostrophe in the example, as Iguana kindly pointed out.
Now that I can get the data I need to know if I can compare the value of the request.form("patronID") to the patronID field in the table or query and if there is no match for request.form value set the value of a variable to zero, and write the variable to the screen.
Also, patrons log in multiple times a day so I cannot set the patronID field in the table to be the Primary Key. How can add up all of the login times and still only display 1 of the entries for that patronID on the screen.
In other words, display the patronID # once and the accumulated time logged on once, instead of the patronID # showing up multiple times.
It's a lot I know.
Sorry,
Kyje69
What about this statement?
RS.Open "SELECT * FROM history WHERE patronID = '" & Request.Form("patronID") & "' AND WHERE logindate = Date()", , adOpenKeyset, adLockOptimistic
It works ok as long as I am trying to match just patronID, I cannot figure out how to just pull records for today only.
logindate is a short date format field in the table.
Thanks,
Kyle
