Solved

returning sql variables to vbscript msgbox

Posted on 2006-11-09
5
562 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 250 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 250 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

820 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