SQL Query in VBScript

[Brazil]

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.

[Richard Mueller [MVP]]

Ben wrote:

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
--

[Bob Barrows [MVP]]

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"

[Brazil]

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

[kathiravanilamparithi]

Hi,
i need to createa schedular which fetch the data from the table which is in server and it should be stored in to the .CSV format.

To create a batch File i need a VB Script to execute it.

Can anyone help me out in this?

Thanks in advance

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

[Joe Fawcett]

"Kathiravan Ilamparithi" wrote in message
news:20091171593...@yahoo.in...

You can do this task in a number of ways (although I'm not sure why you need
a VBScript and a batch file):
(If it's SQL Server)
* A scheduled SQL Server job

* Scheduled OSQL script
http://msdn.microsoft.com/en-us/library/aa213090(SQL.80).aspx

(For general SQL databases)

* Use ADODB classes to retrieve data

* Use PowerShell and .NET

--

Joe Fawcett (MVP - XML)
http://joe.fawcett.name