• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

SQL Query in VBScript

B

Brazil

#1
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.
 

My Computer

B

Brazil

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

My Computer

K

Kathiravan Ilamparithi

#3
VB Script to run the SQL Select query in Server.

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
 

My Computer

J

Joe Fawcett

#4
Re: VB Script to run the SQL Select query in Server.

"Kathiravan Ilamparithi" wrote in message
news:200911715936ikathiravan@xxxxxx

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

My Computer

Users Who Are Viewing This Thread (Users: 1, Guests: 0)