By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,660 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,660 IT Pros & Developers. It's quick & easy.

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

P: 13
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. variable = Request.querystring("formname")
  3.  
  4.     Set check = MyConn.Execute("select * from table where name='" & variable &"'")
  5.  
  6.     if check("name")=variable then
  7.  
  8.         response.write("I'm sorry, but the name you specified - " & check("name") & "already exists")
  9.  
  10.     else
  11.         call function()
  12.     end if
  13.  
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
Mar 16 '11 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. if check.eof then
  2.    'Nothing was returned, so there is no conflict
  3. Else
  4.    'Conflict found
  5. End if
if you're interested, the query returns a "recordset", in other words, the data looks like this:
Expand|Select|Wrap|Line Numbers
  1. bof
  2. Row of data
  3. Row of data
  4. Row of data
  5. 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

Share this Question
Share on Google+
2 Replies


jhardman
Expert 2.5K+
P: 3,405
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
Expand|Select|Wrap|Line Numbers
  1. if check.eof then
  2.    'Nothing was returned, so there is no conflict
  3. Else
  4.    'Conflict found
  5. End if
if you're interested, the query returns a "recordset", in other words, the data looks like this:
Expand|Select|Wrap|Line Numbers
  1. bof
  2. Row of data
  3. Row of data
  4. Row of data
  5. 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
Mar 16 '11 #2

P: 13
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. variable = Request.querystring("formname")
  3.  
  4.      Set check = MyConn.Execute("select * from table where name='" & variable &"'")
  5.  
  6.      if not check.EOF then
  7.           response.write("I'm sorry, but the name you specified - " & variable & "already exists")
  8.  
  9.      else
  10.           call function()
  11.  
  12.      end if
  13.  
Mar 17 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.