returning sql variables to vbscript msgbox

Hi Experts,

I am performing an sql query via vbscript and I cant seem to get the sql variable to show up in the msgbox. Here is my code

dim myconnection
dim myRecordset
dim sql
dim x

Set myconnection = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset" )
myconnection.Open "DSN=x;UID=y;PWD=z"
sql = "SELECT TOP 1 Orders.OrderNumber,x = Orders.OrderNumber+1 FROM Orders"
 myRecordset.Open sql, myconnection

msgbox x


myconnection.Close

Set myRecordset = Nothing
Set myconnection = Nothing

I want to get the x variable out of the sql query and pass it to a vbscript variable - which I can return to an application.

Thanks In Advance :)
Mike


LVL 15
badgermikeAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
would something like this help


sql = "SELECT TOP 1 Orders.OrderNumber FROM Orders"
myRecordset.Open sql, myconnection

x = myRecordset.Fields("OrderNumber")+1
msgbox x
0
 
hesCommented:
Try:

sql = "SELECT TOP 1 Orders.OrderNumber, [Orders.OrderNumber+1] as x FROM Orders"
0
 
badgermikeAuthor Commented:
I tried the statment however I get an error saying that the column Orders.Ordernumber + 1 is not found.

Basically I want the scriptt to take the sql variable that I create( orders.ordernumber + 1) and pass it to the script so I can display it in a msgbox to see if it works

I also tried

sql = "SELECT TOP 1 Orders.OrderNumber as x From Orders"

then created a new variable y

y = x +1
msgbox y

in this case I don't get an invalid error, the message box pops up simply the number 1 when my top record in the database in 1000067...I want it to read 1000068 in the message box. The full formula is:

dim myconnection
dim myRecordset
dim sql
dim str
dim objFS
dim objFile
dim x
dim y
Set myconnection = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset" )
myconnection.Open "DSN=x;UID=y;PWD=z"
sql = "SELECT TOP 1 Orders.OrderNumber as x FROM Orders"

myRecordset.Open sql, myconnection
y = x + 1
msgbox y


myconnection.Close

Set myRecordset = Nothing
Set myconnection = Nothing
0
 
Dany BalianCTOCommented:
ur sql is correct:

try:
myRecordset.movefirst
msgbox myRecordset("x")

hope this helps

dan
0
 
Dany BalianConnect With a Mentor CTOCommented:
sorry, i didnt see ur post...

dim myconnection
dim myRecordset
dim sql
dim str
dim objFS
dim objFile
dim x
dim y
Set myconnection = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset" )
myconnection.Open "DSN=x;UID=y;PWD=z"
sql = "SELECT TOP 1 Orders.OrderNumber as x FROM Orders"

myRecordset.Open sql, myconnection
y = myRecordset("x") + 1
msgbox y


myconnection.Close

Set myRecordset = Nothing
Set myconnection = Nothing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.