Query DB by ID

CW · October 17th, 2003, 08:39 AM

Hi. Guys.

I want to query a database with ID.

For example.

There is a page that only publish the lastnames of people,s record, and that is also a link. So when you click it , you will see all the record (firstname, DOB etc) of the person whose lastname have been clicked.

could any one give a solution

Thanks Jonax for your help

Imar · October 17th, 2003, 12:19 PM

Hi there,

You'll need to create a "master-detail" solution: A master page that shows lots of records, but just their summaries. Each record has a "details" link (or you can link the last name of the user for example) that sends the Unique ID of the user to the details page. If you're using Dreamweaver, you can have this created automatically for you.

Here's some pseudo code for a hand coded solution to show what I mean:

[Master.asp]

Code:

<%
  ' Create recordset. I assume it has a LastName and a ID column:
  Do While Not MyRecordset.EOF
    Response.Write("<a href=""Detail.asp?ID=" & _
      MyRecordset("ID") & """>" & MyRecordset("LastName") & _
      "</a>
")
  Loop
%>

This code will loop through all the records in the recordset, writing out an <a> and a tag. The <a> tag links to Details.asp and the ID of the user is passed through the QueryString. As the visible element for the <a> (the part you can click on) the user's last name is displayed on the screen.

[Details.asp]

Code:

<%
Dim UserID
UserID = Request.QueryString("ID")
If Len(UserID) > 0 Then
  ' Valid ID passed
  ' Create a recordset that retrieves the details for this user.
End If
%>

The Details page retrieves the user ID from the QueryString, and then uses that ID to do a database lookup to show retrieve the User's details.

Does this help?

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

CW · October 18th, 2003, 02:32 PM

Thanks Imar.

But my question is how can cbe created a recortset that retrieves the details of the user.

Thanks

CW · October 18th, 2003, 03:49 PM

This is my code.
I have seen this code in the book called Begining ASP database
<html>
<head>
<title>detail</title>
</head>
<body>
 
<%
UserID = Request.QueryString("ID")
dim oRSmt
set oRSmt=server.createobject("ADODB.recordset")

sqlText="Select * FROM Student "
sqlText="sqlText & "WHERE ID='" & UsedID & "';"
oRSmt.Open "sqltext, "DSN=Arday"

Do While Not oRSmt.EOF
Response.Write oRSmt("FirstName") & " "

Loop
oRSmt.Close
Set oRSmt = Nothing
%>
</body>
</html>

I get this error.

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/Thesis/test3.asp, line 13, column 20
sqlText="sqlText & "WHERE ID='" & UsedID & "';"
-------------------^

Thanks in advance

Imar · October 18th, 2003, 03:53 PM

Well, it looks like you have a quote too much:

sqlText="Select * FROM Student "
sqlText="sqlText & "WHERE ID='" & UsedID & "';"

You are appending the WHERE clause to the variable sqlText, not to the string value sqlText. Change it to this:

sqlText = "Select * FROM Student "
sqlText= sqlText & "WHERE ID='" & UsedID & "';"

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

CW · October 18th, 2003, 04:13 PM

Thanks Imar.
This is the code, bbut this error comes.

the code.

<html>
<head>
<title>detail</title>
</head>
<body>
 
<%
UserID = Request.QueryString("ID")
dim oRSmt
set oRSmt=server.createobject("ADODB.recordset")
sqlText="Select * FROM Student "
sqlText= sqlText & "WHERE ID='" & UsedID & "';"
oRSmt.Open sqlText, "DSN=Arday"
Do While Not oRSmt.EOF
Response.Write oRSmt("FirstName") & " "

Loop
oRSmt.Close
Set oRSmt = Nothing
%>
</body>
</html>

The error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/Thesis/test3.asp, line 13

I have to look at line 13, but, i could not see any error.

Thanks

Imar · October 18th, 2003, 04:28 PM

What is the datatype of the UserID column in your database? A number, or text?

If it's a number, you should drop the ' around the UserID:

sqlText= sqlText & "WHERE ID=" & UsedID

There is also no need for the semi-colon at the end, so I dropped that as well.

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

CW · October 18th, 2003, 05:43 PM

YEs, sir.
If i try to drop what you say, then the excution of the code took too long.

then it prints that the excution time exceeds.
what may cause that delay ? and how to solve it ?

thanks

Imar · October 18th, 2003, 06:07 PM

Right, sorry, I overlooked that one (This situation happened to me a million times before, causing the same problems you're experiencing):

Do While Not oRSmt.EOF
 Response.Write oRSmt("FirstName") & " "
Loop

This code will loop through the recordset, while EOF (End Of File / Recordset) is not True. However, you're not advancing to the next record, so you keep pointing to the same (the first) record, so EOF will never be True. Change it to this:

Do While Not oRSmt.EOF
 Response.Write oRSmt("FirstName") & " "
 oRSmt.MoveNext()
Loop

This way, after you have output the FirstName, the recordset moves to the next record. Once the last record is displayed, EOF will be True and the loop ends.

HtH,

Imar

P.S. No need to call me Sir. Makes me feel old ;)

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

CW · October 19th, 2003, 02:15 AM

OK. This is again an error.

I do not know why this is so difficult

Error Type:
Microsoft VBScript compilation (0x800A0408)
Invalid character
/Thesis/test3.asp, line 15
Response.Write oRSmt("FirstName") & " "