Solved

returning sql variables to vbscript msgbox

Posted on 2006-11-09
5
564 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
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
Course of the Month11 days, 13 hours left to enroll

623 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