ASP - SQL query with where clause causes error if not returned.

[Phil Gent]

Hi,
I'm fairly new to ASP and I want to check a form input against my database to see if an entry with the same name already exists and have an if..then and else clause based on this.
I think I'm going about this the wrong way as I get an error when the database doesn't find a match.

 
variable = Request.querystring("formname")
 
    Set check = MyConn.Execute("select * from table where name='" & variable &"'")
 
    if check("name")=variable then
 
        response.write("I'm sorry, but the name you specified - " & check("name") & "already exists")
 
    else
        call function()
    end if
 

This code works fine where there is already an entry in the database, but falls over on the if function when there is no match.

Any suggestions much appreciated

[jhardman]

Good question, phil. Look at your logic. If the query returns anything than that means you have a name conflict. Instead of checking for a name conflict after you run the query (check("name") = variable) check to see if you get anything

if check.eof then
   'Nothing was returned, so there is no conflict
Else
   'Conflict found
End if

if you're interested, the query returns a "recordset", in other words, the data looks like this:

bof
Row of data
Row of data
Row of data
Eof

and then positions the recordset on the first row of data. When you ask for check("name") you are asking the recordset what is the "name" field value of the current row. If you use the check.movenext command you go on to the second row of data etc. If you go on to the eof line and ask for the name field you will get an error because there is no data on that line. When your query returns no results all your recordset receives is "eof". So when you ask for check("name") you are asking it to look in the eof line, and there is no data there.

Let me know if this helps.

Jared

[Phil Gent]

Thanks for your help Jared, I actually managed to work it out in the end, but your explanation helps me to understand why this works so cheers for that (I did it a little bit backwards, but works in the same way!) Code below

 
variable = Request.querystring("formname")
 
     Set check = MyConn.Execute("select * from table where name='" & variable &"'")
 
     if not check.EOF then
          response.write("I'm sorry, but the name you specified - " & variable & "already exists")
 
     else
          call function()
 
     end if