Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

returning sql variables to vbscript msgbox

Posted on 2006-11-09
5
Medium Priority
?
572 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

916 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