Need help please. VBS to display specific SQL record

[chaznbs]

I have tried a few different way to write this script. I need to prompt for Item Number and then display what shows up in the Avg_Cost along with the Item Number and Price

Just been testing with trying to get the Item Number and Average Cost to display but it has been a failure.

Here is one of the many versions of writing this:

dim dbconnection, sqlrs, itemno, sql


Const CONNECT_STRING = "provider=SQLOLEDB.1;Presist Security info=True;User ID=sa;Initial Catalog=AAAAAA;Data Source=BBBBBB;password=CCCCCC"

Set WshShell = WScript.CreateObject("WScript.Shell")
set objFSO = CreateObject("Scripting.FileSystemObject")
Set dbconnection = createobject("ADODB.connection")
Set sqlrs = createobject("ADODB.Recordset")

Itemno = InputBox("Enter Item Number:", "Item Number")



SQL = "SELECT avg_cost FROM [AAAAAA].[dbo].[IM_INV] where ITEM_NO = ITEMNO and LOC_ID = 'MAIN'"

dbconnection.open CONNECT_STRING
dbconnection.execute SQL

Set SQLRS = dbconnection.OpenRecordset(SQL)


wscript.echo "Average Cost of item "& ITEMNO & " is " & sqlrs("avg_cost")


SQLRS.Close

dbconnection.close


I am so not a programmer or database guy so any guidance would be greatly appreciated.

 

[guitarzan]

Try:

SQL = "SELECT avg_cost FROM [AAAAAA].[dbo].[IM_INV] where ITEM_NO = [highlight #FCE94F]" & ITEMNO & "[/highlight] and LOC_ID = 'MAIN'"

 

[chaznbs]

No go.

Is there a way to tell if it is even receiving the variable?


This line works as long as I do not add the sql part to it
wscript.echo "Average Cost of item "& ITEMNO & " is " & sqlrs("avg_cost")

I even tried with a msgbox. I cant seem to pull the sql data

 

[guitarzan]

Something like this may work:

Itemno = InputBox("Enter Item Number:", "Item Number")

SQL = "SELECT avg_cost FROM [AAAAAA].[dbo].[IM_INV] where ITEM_NO = " & Itemno & " and LOC_ID = 'MAIN'" 
dbconnection.open CONNECT_STRING
[s]dbconnection.execute SQL
Set SQLRS = dbconnection.OpenRecordset(SQL)[/s]
Set SQLRS = dbconnection.execute(SQL)

wscript.echo "Average Cost of item "& Itemno & " is " & sqlrs("avg_cost")

Also, make sure you do not have "On Error Resume Next" at the top of the program, which would suppress runtime errors.

 

[chaznbs]

Well I found a script I wrote about a year ago that works for a customer to pull gift certificate values.

What stinks is that when I change the variables around, it bombs out. WTH!!!

WORKING SCRIPT

dim dbconnection, sqlrs, GC_NUM

Set WshShell = WScript.CreateObject("WScript.Shell")
set objFSO = CreateObject("Scripting.FileSystemObject")
Set dbconnection = createobject("ADODB.connection")
Set sqlrs = createobject("ADODB.Recordset")

dbconnection.open CONNECT_STRING

GC_NUM = inputbox("Enter Gift Certificate Number","Gift Certificate Balance Inquiry")

sqlrs.open "SELECT * FROM [SY_GFC] where GFC_NO = '"&GC_NUM&" ' ", CONNECT_STRING

If sqlrs.EOF Then
Wscript.Echo "Gift Card cannot be found."
Else
msgbox ("The balance for Gift Card "&sqlrs("GFC_NO")& " is: $"&sqlrs("CURR_AMT")& "")
END IF

sqlrs.close
dbconnection.close


NONWORKING SCRIPT THAT I NEED:
dim dbconnection, sqlrs, itemno

Set WshShell = WScript.CreateObject("WScript.Shell")
set objFSO = CreateObject("Scripting.FileSystemObject")
Set dbconnection = createobject("ADODB.connection")
Set sqlrs = createobject("ADODB.Recordset")

dbconnection.open CONNECT_STRING

Itemno = InputBox("Enter Item Number:", "Item Number")

sqlrs.open "SELECT * FROM [DEMOGOLF] where ITEM_NO = '"&Itemno&"' AND LOC_ID='MAIN'", CONNECT_STRING

If sqlrs.EOF Then
Wscript.Echo "Invalid Item. Please Re-Enter."
Else
msgbox ("The Average Cost for Item "&sqlrs("ITEM_NO")& " is: $"&sqlrs("AVG_COST")& "")
END IF

sqlrs.close
dbconnection.close

 

[chaznbs]

Nevermind.

Forgot to change database name to table name.

What a Homer!!! DOH!!!!