looping through the recordset?

rad0123 · #1 (**permalink**) 09-23-05, 23:25

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

rokslide · #2 (**permalink**) 09-26-05, 22:02

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

rad0123 · #3 (**permalink**) 09-26-05, 23:26

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

rokslide · #4 (**permalink**) 09-27-05, 00:22

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

rad0123 · #5 (**permalink**) 09-27-05, 09:10

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"%>


<%
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> </p>
</body>
</html>
<%
sales_ledger.Close()
Set sales_ledger = Nothing
%>
<%
customers.Close()
Set customers = Nothing
%>

rokslide · #6 (**permalink**) 09-27-05, 18:52

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....

rokslide · #7 (**permalink**) 09-27-05, 19:36

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
%>

achilez · #8 (**permalink**) 10-10-05, 05:28

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>"

%>

achilez · #9 (**permalink**) 10-10-05, 05:36

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
%>

Internet Services

Web Development

Digital Marketing

Consumer Tech