SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Jun 2004
    Location
    germany
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Looping thorough a table to get code list

    Hello Assume there is a table called Item

    and fields Itemcode Customercode

    now a customer may have one or many items(itemcode)

    Now the issues are, Customercode can be zero or a code value.

    NOw i want to put a do while loop to get all the records as comma seperated format

    TempItemcodest=0
    select * from Itemtable where customercode=customervalue
    Do while not Rs.eof
    TempItemcodest=TempItemcodest & "," & rs.fields("Itemcode")
    rs.movenext
    loop

    TempItemcodest=TempItemcodest & "0"

    so i am adding a zero at the begging and end to have the commas seperated properly.

    is there any better way to do this?
    Thanks for your reply in advance
    with regards
    vimal
    Knowledge is all about Sharing

  2. #2
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You can use getrows() to pull all the matching records from you table into an array:

    Code:
    strSQL = "select * from Itemtable where customercode=customervalue"
    Set rstCurrent = conn.Execute(strSQL)
    If rstCurrent.EOF = false then
    arrResultSet = rstCurrent.GetRows()
    End if

  3. #3
    SitePoint Addict
    Join Date
    Jun 2004
    Location
    germany
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if i can get it in a array then i have to get all those elements and put into a string inorder to use it another sql query.is it a possibility that i can get all elements comma seperated as string?
    with regards
    vimal
    Knowledge is all about Sharing

  4. #4
    SitePoint Member
    Join Date
    Aug 2004
    Location
    Portsmouth, Hampshire, UK
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have a look at the "Join" function, it will convert an array to a string, adding a seperator.

    Note all this code is off the top of my head so it may not work as planned but should give you an idea.

    Dim outputString
    Dim loopIndex

    ' Code from earlier code example
    ' Note Select * is changed to select ItemCode, so you only get the
    'data you want in the array.
    strSQL = "select ItemCode from Itemtable where customercode=customervalue"
    Set rstCurrent = conn.Execute(strSQL)
    If rstCurrent.EOF = false then
    arrResultSet = rstCurrent.GetRows()
    End if
    ' end code from earlier example

    ' If your passing the string back into SQL and its string data you will need
    ' to put single quotes round it, if its not string data you dont need
    ' the for loop

    For loopIndex = 0 to ubound(arrResultSet) - 1
    ' "'" is a double quote, then a single quote ' then a double quote.
    arrResultSet(loopIndex) = "'" & arrResultSet(loopIndex) & "'"
    Next loopIndex

    outputString = join(arrResultSet, ",")

    Dont forget to close and set to Nothing your recordset. Also its always best practice (and for this piece of code essencial) to only select data from a database you are going to use, using select * shouldnt really be used.

    Jen

  5. #5
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, GetRows isn't needed at all here... GetString is the way to go!

    See my blog entry here!

    Alternatively, for a small performance gain, you can load the recordset from the database, disconnect it from the database connection, create a new JET OLEDB text file connection (with the desired CSV settings), reconnect the recordset, and write it out to the target CSV. However, I suggest that you just use my posted code for now!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  6. #6
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Wow! Marcus returns!

  7. #7
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not back for good, just visiting!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •