asp db receive "SELECT * FROM people WHERE id =" & Request.QueryString("id")

Question

I have code written in an ASP file which is not working

Set Conn = Server.CreateObject("ADODB.Connection")         
Conn.Open "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Server.MapPath("dbbb.mdb")
Dim strSQL  
Dim strSQL  
strSQL = "SELECT * FROM people WHERE id =" & Request.QueryString("id")
Set rs = Conn.Execute(strSQL)

The message I receive is:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'id ='.

/testAsp/test3/continue/person.asp, line 26

I tried so much and still not getting it where is the problem? (and yes I am noob in this area)

please help

p.s. I can receive a specific id by entering the num instead of Request.QueryString("id")

Answer 1

Your ID field will generally be looking for an number value. So before executing the database query you should check the QueryString Request does actually contain a number. Here is what I do and it will also prevent sql injections. Create Variable ID and Assign the value of the querystring to it. Check the ID value is not empty and is actually a number "IsNumeric()" Then only if both these are true execute your database lookup. nb. don't forget to close your recordset and connection and set them to Nothing.

Dim ID
ID = Request.QueryString("id")
If ID <> "" And IsNumeric(ID) Then

    Set Conn = Server.CreateObject("ADODB.Connection")         
    Conn.Open "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Server.MapPath("dbbb.mdb")
    Dim strSQL 
    strSQL = "SELECT * FROM people WHERE id =" & ID
    Set rs = Conn.Execute(strSQL)

    ' DO YOUR BUSINESS '

    rs.Close()
    Set rs = Nothing
    Conn.Close()
    Set Conn = Nothing
Else
    If ID = "" Then
        Response.Write("ID is missing from the URL")
    ElseIf Not IsNumeric(ID) Then
        Response.Write("ID is NOT a number")
    End If
End If