verify SQL matches 2

[skibascott]

How can I verify if my recordset contains any records?
As of now the code functions fine when the SQL statement finds matches in the database. But, when there are no matches I get an error when I try to read the recordset into an array.

<%	
  PatternNum = Request.Form("Pat")
  SheetNum = Request.Form("Sheet")
  Operation = Request.Form("Oper")
  CurrentRev = Request.Form("Rev")
  CurrentRevDate = Request.Form("RevDate")
  Tool = Request.Form("Tool")
  OpReq = Request.Form("OpReq")

  SQL = "SELECT watchnumber,docnumber FROM WatchDocLink   WHERE docnumber = '" & SheetNum & "'"

  Set RecSet = Server.CreateObject ("ADODB.Recordset") 
	RecSet.Open SQL, Connection, adOpenStatic, adLockReadOnly, adCmdText
  AssDocs_Array = RecSet.GetRows()
	
  RecSet.Close
  Set RecSet = Nothing
	
%>

 

[Chopstik]

This is a frequently asked question and there is also a faq that may help. Try faq333-3804.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[TonyRosen]

PatternNum = Request.Form("Pat")
SheetNum = Request.Form("Sheet")
Operation = Request.Form("Oper")
CurrentRev = Request.Form("Rev")
CurrentRevDate = Request.Form("RevDate")
Tool = Request.Form("Tool")
OpReq = Request.Form("OpReq")

SQL = "SELECT watchnumber,docnumber FROM WatchDocLink WHERE docnumber = '" & SheetNum & "'"

Set RecSet = Server.CreateObject ("ADODB.Recordset")
RecSet.Open SQL, Connection, adOpenStatic, adLockReadOnly, adCmdText
AssDocs_Array = RecSet.GetRows()


If RecSet.EOF Or RecSet.BOF Then
'There are no records
Else
' There are records
End If


RecSet.Close
Set RecSet = Nothing

 

[Chopstik]

Tony, I think you may need to change this slightly:

    If RecSet.EOF [COLOR=red]AND[/color] RecSet.BOF Then
      'There are no records
    Else
      ' There are records
    End If

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[TonyRosen]

Nope... it can be either "OR" or "AND" considering that would mean that there are no records.

I've been using "OR" since day one and have not had a known issue.

 

[Chopstik]

My bad, then, Tony. I'll defer to your judgement here. I've always used "AND" since it seems like a more complete way of verifying, but that may be relative to the user.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[skibascott]

Perfect, thanks for the help.
I never noticed that there were FAQs on this site. That will definitely be of help in the future. Sorry for being "that guy".

 

[Chopstik]

Glad that we were able to help, skibascott. And don't worry about not knowing about the FAQ section. It's all a learning process. ;-)

It's a give and take forum, so the more you know, the more you can subsequently help others.

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous