• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Problem integrating SQL (Sybase 10) into a vbscript- returning a message if a select statement returns no rows

I am having some difficulty integrating sql into a basic vbscript

What i would like to do-
Connect to the DB and query V_ticket - if no rows are returned have a message pop up. (ultimately ill include a net send command)

If rows are found the script exits.
conn= "DSN=TRAIN;UID=xxx ;PWD=yyyy"
 
' Begin by getting a connection to the database
 
  set adoConn = CreateObject ("ADODB.Connection")
  adoConn.Open (conn)
 
' Associate a recordset object with the connection
 
  set adoRecord = CreateObject ("ADODB.RecordSet")
  adoRecord.ActiveConnection = adoConn
 
 
  query = "select count(*) FROM dba.v_ticket where add_user = 'ivr01'"
		
 
  IF adoRecord.Open (query) = 0then
	answer=MsgBox("Rows Found",65,"Rows Found")
	END IF
done

Open in new window

0
tororom
Asked:
tororom
  • 2
  • 2
1 Solution
 
reb73Commented:
Change lines 17-19 as follows -
adoRecord.Open query
If adoRecord.EOF Then
    MsgBox "No records found!"
End If
adoRecord.Close
set adoRecord = nothing

Open in new window

0
 
tororomAuthor Commented:
Code Modified as suggested- in SQL client the query results in a count of 0 however the messagebox is not shown.
conn= "DSN=TRAIN;UID=xxx ;PWD=yyy"
 
 
 
  set adoConn = CreateObject ("ADODB.Connection")
  adoConn.Open (conn)
 
 
 
  set adoRecord = CreateObject ("ADODB.RecordSet")
  adoRecord.ActiveConnection = adoConn
 
 
  query = "select count(*) FROM dba.v_ticket where acct_id = 0"
                
 
  adoRecord.Open query
  If adoRecord.EOF Then
       MsgBox "No records found!"
  End If
  adoRecord.Close
 
set adoRecord = nothing

Open in new window

0
 
reb73Commented:
Actually, you are looking for count(*) in your query which will always return a value..
Change as follows -

If not adoRecord.EOF Then
    If adoRecord.fields(0).value = 0 Then
       MsgBox "No records found!"
    End If
End If

0
 
tororomAuthor Commented:
Excellent! Thanks for your help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now