Stuck on simple ADODB SQL server query

Hi all,

I'm stuck on a really simple problem with running a SQL query through VBS. I'm trying to stuff a variable in the query and the single quotes are protecting it from being referenced. Need to use single quotes or I get an invalid column name error in T-SQL. I've tried Chr(39) and a few other things. I did search first, I promise!

Thanks--

hostname = "server1"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Driver={SQL Server};Server=xxxxxxx;uid=xxxxxx;pwd=xxxxxxx;Database=xxxxx;"
Wscript.Echo "Connection Made"
Wscript.Echo "Argument passed is: " & hostname

Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.CursorLocation = adUseClient

strSQL = "select O.ObjID from Object O where O.Name = 'hostname'"

Wscript.Echo "strSQL is " & strSQL

Try this:

strSQL = "select O.ObjID from Object O where O.Name = '" & hostname & "'"

Hope it helps.

Thanks for the reply; tried that one, won't work. The script won't compile with '"variable"', and "'variable'" treats anything after the fist single quote as a comment.

Quotes and escaping characters are what frustrate me most about Windows coding. What I would give to be able to /'hostname/'.

Is your table named O ? Frankly, I have no idea what's the name of your table. Assuming you table is named O, you could do the following.

strSQL = "select ObjID from O where Name='" & hostname & "'"

Note that you can't have space for the table name.

Here is what I tested and it works for me?

hostname = "server1"
adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Driver={SQL Server};Server=xxxxx;uid=xxxxx;pwd=xxxxx;Database=xxxxx;"
Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.CursorLocation = adUseClient
strSQL = "select O.ObjID from Object O where O.Name = '" & hostname & "'"
objRecordset.Open strSQL, objConnection,3,3
do while NOT objRecordset.EOF
wscript.echo objRecordset.fields("ObjID")
objRecordset.MoveNext
loop

Token,

He is using the "O" as an alias. In this case it is not important but if he were to join two or more tables that each had fields with the same name, "ID" for instance then there would be no way for SQL to know which table you are refering too. by setting an alias for each table it allows you to easily distinquish between the tables. For example:

select a.id, a.name, a.asset, m.id, m.model from assetdata as a
left join mdl_tbl as m on a.mdl_id = m.id

Or you could just type the whole table name each time like:
select assetdata.id, assetdata.name, assetdata.asset, mdl_tbl.id, mdl_tbl.model from assetdata
left join mdl_tbl on assetdata.mdl_id = mdl_tbl.id

But the first example is easier to read and it gets worse if your are joining many table together.

Hope this helps to explain it a bit.

Also I would like to thank token. He has helped me solve my vbscript problems on more than one occasion and I greatly appreciate it.

Thanks token for all of your help.

I thought I had tried putting in double quotes and the & yesterday, but must have been fried. That did it. And it is indeed an alias for just the reasons mentioned, this database is a horror to join tables. Token helped me out a few months ago as well--always appreciated!

Anybody got Tivoli/NetIQ or LS1 engine tuning questions? I can't do diddly with VBS but would really like to pay back the kindnesses ;-)

Thank you all!

fosterr_2000, gotcha! Thanks for the explaination too :D