Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

returning sql variables to vbscript msgbox

Posted on 2006-11-09
5
Medium Priority
?
574 Views
Last Modified: 2008-02-01
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


0
Comment
Question by:badgermike
5 Comments
 
LVL 20

Expert Comment

by:hes
ID: 17907300
Try:

sql = "SELECT TOP 1 Orders.OrderNumber, [Orders.OrderNumber+1] as x FROM Orders"
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 17907789
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
 
LVL 15

Author Comment

by:badgermike
ID: 17907799
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
 
LVL 11

Expert Comment

by:Dany Balian
ID: 17907821
ur sql is correct:

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

hope this helps

dan
0
 
LVL 11

Assisted Solution

by:Dany Balian
Dany Balian earned 1000 total points
ID: 17907829
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question