Retrieving 2 or more values from a DB query

[egun]

I have stored the results of a DB query in a variable, here's my code:

Code:

Set adIDs = db.execute(AdIDsSql)
val2 = adIDs.GetString
msgbox val2

The message box then displays the following (on separate lines):
TRAIN1
TRAIN2

How can I now store each in it's own varible? What method would take into account a line return? Tried the following but it doesn't work:

adj1 = LTrim(val2)
adj2 = RTrim(val2)

[Saket]

there can be two ways for this - either split the variable val2 and you can get seperate values in variable or
try to traverse through recordset, I mean do not use GetString, try recordsetobject.fields(0).

[basanth27]

Egun -
try this,

Code:

adj = Split(val2, VBLF)

For i = Lbound(adj) to Ubound(adj)
      msgbox adj(i)
Next

[egun]

Hi Basanth,

When I tried this:

Code:

adj = Split(val2, VBLF)

For i = Lbound(adj) to Ubound(adj)
msgbox adj(i)
Next

All that prints out is the same as before:
TRAIN1
TRAIN2

How can I go through the array (read about the LBound and UBound function) and assign each element to a new value? So that TRAIN1 and TRAIN2 are stored in 2 separate variables.

Thank you.

[basanth27]

Egun -
You may need to research more about arrays. Look up for arrays and see what exactly it means. you will get more info about the above code and the usage of split function.

The values are already stored in a variable. the variable here is adj. the values are within the variable adj. Each value has a reference point created begining from the location Zero to the max you utilize. So adj(0) will give you the pointer to the first value and adj(1) will give you the pointer to the second one, so forth. This concept is called Arrays. The values stored in an array has a reference point begining from zero.

A simple example would be,

Code:

Dim a

a = Array(10, 20, 30) ' Defining a array
msgbox Isarray(a) ' Should return True

msgbox a(0) ' Should Return 10
msgbox a(1)  ' Should Return 20
msgbox a(2)  ' Should Return 30

Similary, When you Split it stores all the items in an array. Hence you dont have to individually store them in separate variables but just use the same variable with the reference.

Let me know if it helps.

[egun]

Hi Basanth,

I guess I am still confused at the disconnect here. It seems the 1st variable adj(0) has both values stored. I cannot separate them successfully. I understand the arrays concept but am not familiar with the Split function.

So I updated my code to print out the 2 values after the For statement:

Code:

adj = Split(val2, VBLF)
For i = Lbound(adj) to Ubound(adj)
msgbox adj(i)
Next

msgbox adj(0)
msgbox adj(1)

When I run this, msgbox adj(0) prints out:
TRAIN1
TRAIN2

and msgbox adj(1) throws an error:

Subscript out of range: '[number: 1]'
Line (37): "msgbox adj(1)".

I will look more into the Split function, but if you have further suggestions I appreciate the help...

-egun

[basanth27]

Hi Basanth,

I guess I am still confused at the disconnect here. It seems the 1st variable adj(0) has both values stored. I cannot separate them successfully. I understand the arrays concept but am not familiar with the Split function.

So I updated my code to print out the 2 values after the For statement:

adj = Split(val2, VBLF)
For i = Lbound(adj) to Ubound(adj)
msgbox adj(i)
Next

msgbox adj(0)
msgbox adj(1)

When I run this, msgbox adj(0) prints out:
TRAIN1
TRAIN2

and msgbox adj(1) throws an error:

Subscript out of range: '[number: 1]'
Line (37): "msgbox adj(1)".

I will look more into the Split function, but if you have further suggestions I appreciate the help...

-egun

Okay, From the Msgbox adj(0) it is clear that the split has not happened.

Lets try with this,

Code:

adj = Split(Val2, " ")
msgbox adj(0)

---

Okay, From the Msgbox adj(0) it is clear that the split has not happened.

Lets try with this,

Code:

adj = Split(Val2, " ")
msgbox adj(0)

Quickly, While you try the above, please try the below too,

Code:

adj = Split(Val2, VBCR)
msgbox adj(0)

---

Okay, From the Msgbox adj(0) it is clear that the split has not happened.

Lets try with this,

Code:

adj = Split(Val2, " ")
msgbox adj(0)

---

Okay, From the Msgbox adj(0) it is clear that the split has not happened.

Lets try with this,

Code:

adj = Split(Val2, " ")
msgbox adj(0)

Quickly, While you try the above, please try the below too,

Code:

adj = Split(Val2, VBCR)
msgbox adj(0)

Egun -
I have confirmed the below works fine and is the solution you are looking for.

Code:

adj = Split(val2, VBCR)
For i = Lbound(adj) to Ubound(adj)
msgbox adj(i)
Next

This should store the required values from adj(0), adj(1) etc...

Please try this and let me know if it works !! I am here for the next 10 mins hoping to see your reply.

[egun]

I will try your suggestion now with the VbCr

---

It worked!! Thank you so much, Basanth.... I appreciate your time in looking at this for me.

Thanks,
egun