Populating with the data from database 1

[amorous]

Hi Guys...

the below is the test code with test data...

strReps = "Jim,Pete,Fred,Jane,Brian"
strRepIDs = "1,2,3,4,5"
strSelectedRepIDs = "0,0,0,0,0"

arrayReps = split(strReps,",")
arrayRepIDs = split(strRepIDs,",")

Now i want to replace this test code with actual data from the database..

lets say i have rsObj,conn everything set
and

SQL="SELECT RepName,RepId From REPS"

So how do i get the values of above variables strReps...etc..

Do i need to create arrays and change the logic or can i just emulate the above structure??

-VJ

 

[Ayac]

Something simple like this? Or you can put it in an array too, by adding an incrementing number and an array variable.

strQuery = "SELECT * FROM REPS;"
Set objRS = objConn.Execute(strQuery)

While not objRS.EOF
strReps = objRS("RepName")
strRepID = objRS("RepId")
objRS.MoveNext
Wend

 

[amorous]

But i think the variables inside the while loop will hold only one value at a time...

-VJ

 

[DreXor]

you could :

a. cycle the records, concat strings together then split them after into your array ( clumsy )

b. cycle records, redim preserve already established array, add new value to end of array ( probably best suited option for logic mentioned )

c. fetch recordset, .getrows it and insta array, but not broken into chunks, you would then need to make array handlers to break the data into groups like you want, see above a & b

 

[amorous]

Thanks Drexor...

Could you please some code snippets to achieve the method b you mentioned...

I did not understand what you meant by adding a new value to the end of the array...

-VJ

 

[DreXor]

In tested and working order "as is" :
Feel free to swap out "seed" for your init array or predimmed array or plain text

<%
Response.Expires=0
dim myArr

myArr = AddToArray("seed","joe")
myArr = AddToArray(myArr,"frank")
myArr =AddToArray(myArr,"bill")
myArr =AddToArray(myArr,"fred")

for i=0 to ubound(myArr)
response.write "|" & myArr(i) & "|<br>"
Next

Function AddToArray(OriginArray,AddValue)
  If IsArray(OriginArray) Then
   NewDim = Ubound(OriginArray)+1
   ReDim Preserve OriginArray(NewDim)
   OriginArray(NewDim) = AddValue
   AddToArray = OriginArray
  Else
    AddToArray = Array(OriginArray,AddValue)
  End If
End Function
%>

 

[amorous]

Hi DreXor,

Thanks for the code...

Now i have something like..

<%
dim stritems,itemnumbers,strzeros
Set rsitem = Server.CreateObject("ADODB.Recordset")
Set MdConnection = Server.CreateObject("ADODB.Connection")
MdConnection.Open strConnect

Sql = "SELECT Itemname, Itemnum FROM cafe ORDER BY Itemname ASC"
Set rsitem = MdConnection.Execute(Sql)

Do Until rsitem.EOF
stritems= stritems & rsitem("Itemname") & ","
itemnumbers= itemnumbers & rsitem("Itemnum") & ","
strzeros = strzeros & "0" & ","
rsitem.MoveNext
Loop

Response.Write stritems <- this gives me the item names
Response.Write itemnumbers <- this gives me the item numbers
Response.Write strzeros <- this gives me the zeros
%>

That means now i have two variables as follows

stritems= name1,name2,name3,
itemnumbers= number1,number2,number3,
strzeros= 0,0,0,

This is what i tried to do to emulate the exact structure as in shown in my first post.

Now how do i use the function you wrote to get the other two variables :

arrayReps = split(strReps,",")
arrayRepIDs = split(strRepIDs,",")


-VJ

 

[DreXor]

i guess i dont understand what you mean by the other two variables...

looks like you have everything there, and you dont need the function/code supplied, only suggestion i would have at this point is alter your loop a little :

Do Until rsitem.EOF
stritems= stritems & rsitem("Itemname") & ","
itemnumbers= itemnumbers & rsitem("Itemnum") & ","
strzeros = strzeros & "0" & ","
rsitem.MoveNext
Loop

TO

Do Until rsitem.EOF
stritems= stritems & rsitem("Itemname") 
itemnumbers= itemnumbers & rsitem("Itemnum") 
strzeros = strzeros & "0"
rsitem.MoveNext
If Not rsitem.eof then
  stritems= stritems & ","
  itemnumbers= itemnumbers & ","
  strzeros = strzeros & ","
End If
Loop

this saves you from having to check last digit to see if it's a command and to strip it from each variable, also prevents having an empty array value at the end.

 

[amorous]

Thanks Drexor....I was little bit confused...

i got it working...

-VJ

 

[DreXor]

not a problem , glad to be of help and thanks for the star