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

VBScript to return count from Oracle Database

Hi,

I have a VBScript that connects to an Oracle database and returns a count from a table.  I want to use the returned value as a conditional statement, but get the error message Type mismatch.  I can display the value correctly but can not seem to use it in the If statement.  Is it possible that the count is returning as a string variable, if so how do I convert it to use in the condition

Code is :-

'Create object to open log file and then open the file
Set objFSOLog = CreateObject("Scripting.FileSystemObject")
Set LogFile = objFSOLog.OpenTextFile(strFullLogPath, ForAppending, True)

'============================================================================================

strSQL = "SELECT COUNT(1) from ci_bill"
Set adoConnection = CreateObject("ADODB.Connection")

'============================================================================================

strConnect ="Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=172.)(PORT=15))" & _
"(CONNECT_DATA=(SERVICE_NAME=niedev))); uid=;pwd=;"

'============================================================================================

adoConnection.Open strConnect
Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = adoConnection
' Run the query and create resulting recordset.
adoRecordset.Source = strSQL
adoRecordset.Open

' Enumerate the recordset.
strField1 = adoRecordset.Fields(0).Value
If strField1=0 Then
   msgbox "Count is non-zero"
end If

Thanks in advance

Heather
0
HRMorton
Asked:
HRMorton
  • 3
  • 3
1 Solution
 
Jinesh KamdarCommented:
I believe ur variable strField is a string, but you're trying to compare it directly with a number. Either get the return value into a number or use this - If strField1='0' Then
0
 
Jinesh KamdarCommented:
I meant - If strField1 = "0" Then
0
 
philly_teeCommented:
Hi Heather,

I assume your problem is in

strField1 = adoRecordset.Fields(0).Value
If strField1=0 Then
   msgbox "Count is non-zero"
end If

It is likely that the result will be a string,  try

If CInt(strField1) = 0 Then

Of course, if it's possible the value will be higher than the max for an integer, you can use a double instead, use CDbl instead of CInt.

Regards

Philip
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
HRMortonAuthor Commented:
Thanks for the quick response guys, solution worked perfectly.  Much appreciated.
0
 
HRMortonAuthor Commented:
Thanks for the help
0
 
Jinesh KamdarCommented:
Which solution worked?
0
 
HRMortonAuthor Commented:
Thanks for the help.  Used the CDbl, as suggested the CInt was not large enough for what I needed.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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