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.   


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

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

  5. #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
Query printers in 2008 domain using vbscript or WMI VB Script
SQL Query in VBScript VB Script
VBScript to Query WMI VB Script
execute mysql query using vbscript VB Script
VBScript problem in running Stored Query VB Script