We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

tororom
tororom asked
on
Medium Priority
525 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

Commented:
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

Author

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

Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Excellent! Thanks for your help!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.