Windows Vista Forums

SQL Query in VBScript

  1. #1


    Brazil Guest

    SQL Query in VBScript

    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 System SpecsSystem Spec

  2. #2


    Brazil Guest

    Re: SQL Query in VBScript

    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 System SpecsSystem Spec

  3. #3


    Kathiravan Ilamparithi Guest

    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 System SpecsSystem Spec

  4. #4


    Joe Fawcett Guest

    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/libr...0(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 System SpecsSystem Spec

SQL Query in VBScript

Similar Threads
Thread Forum
are VBscript on Windows server 2003 and VBscript on WS2008 compatible?
Hi everybody, Sorry if my question has already been asked, but I haven't found anything which is clear for me on this point. I spent a lot of...
VB Script
Query printers in 2008 domain using vbscript or WMI
I have the following code which I use to query published printers in AD on our domain (2003). We have now setup a 2 way trust with another domain...
VB Script
SQL Query in VBScript
I am using the following code to perform a SQL query and return a recordset I am getting the error "Arguments are of the wrong type, are out of...
VB Script
VBScript to Query WMI
Hi, I am basically looking for a VBSCRIPT that can query the WMI to retrieve the BIOS password set on a machine (to prevent normal users from...
VB Script
execute mysql query using vbscript
I have a script which will format some text and create a simple mysql query. The next thing I'd like the script to do is run a batch file to open...
VB Script
VBScript problem in running Stored Query
I am having strange problem. I have a saved query in MS Access which works absolutely fine without any problems. But when I try to call the stored...
VB Script
VBScript Problem with Access Query (Unspecified error)
I am facing a strange problem with a access query. I am trying to retrive some records from the multiple tables using Inner Join. The query is...
VB Script