asked on

How to run SQL query in VBScript

How do I query my SQL server? I don't need to keep an open connection. I just need a quick query so that I can create an XML file.

My SQL server name is MAS/SQLEXPRESS and the DB name is MAS200. I'll be using MASUSERS/Administrator as the username.

I'm not sure what version and provider parameters to use.. or how to program this. Once I have the query, I can write the WHILE--WEND script. I've run queries in PHP, but never VBScript, so I have no idea how to program a basic connection/query.

Server version:
MS SQL Server 2005 9.00.3042.00

I need to output the results in XML in the following format:

Sample XML Output
<?xml version="1.0" encoding="utf-8" ?>
<xmldata>
    <Products>
        <ProductCode>CL-SATA500</ProductCode>
        <StockStatus>225</StockStatus>
    </Products>
    <Products>
        <ProductCode>CL-SATA250</ProductCode>
        <StockStatus>10</StockStatus>
    </Products>
    <Products>
        <ProductCode>CL-SATA1TB</ProductCode>
        <StockStatus>341</StockStatus>
    </Products>
</xmldata>

My query:

USE MAS200
SELECT REPLACE(ItemNumber, 'LX-', 'CL-') AS Product, (QtyOnHand - QtyOnSalesOrder) AS StockStatus
FROM IM2_InventoryItemWhseDetl
WHERE WhseCode = '000'
    AND ItemNumber LIKE 'LX-%'
    AND ItemNumber NOT LIKE '%PACK'
    AND ItemNumber NOT LIKE 'LX-19LCDWC'
    AND ItemNumber NOT LIKE 'LX-UV-%'
    AND ItemNumber NOT LIKE 'LX-YC-%'
    AND ItemNumber NOT LIKE 'LX-SATA250'
    AND ItemNumber NOT LIKE '%MPEG%'
    AND ItemNumber NOT LIKE '%PTZ%'
    AND ItemNumber NOT LIKE '8LITEPK'

Open in new window

carsRST

See sample code. Just fill in your info.

Dim , conn, rs, SQL

server = ""
database = ""
UN=""
PW=""

SQL = "SELECT REPLACE(ItemNumber, 'LX-', 'CL-') AS Product, (Qt..." '<--fill in SQL




Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.recordset")

conn.Open "Provider=sqloledb;" & _
         "Data Source=" & server & ";" & _
         "Initial Catalog=" & database & ";" & _
         "User Id=" & UN & ";" & _
         "Password=" & PW
            
conn.Open strConnection



rs.open SQL, conn

do while not rs.eof

   rs.movenext
loop

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

Open in new window

ASKER CERTIFIED SOLUTION

carsRST

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jamesbcox1980

ASKER

On line 22: conn.Open strConnection

I'm getting the error: "Operation is not allowed when the object is open"

SOLUTION

carsRST

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jamesbcox1980

ASKER

Nevermind, I see what it was supposed to be

strConnection = "Provider=sqloledb;" & _
                "Data Source=" & server & ";" & _
                "Initial Catalog=" & database & ";" & _
                "User Id=" & UN & ";" & _
                "Password=" & PW
            
conn.Open strConnection

Open in new window

jamesbcox1980

ASKER

Thanks it's working great!

jamesbcox1980

ASKER

Thanks. See both answers for a slight error correction