Trying to run an sql command through VB Script

March 22, 2018 at 22:12:21
Specs: Windows 8
Hi there,
I am trying to check whether a particular table has been updated in the last 1 hour.There is a column server_datetime in my table.When i run the below vbs script there is no value in varx.Is there something wrong
Function CheckTable
dim dbconn, connect,rs,varx

On Error Resume Next
connect = "Driver={SQL Server};Server=Servername;database=databasename ;uid=userid;pwd=password"
Set dbconn = CreateObject("ADODB.Connection")
dbconn.Open connect
set rs=dbconn.Execute("select count(*) from retail_transaction WHERE DATEADD(hh,-1,GETDATE()) < server_datetime")
varx = rs(0).value
LogDetail("Value of count is " & varx)
If varx =0 Then
LogDetail("Error create some file")
else
LogDetail("No error")
end if

End Function

See More: Trying to run an sql command through VB Script

Report •

#1
March 24, 2018 at 08:18:46
Two things:
On Error Resume Next
If you're trying to figure out what's going wrong, don't tell your script to hide what's going wrong.

set rs=dbconn.Execute("select count(*) from retail_transaction WHERE DATEADD(hh,-1,GETDATE()) < server_datetime")
varx = rs(0).value

Pretty sure you need a rs.MoveFirst in here.

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#2
March 25, 2018 at 22:20:21
Function CheckTable
dim dbconn, connect,rs,varx


connect = "Driver={SQL Server};Server=wusrcqapstor:8889;database=USICOAL ;uid=userid;pwd=password"
Set dbconn = CreateObject("ADODB.Connection")
dbconn.Open connect
if Err.Number > 0 Then
WScript.Echo("Error establishing sql connection- " & Err.Number & ": " & Err.Description)
Err.Clear
End If
set rs=dbconn.Execute("select count(*) as 'Transactions' from retail_transaction WHERE DATEADD(hh,-1,GETDATE()) < server_datetime")
if Err.Number > 0 Then
WScript.Echo("Error executing SQL Statement - " & Err.Number & ": " & Err.Description)
Err.Clear
End If
varx = rs("Transactions")
if Err.Number > 0 Then
WScript.Echo( "Error assigning value to varx - " & Err.Number & ": " & Err.Description)
If rs.EOF = True Then LogDetail("Recordset contains no data!")
Err.Clear
End If
LogDetail("Value of count is " & varx)
If varx =0 Then
LogDetail("Error create some file")
else
LogDetail("No error")
end if

End Function

It shows error on the line dbconn.Open connect saying microsoft odbc sql server driver dbnetlib invalid connection

I have SQL Server Driver 32 bit


Report •

#3
March 26, 2018 at 09:52:59
So something's wrong with your connection string. At this point, you need to work with your DBA or anyone who can view the original, non-anonymized string. Ideally, that person would also be able to verify the database connection info you're using.

How To Ask Questions The Smart Way


Report •
Related Solutions


Ask Question