Assign multiple values from querey to variables

I have a query that retrieves 16 lines

the result would be like this


12345, 1, 50
45678, 2, 100
23456, 3, 25

What I need to do is assign the quantity to a Global variable for each Location

I have these Global Variables


How can I query the DB and assign the appropriate value to the appropriate Global Variable

GIndicator1 = 50
GIndicator2 = 100
GIndicator3 = 25
Who is Participating?
Kelvin McDanielSr. Developer/ConsultantCommented:
Here you go... I don't have the ability to run VB6 where I am (nor do I have a Classic ASP site to test on)... so try the code below and post any errors that you run into so that we can debug it. Please let me know if my example wasn't clear.
Dim GIndicator1
Dim GIndicator16
While Not rs.EOF
  If rs(1) = 1 Then
    GIndicator1 = rs(2)
  Else If rs(1) = 2 Then
    GIndicator2 = rs(2)
  Else If rs(1) = 16 Then
    GIndicator16 = rs(2)
  End If

Open in new window

I don't understand what you mean about "Global Variables", as SQL has no variables.

 I recommend you to execute this SELECT:

SELECT Location, SUM(Quantity) as TotalQuantity
FROM (your original table or query here)
GROUP BY Location;

This would display the result you want.
Kelvin McDanielSr. Developer/ConsultantCommented:
What are you going to be consuming this with? A VB6 app? A Classic ASP web application? Need more details.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rmardisAuthor Commented:
I'm sorry I wasn't very clear on the question

i need to populate vb Global Variables with the returned values from the sql query

My sql table has 3 fields

i need to take the quantity value from the returned query and place it in a vb global variable where
the variable represents the location

My sql query returns what i need but it have 16 lines of data

in VB6 i have the Global Variables that need to be filled with the data returned from the DB

such that

select * from sometable would return

Item       Location       Quantity
12345     1                     50
34567     2                     75
65768     3                     35

in the record set

I need to match the location value with the vb global variable with its corresponding name

I need to assign the value for location 1 to Gindicator1

GIndicator1 = 50
GIndicator2 = 75
GIndicator3 = 35
rmardisAuthor Commented:
If my query were to return only 1 record like the following

select * from sometable where location = 1

then i would only have to assign the value like this

GIndicator1 = rsSomeRecordSet!Quantity

It's more of a how to loop through the recordset returned and tie the values
for the location to the appropriate Global Variable

GIndicatorX = LocationX    the X value indicates the relationship

GIndicator1 would be Location 1 in the DB
GIndicator2 would be Location 2 in the DB

since the query will return the values for all 16 locations how can it assign the appropriate value to the appropriate Global Variable
Use the SELECT I said before with an ORDER BY:

SELECT Location, SUM(Quantity) as TotalQuantity
FROM (your original table or query here)
GROUP BY Location;

Put this SELECT on a cursor

Instead of using 12 global variables, use a single array variable with 12 elements.

Fetch every row of the cursor and assign values from cursor the n element of the array.
Sorry, the SELECT would be:

SELECT SUM(Quantity) as TotalQuantity
FROM (your original table or query here)
GROUP BY Location
ORDER BY Location;
rmardisAuthor Commented:
i don't want to sum the values
the values are going into the variables so they can then be written to 16 different digital indicators

there are time the RS would only return a few lines and not always the 16 records in the table

If you post your code we can better see what you are doing and better understand what you are trying to do.
rmardisAuthor Commented:
Your nailed it
Thank you so much!!!
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.