×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Need help please. VBS to display specific SQL record

Need help please. VBS to display specific SQL record

Need help please. VBS to display specific SQL record

(OP)
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.

RE: Need help please. VBS to display specific SQL record

Try:

CODE

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

RE: Need help please. VBS to display specific SQL record

(OP)
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

RE: Need help please. VBS to display specific SQL record

Something like this may work:

CODE

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

RE: Need help please. VBS to display specific SQL record

(OP)
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

RE: Need help please. VBS to display specific SQL record

(OP)
Nevermind.

Forgot to change database name to table name.

What a Homer!!! DOH!!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close