Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 896
  • Last Modified:

Assign multiple values from querey to variables

I have a query that retrieves 16 lines

the result would be like this

Item,Location,Quantity

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

GIndicator1
GIndicator2
GIndicator3
GIndicator4
GIndicator5
GIndicator6
GIndicator7
GIndicator8
GIndicator9
GIndicator10
GIndicator11
GIndicator12

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

GIndicator1 = 50
GIndicator2 = 100
GIndicator3 = 25
0
rmardis
Asked:
rmardis
  • 4
  • 3
  • 2
  • +1
1 Solution
 
gplanaCommented:
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.
0
 
Kelvin McDanielSr. Developer/ConsultantCommented:
What are you going to be consuming this with? A VB6 app? A Classic ASP web application? Need more details.
0
 
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
Item,Location,Quantity

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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
0
 
gplanaCommented:
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.
0
 
gplanaCommented:
Sorry, the SELECT would be:

SELECT SUM(Quantity) as TotalQuantity
FROM (your original table or query here)
GROUP BY Location
ORDER BY Location;
0
 
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

0
 
orbitusCommented:
If you post your code we can better see what you are doing and better understand what you are trying to do.
0
 
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
 
  rs.MoveNext
Wend

Open in new window

0
 
rmardisAuthor Commented:
Your nailed it
Thank you so much!!!
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.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now