SQL Query in VBS
aadubey
I am trying to convert some VB.net code to VBS.
I am simply trying to query an SQL Database to return an integer value and
assign it to a variable in my script.
the code works perfectly in VB.net.
The problem I am having in VBS appears to be returning the value of the data
field.
the connection works fine. my test for BOF and EOF does not fail and the
logic makes it to my assignment statement.
I do not recieve any errors, but it seems whatever I try, the expected data
is not assigned and is always null. The SQL query when run by itself through
Enterprise Manager, returns the proper data. One Integer Field.
here is my code.
Dim fso, WinPath, CompName, CheckServer
Set fso = CreateObject("Scripting.FileSystemObject")
Set WshShell = WScript.CreateObject("WScript.Shell")
WinPath = WshShell.ExpandEnvironmentStrings("%WINDIR%")
CompName = lCASE(trim(WshShell.ExpandEnvironmentStrings("%
computername%")))
' connection and recordset variables
Dim Cnxn, strCnxn
Dim rsTYPEID, strSQL
' open connection
Set Cnxn = Server.CreateObject("ADODB.Connection")
strCnxn = "Provider=sqloledb;Data Source=SQLSERVERNAME;Initial
Catalog=MyDatabaseName;User Id=MyUserName;Password=MyPassword;"
Cnxn.Open strCnxn
' create and open Recordset using object refs
Set rsTYPEID = Server.CreateObject("ADODB.Recordset")
strSQL = "Select ASSETTYPEID from dbo.WORKSTAT where COMPNAME = '" +
CompName + "'"
rsTYPEID.ActiveConnection = Cnxn
rsTYPEID.CursorLocation = adUseClient
rsTYPEID.CursorType = adOpenKeyset
rsTYPEID.LockType = adLockOptimistic
rsTYPEID.Source = strSQL
rsTYPEID.Open
if not (rsTYPEID.bof and rsTYPEID.EOF) then
rsTYPEID.MoveFirst
CheckServer = rsTYPEID.FIELD(0).value
'CheckServer = rsTYPEID("ASSETTYPEID")
'I have tried several renditions of both of the examples above
'This is where the code fails, it prints the next echo, so I
know it gets here, but the CheckServer variable is null. I have tried to use
CSTR() still no luck.
wscript.echo CheckServer,"CheckPoint Not BOF or EOF"
Else
CheckServer = 0
wscript.echo CheckServer,"CheckPoint Is BOF or EOF"
End if
rsTYPEID.Close
Cnxn.close
'Do something with the CheckServer value *****
Does anyone have an idea what is wrong with my syntax on?
CheckServer = rsTYPEID.FIELD(0).VALUE
Thanks,
Scoobie 921
Bob Barrows
> I am trying to convert some VB.net code to VBS.
>
> I am simply trying to query an SQL Database to return an integer
> value and assign it to a variable in my script.
>
> the code works perfectly in VB.net.
>
> The problem I am having in VBS appears to be returning the value of
> the data field.
> the connection works fine. my test for BOF and EOF does not fail and
> the logic makes it to my assignment statement.
>
> I do not recieve any errors, but it seems whatever I try, the
> expected data is not assigned and is always null. The SQL query when
> run by itself through Enterprise Manager, returns the proper data.
> One Integer Field.
Have you verified that you have built the sql statement correctly? Or
are you running the statement you assume that vbscript is creating in
Enterprise Manager?
There are two ways to confirm that you have built the statement
correctly:
1. use msgbox or wscript.echo to see what your sql statement variable
contains
2. use SQL Profiler to see the actual commands being sent to the
database
>
> here is my code.
>
<snip>
Whoo, you are going to a lot of trouble to do this:
Set Cnxn = Server.CreateObject("ADODB.Connection")
strCnxn = "Provider=sqloledb;Data Source=SQLSERVERNAME;Initial
Catalog=MyDatabaseName;User Id=MyUserName;Password=MyPassword;"
Cnxn.Open strCnxn
strSQL = "Select ASSETTYPEID from dbo.WORKSTAT where COMPNAME = '" +
CompName + "'"
'I would use & instead of +
'you should do a msgbox strSQL to make sure the sql statement contains
'what you expect it to contain
Set rsTYPEID=Cnxn.Execute(strSQL,,1)
if not rsTYPEID.EOF Then
'no need to movefirst - the recordset is already pointing at the 1st
record
CheckServer = rsTYPEID.FIELD(0).value
else
wscript.echo "no records were returned"
end if
rsTYPEID.close
Cnxn.close
--
HTH,
Bob Barrows
Richard Mueller [MVP]
"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:%23pR6DiA...@TK2MSFTNGP02.phx.gbl...
As suggested, MsgBox strSQL should help. For example, does the variable
CompName have a value assigned?
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
David C. Holley
"aadubey" <aad...@discussions.microsoft.com> wrote in message
news:F55E02E9-63D1-4FE6...@microsoft.com...
Tim Williams
should be
> CheckServer = rsTYPEID.FIELDS(0).value
Tim
"aadubey" <aad...@discussions.microsoft.com> wrote in message
news:F55E02E9-63D1-4FE6...@microsoft.com...