Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Angry Unanswered: sql recordset into javascript array

    Can someone help with this little one, i reallly am stuck

    I want to produce an array for tree nodes on a page

    I connect to sql fine but i'm, have trouble going through a for loop

    function getRecordset(sql)
    {
    var dbConnectionString = "DRIVER={SQL SERVER};SERVER=name;DATABASE=name;UID=sa;PWD=passw ord"
    var sDBConnection = new ActiveXObject("ADODB.Connection");
    sDBConnection.Open (dbConnectionString);

    var rs = new ActiveXObject("ADODB.Recordset");
    rs.CursorLocation = 3;
    rs.CursorType = 3;
    rs.LockType = 1;
    rs.Open(sql, sDBConnection);

    return rs;

    rs.Close;
    }

    var rs = getRecordset("SELECT * FROM mytable");

    mytable has 2 cols 'id' and 'name' and 2 rows - a simple table to get me started

    e.g
    id name
    1 my first node
    2 my second node


    I want to use something like this, but don't know how to finish it off

    How do I get the numberOfRows? Is it rs.recordcount?
    How do I get the numberOfColumns?
    I don't understand the "queryResults.rows[i].columns[j] +" part

    for (var i=0; i<numberOfRows; i++)
    {
    document.write(i > 0 ? ",\n[" : "[");

    for (var j = 0; j < numberOfColumns; j++)
    {
    document.write
    (
    (j > 0 ? ", " : "") +
    "'" +
    queryResults.rows[i].columns[j] +
    "'"
    );
    }
    document.write(" ]");
    }

    many thanks

  2. #2
    Join Date
    Aug 2004
    Posts
    96
    Hi,
    it looks like you are mixing server side javacasript and client side.

    First if you want to loop through your entire recordset it is easier to use a while loop. Then i am not too sure why you wnat to loop throught the column, you already know your column from the database, all you have to do is use instances of your rs:

    Code:
    //looping through the recorset until we reach the last record:
    
    while (rs.EOF == false)//EOF stands for end of file
    {
    //lets get the value of the first column to use in whatever way you want and output ot the page
    //note that document.write is replaced by Response.Write in server side JS
    
    Response.Write("Here is the Id: "+rs("id").Value+" and here is the name: "+rs("name").Value+"");
    
    //then we move to the next record
    rs.MoveNext();
    
    
    }
    //now all records in your DB are printed on the screen and we are outside the loop
    Hope this helps,
    Olivier

  3. #3
    Join Date
    Jan 2004
    Posts
    53
    I need the recordset to be an array so i can pass it to other code

    like this
    <script type="text/javascript">
    var rows = [
    <%
    for (var i = 0; i < numberOfRows; i++) {
    Response.write(i > 0 ? ",\n[" : "[");
    for (var j = 0; j < numberOfColumns; j++) {
    Response.write(
    (j > 0 ? ", " : "") +
    "'" +
    queryResults.rows[i].columns[j] +
    "'"
    );
    }
    Response.write(" ]");
    }
    %>
    ];
    </script>

    Thanks

  4. #4
    Join Date
    Aug 2004
    Posts
    96
    it looks like you are mixing up server side and client side javacsript.

    To fill up an array in client side JS with data from your database, you would do something like this:


    Code:
    <script language="javascript">
    
    //opening an array
    
    var MyArray = new Array();
    
    //now we go server side to get the data from the db
    <%
    while (rs.EOF == false)
    {
    
    %>
    //now going back client side
    //let say than the index of your array will be the id from your db
    //and the value of your array will be the names
    
    
    //using <%=  replaces your response.Write, you only use Response.Write to write on the page, not to pass a value
    MyArray[<%= rs("id").Value %>] = <%= rs("name").Value %>;
    
    <%
    //now we go back server side to keep looping
    //then we move to the next record
    rs.MoveNext();
    
    
    }
    
    
    
    %>
    
    
    
    
    </script>
    This will store all values from your db into your array

    Hope this helps

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    plork.. is this Server-Side JavaScript (SSJS) or VBScript?
    That which does not kill me postpones the inevitable.

Posting Permissions

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