nil RecordSet test for record not found

Databases
1

sSQL = "SELECT * FROM Contacts WHERE ContactID = '" + sContactID + "'" rsTemp = dbSQL.SQLSelect(sSQL) if rsTemp <> Nil then MsgBox("The ContactID supplied is in use, select a different ContactID") tfContactID.SetFocus Return end if

I have the above code in the LostFocus event of the a TextField for ContactID which must be unique and between 4 and 12 characters. I am testing the code and putting a value of “TEST” in the ContactID field. There is NO record in the table with a value of “TEST” in ContactID. Yet rsTemp is NOT nil but has a row with nil in all fields. So it didn’t find a row but it still isn’t nil. what am I doing wrong?

thanks,
b

2

You should use rsTemp.EOF to test for no records.

if rsTemp < Nil And Not rsTemp.EOF Then.

rsTemp will only be Nil if your sql statement contains errors.

3

Okay, thanks. That does explain it.

4

here is a better way…


 sSQL   = "SELECT COUNT(8) as cnt FROM Contacts WHERE ContactID = '" + sContactID + "'"
  rsTemp = dbSQL.SQLSelect(sSQL)
  if rs.field("cnt").integervalue>0 then
    MsgBox("The ContactID supplied is in use, select a different ContactID")
    tfContactID.SetFocus
    Return
  end if

It will ALWAYS return a record… assuming of course the table and fields exist

and YES… that is an “8” not an “" the "” would build a full data vector from the table… wasted time/resources… when all you want is a simple count.