I've written a sql select statement that return data just fine. I've got to format the data for my JS array though. The party I'm having issues with is place the , in the correct spot. I need to place it between the } and the next { . Current how my code is it add's an extra , at the end which causes issues.

<%
    sqlEmployees "SELECT * FROM people ORDER BY Last_Name"
    Set rsEmployees = conn.Execute(sqlEmployees)

    If (Not rsEmployees.EOF) Then
        response.write "employees = ["
        While (Not rsEmployees.EOF)
            response.write "{label: '" & rsEmployees("Name_Last") & ", " & rsEmployees("Name_First") & "', value: " & rsEmployees("ID") & "}, "
            rsEmployees.MoveNext
        Wend
        response.write "];"
    End If
    Set rsEmployees = Nothing
%>

Maybe try a different approach. Im not that familiar with the use of While..Wend. Maybe a Do..Loop would work better. For example...

    Do While Not rsEmployees.EOF
      response.write "{label: .................... value: " & rsEmployees("ID") & "}, "
      rsEmployees.MoveNext
      If rsEmployees.EOF Then
        response.write "{label: ................... value: " & rsEmployees("ID") & "} "
      End If
    Loop

Notice that I'm checking to see if it is the last record and on that one response.write without the comma at the end.

This hasnt been tested... just wanted to share the concept with you.

I see what you are doing. I tried the Do While and a For Each and neither worked. They both break the application. I've commented the parts in the center out except for the .MoveNext and it still crashes.

I actually ended up writing it like this.

<%
                sqlEmployees = "Select ID, Name_First, Name_Last FROM tbl_people ORDER BY Name_Last ASC" 
                Set rsEmployees = conn.Execute(sqlEmployees)

                If (Not rsEmployees.EOF) Then
                    response.write "employees = ["

                    intCount1 = 0
                    While (Not rsEmployees.EOF)
                        intCount1 = intCount1 + 1
                        rsEmployees.MoveNext
                    Wend

                    rsEmployees.MoveFirst

                    intCount2 = 0
                    While (Not rsEmployees.EOF)
                        If (intCount2 <> intCount1 - 1) Then
                            response.write "{label: '" & rsEmployees("Name_Last") & ", " & rsEmployees("Name_First") & "', value: " & rsEmployees("ID") & "}, "
                        Else
                            response.write "{label: '" & rsEmployees("Name_Last") & ", " & rsEmployees("Name_First") & "', value: " & rsEmployees("ID") & "}"
                        End If
                        intCount2 = intCount2 + 1
                        rsEmployees.MoveNext
                    Wend
                    response.write "];"
                End If
                Set rsEmployees = Nothing
            %>

I ran the first While Loop to get the # of results. Then the second loop uses intCount1 to compare if it's at the end to dispaly a different response.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.