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]

aadubey wrote:
> 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]

Try changing the '+' to '&' in the SQL Statement.

"aadubey" <aad...@discussions.microsoft.com> wrote in message
news:F55E02E9-63D1-4FE6...@microsoft.com...

[Tim Williams]

> CheckServer = rsTYPEID.FIELD(0).value
should be
> CheckServer = rsTYPEID.FIELDS(0).value

Tim

"aadubey" <aad...@discussions.microsoft.com> wrote in message
news:F55E02E9-63D1-4FE6...@microsoft.com...