What if SQL does not return a record?

[inteleserve]

My code works fine when SQL returns a record but when it does not I get an exeption error. Any Ideas?

Set objConn = Server.CreatObject ("ADOB.Connection")
Connstr = "Driver=Microsoft Visual Foxpro Driver; etc...
objConn.Open Connstr

Set objRS = Server.CreateObject ("ADOB.Connection")
strSQL = "select * from mytable where name='myname'"

Response.Write objRS("name")

How do i get away from the execption error if the SQL string does not locate the record in my database?

 

[onpnt]

.EOF may help you

ALWAYS validate you are not at EOF for any language platform etc.. when hitting a database

syntax simply changes from language to language, but the same validation applies to all.


___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[Mr3Putt]

I've always been of the opinion that:

if objRS.BOF and objRS.EOF then
  ... do your NO RECORDS stuff...
else
  ... You've got RECORDS! Hooray!...
end if

is the best way to go. Checking only ONE (.BOF or .EOF) is not entirely accurate. Also, checking the Count of the records (to see if it's ZERO) doesn't return reliable results.

One of my co-workers argues that objRS.RecordCount is always accurate, but it's not... but I can't find the reference material that explains why... dangit.

Using the combination of .BOF and .EOF has always worked 100% for me.

Naturally, your mileage may vary.

 

[onpnt]

Point well taken and a much more complete explanation Mr3Putt.

I think frustration has lead me down the wrong path of my replies. I'll work on that

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[inteleserve]

Thanks onpnt .. you got me going in the right direction.

This works if the name doesn't exist in the database.

If objrs.EOF Then
Response.Write "Error EOF!"
Else
Response.Write objrs("name")
End If

 

[onpnt]

Thanks inteleserve, deffinetely take Mr3Putt's reply into consideration.

To actually partake in my example and only using the .EOF may be even considered lazy programming.

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[inteleserve]

Mr3Putt thanks too .. I'm using both .BOF and .EOF and its working great.

 

[Mr3Putt]

You are, of course, entirely welcome. Now we'll enjoy the part of our show devoted to being a "MrKnowItAll"...

Don't forget to move your recordset into an array at your earliest convenience, then close the recordset and set it to nothing. Much, much more efficient.

Like:

if objRS.BOF and objRS.EOF then
  set objRS = nothing ' no need to close if it's empty...
  Conn1.Close : set Conn1 = nothing  ' close your dbase connection
  --- DO YOUR "NO RECORDS" STUFF HERE --
else
  set rsArray = objRS.getRows
  objRS.Close : set objRS = nothing
  Conn1.Close : set Conn1 = nothing
  recordCount = UBound(rsArray, 2)
  columnCount = UBound(rsArray, 1)
  response.write("<table>" & vbCrLf)
  for r = 0 to (recordCount - 1)
    response.write("<tr>")
    for c = 0 to (columnCount - 1)
      response.write("<td>" & rsArray(c, r) & "</td>")
    next
    response.write("</tr>" & vbCrLf)
  next
  response.write("</table>" & vbCrLf)
end if

Using the Array means you have to KNOW what your columns are by index#... And remember: Arrays are zero-based.

It's a bit more code, and it's a bit harder to read... but, if you're working with large recordsets, it's a much better and faster way to go.

Note: I have made every effort, short of doing any research, to ensure that the code above is correct. It is, almost assuredly, flawed in one or more ways.

Good luck with your project...

Mr3Putt
Seattle... actually, Portland today