Prev: Running an Excel macro usning .vbs
Next: Map network drive for user using VB/ASP.NET
From: Brazil on 27 Jun 2008 12:11
Hi,

I am trying to write a SQL query in vbs. I have been able to write a
script which queries a single table. My problem now is that I am not
sure how to structure the query for two tables. This is what I have
got so far:

----------------------------------------------------------------------------------------------

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Test;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT ComputerProperties.ComputerName,
ComputerProperties.ParentID, ComputerProperties.UserName,
ProductProperties.ParentID, ProductProperties.DatVer FROM
ComputerProperties, ProductProperties" , objConnection, adOpenStatic,
adLockOptimistic

strSearchCriteria = "ComputerProperties.ParentID =
ProductProperties.ParentID" objRecordSet.Find strSearchCriteria

If objRecordset.EOF Then
Wscript.Echo "Record cannot be found."
Else
wscript.echo objrecordset("ComputerProperties.ComputerName") & "
" & objrecordset("ProductProperties.DatVer")
End If

objRecordset.Close
objConnection.Close

-----------------------------------------------------------------------------

When I run this script I get the following message:

"Arguments are of the wrong type, are out of acceptable range, or are
in conflict with one another"


Essentially, I am trying to run this SQL Query:

Select ComputerName, DatVer, UserName
From ComputerProperties, ProductProperties
where Computer.Properties.ParentID = ProductProperties.ParentID
and ProductProperties.ProductCode = 'Viruscan8600'
and DatVer < '5315'
Order By ComputerName



Does anyone know how I might be able to do this?

Many thanks,

Ben.




From: Richard Mueller [MVP] on 27 Jun 2008 12:47
Ben wrote:

> I am trying to write a SQL query in vbs. I have been able to write a
> script which queries a single table. My problem now is that I am not
> sure how to structure the query for two tables. This is what I have
> got so far:
>
> ----------------------------------------------------------------------------------------------
>
> Const adOpenStatic = 3
> Const adLockOptimistic = 3
> Const adUseClient = 3
> Set objConnection = CreateObject("ADODB.Connection")
> Set objRecordset = CreateObject("ADODB.Recordset")
> objConnection.Open "DSN=Test;"
> objRecordset.CursorLocation = adUseClient
> objRecordset.Open "SELECT ComputerProperties.ComputerName,
> ComputerProperties.ParentID, ComputerProperties.UserName,
> ProductProperties.ParentID, ProductProperties.DatVer FROM
> ComputerProperties, ProductProperties" , objConnection, adOpenStatic,
> adLockOptimistic
>
> strSearchCriteria = "ComputerProperties.ParentID =
> ProductProperties.ParentID" objRecordSet.Find strSearchCriteria
>
> If objRecordset.EOF Then
> Wscript.Echo "Record cannot be found."
> Else
> wscript.echo objrecordset("ComputerProperties.ComputerName") & "
> " & objrecordset("ProductProperties.DatVer")
> End If
>
> objRecordset.Close
> objConnection.Close
>
> -----------------------------------------------------------------------------
>
> When I run this script I get the following message:
>
> "Arguments are of the wrong type, are out of acceptable range, or are
> in conflict with one another"
>
>
> Essentially, I am trying to run this SQL Query:
>
> Select ComputerName, DatVer, UserName
> From ComputerProperties, ProductProperties
> where Computer.Properties.ParentID = ProductProperties.ParentID
> and ProductProperties.ProductCode = 'Viruscan8600'
> and DatVer < '5315'
> Order By ComputerName
>
>
>
> Does anyone know how I might be able to do this?

You need to make your query into a string (enclosed in double quotes). I
assign the query to the Source property of the ADO Recordset object and then
invoke Open. I assign the query to a string variable to assist
troubleshooting. I can echo the query to the console to confirm what it
really looks like. For example:
=============
strQuery = "SELECT ComputerName, DatVer, UserName " _
& "FROM ComputerProperties, ProductProperties " _
& "WHERE Computer.Properties.ParentID = ProductProperties.ParentID " _
& "AND ProductProperties.ProductCode = 'Viruscan8600' " _
& "AND DatVer < '5315' " _
& "ORDER BY ComputerName"

' Echo the query to the console to verify.
Wscript.Echo strQuery

objRecordset.Source = strQuery
objRecordset.Open
=========
But that query looks wrong in several respects. Unless I'm wrong you cannot
have two tables in the FROM clause. More likely you want to use a JOIN.
Also, Computer.Properties is probably ComputerProperties. Also, if you are
retrieving values from more than one table (with a JOIN) you may need to
identify the table the fields apply to (Perhaps
ComputerProperties.ComputerName for example). The query might be similar to:
=============
strQuery = "SELECT ComputerName, DatVer, UserName " _
& "FROM ComputerProperties " _
& "INNER JOIN ProductProperties " _
& "ON WHERE ComputerProperties.ParentID = ProductProperties.ParentID
" _
& "WHERE ProductProperties.ProductCode = 'Viruscan8600' " _
& "AND DatVer < '5315' " _
& "ORDER BY ComputerName"

' Echo the query to the console to verify.
Wscript.Echo strQuery

objRecordset.Source = strQuery
objRecordset.Open
==========
It's up to you which type of JOIN is best (INNER, OUTER, etc). The tricky
part is getting the commas and spaces in the right places.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--


From: Bob Barrows [MVP] on 27 Jun 2008 13:11
Richard Mueller [MVP] wrote:
> But that query looks wrong in several respects. Unless I'm wrong you
> cannot have two tables in the FROM clause.

Actually, you can. Without a WHERE clause, it produces a cross join. With a
WHERE clause, the join is performed in the WHERE clause. THis is the old,
obsolete way of doing joins.

> More likely you want to use a
> JOIN.

I agree. The Join ... ON syntax is more accepted


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


From: Brazil on 30 Jun 2008 05:33
Hi,

thanks for your replies. I have changed my script as suggested by
Richard to the following:

----------------------------------------------------------------------------------------------------------------------



strQuery = """" & """" & "SELECT ComputerName, DatVer, UserName" _
& " FROM ComputerProperties, ProductProperties" _
& " WHERE ComputerProperties.ParentID = ProductProperties.ParentID" _
& " and ProductProperties.ProductCode = " & "'" & "Viruscan8600" & "'"
_
& " and DatVer < " & "'" & "5315" & "'" _
& " Order By ComputerName" & """" & """"


wscript.echo strquery



Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Test;"
objRecordset.CursorLocation = adUseClient
objRecordset.Source = strQuery
objRecordset.Open

If objRecordset.EOF Then
Wscript.Echo "Record cannot be found."
Else
wscript.echo objrecordset("ComputerProperties.ComputerName") & "
" & objrecordset("ProductProperties.DatVer")
End If

objRecordset.Close
objConnection.Close


--------------------------------------------------------------------------------------------------------------------------------

When I run the script now I get the following error message:

The connection cannot be used to perform this operation. It is either
closed or invalid in this context. In my previous script I included
the following as part of the SQL query:

objConnection, adOpenStatic, adLockOptimistic

Do I need to include this somewhere?

Thanks,

Ben



 | 
Pages: 1
Prev: Running an Excel macro usning .vbs
Next: Map network drive for user using VB/ASP.NET