Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    15

    Unanswered: looping through the recordset?

    hi i have a list/menu and in that menu i would like to display results from a recordset! this is what i would like to do:

    SALES_LEDER Table=Live Data from different database
    Customer table= customer records already copied over


    every time sales_ledger is updated the new records stay in that table, but i would like a list/menu which shows the records that are not in the customer table, but are in the sales_ledger table.

    i have it working but it only checks the first record from the customer table i would like it to loop through all the records in the customer table to check every record. sorry if this is a bit complicated! novice!

    here is my code so far

    <%
    Dim sales_ledger__MMColParam
    sales_ledger__MMColParam = "0"
    If ((customers.Fields.Item("SageRef").Value) <> "") Then

    sales_ledger__MMColParam = (customers.Fields.Item("SageRef").Value)
    end if
    %>
    <%
    Dim sales_ledger
    Dim sales_ledger_numRows

    Set sales_ledger = Server.CreateObject("ADODB.Recordset")
    sales_ledger.ActiveConnection = MM_sage_STRING
    sales_ledger.Source = "SELECT * FROM SALES_LEDGER WHERE ACCOUNT_REF <> '" + Replace(sales_ledger__MMColParam, "'", "''") + "'"
    sales_ledger.CursorType = 0
    sales_ledger.CursorLocation = 2
    sales_ledger.LockType = 1
    sales_ledger.Open()

    sales_ledger_numRows = 0
    %>


    thanks

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    something like this??
    Code:
    Dim sales_ledger
    Dim sales_ledger_numRows
    Set sales_ledger = Server.CreateObject("ADODB.Recordset")
    
    Do while not customers.eof
        If ((customers.Fields.Item("SageRef").Value) <> "") Then 
            sales_ledger__MMColParam = (customers.Fields.Item("SageRef").Value) 
        end if
    
    
        sales_ledger.ActiveConnection = MM_sage_STRING
        sales_ledger.Source = "SELECT * FROM SALES_LEDGER WHERE ACCOUNT_REF <> '" + Replace(sales_ledger__MMColParam, "'", "''") + "'"
        sales_ledger.CursorType = 0
        sales_ledger.CursorLocation = 2
        sales_ledger.LockType = 1
        sales_ledger.Open()
    
        sales_ledger_numRows = 0
        sales_ledger.Close()
        customers.MoveNext()
    Loop
    
    set sales_ledger = nothing

  3. #3
    Join Date
    Jan 2004
    Posts
    15
    hiya thanks for the reply!
    i tried your code but when i run it, the list box comes back with no values. Any Ideas?

    thanks

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    well you didn't really give me any code that you are currently using for your list box so....

    I assume you want something like this.... perhaps... bit of a guess without more info...
    Code:
    Dim sales_ledger
    Dim sales_ledger_numRows
    Set sales_ledger = Server.CreateObject("ADODB.Recordset")
    
    Response.Write("<select id=""temp"" name=""temp"">")
    Do while not customers.eof
        If ((customers.Fields.Item("SageRef").Value) <> "") Then 
            sales_ledger__MMColParam = (customers.Fields.Item("SageRef").Value) 
        end if
    
    
        sales_ledger.ActiveConnection = MM_sage_STRING
        sales_ledger.Source = "SELECT * FROM SALES_LEDGER WHERE ACCOUNT_REF <> '" + Replace(sales_ledger__MMColParam, "'", "''") + "'"
        sales_ledger.CursorType = 0
        sales_ledger.CursorLocation = 2
        sales_ledger.LockType = 1
        sales_ledger.Open()
        
        if sales_ledger.RecordCount <= 0 then
            Response.Write ("<option value=""" + sales_ledger__MMColParam + """>" + sales_ledger__MMColParam + "</option>"
        end if
        
        sales_ledger.Close()
        customers.MoveNext()
    Loop
    Response.Write("</select>")
    set sales_ledger = nothing
    Personally I'd do one query rather then looping and doing a lot of queries,.. something more like
    Code:
    Dim sales_ledger__MMColParams
    Dim sales_ledger 
    Dim sales_ledger_numRows
    Set sales_ledger = Server.CreateObject("ADODB.Recordset")
    
    sales_ledger__MMColParams=""
    
    Do while not customers.eof
        if sales_ledger__MMColParams <> "" then sales_ledger__MMColParams = sales_ledger__MMColParams + ","
        If ((customers.Fields.Item("SageRef").Value) <> "") Then 
            sales_ledger__MMColParam = (customers.Fields.Item("SageRef").Value) 
        end if
        sales_ledger__MMColParams = sales_ledger__MMColParams + "'' +Replace(sales_ledger__MMColParam, "'", "''") + "'"
        customers.MoveNext()
    Loop
    
    sales_ledger.ActiveConnection = MM_sage_STRING
    sales_ledger.Source = "SELECT * FROM SALES_LEDGER WHERE ACCOUNT_REF not in (" + sales_ledger__MMColParams + ")"
    sales_ledger.CursorType = 0
    sales_ledger.CursorLocation = 2
    sales_ledger.LockType = 1
    sales_ledger.Open()
    
    if not(sales_ledger.eof) 
        Response.Write("<select id=""temp"" name=""temp"">")
    
        do while not sales_ledger.eof
            Response.Write ("<option value=""" + sales_ledger("ACCOUNT_REF") + """>" + sales_ledger("ACCOUNT_REF") + "</option>"
        end if
            sales_ledger.MoveNext()        
        Loop
        sales_ledger.Close()
    
        Response.Write("</select>")
    end if
    set sales_ledger = nothing

  5. #5
    Join Date
    Jan 2004
    Posts
    15
    hiya i used your code! the first one worked ok, but the only problem it has is that it brings records in but all the ones that are in the customer table, i just want the records that are in the sales_ledger table but not in the customers table. Sorry to be a pain, i just cannot see the problem with the code.

    btw here is the code i used before yours


    Many Thanks

    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="Connections/sage.asp" -->
    <!--#include file="Connections/ORGSchedularSQL.asp" -->
    <%
    Dim customers
    Dim customers_numRows

    Set customers = Server.CreateObject("ADODB.Recordset")
    customers.ActiveConnection = MM_ORGSchedularSQL_STRING
    customers.Source = "SELECT * FROM orgschedularsql.customers"
    customers.CursorType = 0
    customers.CursorLocation = 2
    customers.LockType = 1
    customers.Open()

    customers_numRows = 0
    %>
    <%
    Dim sales_ledger__MMColParam
    sales_ledger__MMColParam = "0"
    If ((customers.Fields.Item("SageRef").Value) <> "") Then

    sales_ledger__MMColParam = (customers.Fields.Item("SageRef").Value)
    end if
    %>
    <%
    Dim sales_ledger
    Dim sales_ledger_numRows

    Set sales_ledger = Server.CreateObject("ADODB.Recordset")
    sales_ledger.ActiveConnection = MM_sage_STRING
    sales_ledger.Source = "SELECT * FROM SALES_LEDGER WHERE ACCOUNT_REF <> '" + Replace(sales_ledger__MMColParam, "'", "''") + "'"
    sales_ledger.CursorType = 0
    sales_ledger.CursorLocation = 2
    sales_ledger.LockType = 1
    sales_ledger.Open()

    sales_ledger_numRows = 0
    %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <title>Untitled Document</title>
    </head>

    <body>
    <form name="form1" method="post" action="1.asp">
    <div align="center">
    <select name="select">
    <option value="0">Please Select...</option>
    <%
    While (NOT sales_ledger.EOF)
    %>
    <option value="<%=(sales_ledger.Fields.Item("ACCOUNT_REF") .Value)%>"><%=(sales_ledger.Fields.Item("NAME").Va lue)%></option>
    <%
    sales_ledger.MoveNext()
    Wend
    If (sales_ledger.CursorType > 0) Then
    sales_ledger.MoveFirst
    Else
    sales_ledger.Requery
    End If
    %>
    </select>
    <input type="IMAGE" src="../images/Nav_img/go.gif" alt="Submit button" width="18" height="18">
    </div>
    </form>
    <p></p>

    <p></p>
    <p>&nbsp;</p>
    </body>
    </html>
    <%
    sales_ledger.Close()
    Set sales_ledger = Nothing
    %>
    <%
    customers.Close()
    Set customers = Nothing
    %>

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I'll try and have a look over it later today,... but basically it suggests to me that your sql select statement is incorrect somewhere....

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    here's a full page, which should, ignoring any syntax problems, do what you want... I think...
    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="Connections/sage.asp" -->
    <!--#include file="Connections/ORGSchedularSQL.asp" -->
    <%
    Set customersWithOutSales = Server.CreateObject("ADODB.Recordset")
    customers.ActiveConnection = MM_ORGSchedularSQL_STRING
    customers.Source = "SELECT * FROM SALES_LEDGER WHERE ACCOUNT_REF not in (select SageRef from orgschedularsql.customers)"
    customers.CursorType = 0
    customers.CursorLocation = 2
    customers.LockType = 1
    customers.Open()
    %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <title>Untitled Document</title>
    </head>
    
    <body>
    <form name="form1" method="post" action="1.asp">
    <div align="center">
    <select name="select">
    <option value="0">Please Select...</option>
    <%
    While (NOT customersWithOutSales .EOF)
    %>
        <option value="<%=(customersWithOutSales.Fields.Item("ACCOUNT_REF").Value)%>"><%=(customersWithOutSales.Fields.Item("NAME").Value)%></option>
    <%
        customersWithOutSales.MoveNext()
    Wend
    %>
    </select>
    <input type="IMAGE" src="../images/Nav_img/go.gif" alt="Submit button" width="18" height="18">
    </div>
    </form>
    <p></p>
    
    <p></p>
    <p>&nbsp;</p>
    </body>
    </html>
    <%
    customersWithOutSales.Close()
    Set customersWithOutSales = Nothing
    %>

  8. #8
    Join Date
    Oct 2005
    Location
    Cebu, Philippines
    Posts
    3

    looping through the recordset

    Here's the solution that will retrieve all your data on the recordset provided by the sql statement to display on the list/menu object on ASP.

    Here's my code below:

    Thanks,
    Achiles Archie Mercader

    <%

    dim rs,strHTML

    set rs = server.createobject("adodb.recordset")

    rs,strHTML = ""
    SQL = "SELECT * FROM SALES_LEDGER WHERE ACCOUNT_REF <> '" + Replace(sales_ledger__MMColParam, "'", "''") + "'"

    set rs = conn.execute(sql)

    strHTML = "<select name=""sales_ledger"">"
    while not rs.eof

    strHTML = strHTML & "<option>" & rs("SageRef") & "</option>" & vbcrlf
    rs.movenext

    wend
    rs.close

    strHTML = strHTML = "</select>"

    %>

  9. #9
    Join Date
    Oct 2005
    Location
    Cebu, Philippines
    Posts
    3

    looping through the recordset

    You can also use to call this function DBtoListMenu (convert your data in your recordset to Listmenu) and return the HTML code.


    <%

    Function DBtoListMenu(SQL,Conn,RS)
    dim tmpstr

    tmpstr = "<select name=""sales_ledger"">"
    set rs = conn.execute(sql)
    while not rs.eof

    tmpstr = tmpstr & "<option>" & rs(0) & "</option>"

    rs.movenext
    wend
    rs.close
    conn.close

    tmpstr = tmpstr & "</select>"

    DBtoListMenu = tmpstr
    %>

Posting Permissions

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