urgent reply required

mehdi62b · August 19th, 2004, 10:35 AM

as you should know datareader is a forward-only data source that means u can only read one field and then go ahead(u cant go back and read previous fields)
then now u wan to implement paging using such object(datareader)u should be able to read previous fields with help of a smart method(as peter mentioned) i.e
suppose in this time u are in page 6 that includes fields 50 up to 60 ,and now u want to read page 4(includes fields 30 up to 40)u should again close ur datareader
and again open and read(objDataReader.Read())from the begining until reaching field 40 now if u want go ahead no need for closing ur reader u can go on reading fields.
I think its better not to use datareader and use the link Imar mentioned.

--------------------------------------------
Mehdi.:)

ham · August 20th, 2004, 01:02 AM

hi

still confused , can u write come code for implementing paging based on my requirenment(html table and datareader) , so it give me an idea
how to implment that.

how i display just 5 rows e.g on page 1

scenario:

sql="select * from city"

i use

while dr.read
<tr><td>response.write(dr("city_id"))</td></tr>
end while

how i can page so that only first 5 rows are displayed per page for the above query.
and wat i write in next button which display next 5 rows on page 2.
---------------------------------------------------
i think that help u in solving my prob.

waiting for reply

mehdi62b · August 20th, 2004, 07:32 AM

I still advise u to use the link Imar told but anyway

Quote:

quote:
while dr.read
<tr><td>response.write(dr("city_id"))</td></tr>
end while

was it javasciript?,its better to use codebehnd ,i.e use a Repeater object &
work with ItemTemplate and DataSource of ur repeater then bind ur repeater everytime u goes to new page
ur query could be something like below

Code:

select * 
from MyTable 
where ColId between m and n
order by ColId

u can determine and implement m & n in ur codebehinde through SqlParameters
if u dont have an ID column(!) in ur table u should use another ways like
http://www.sqlteam.com/item.asp?ItemID=566

HtH.

--------------------------------------------
Mehdi.:)

mehdi62b · August 20th, 2004, 08:04 AM

Quote:

quote:
if u dont have an ID column(!) in ur table u should use another ways like
http://www.sqlteam.com/item.asp?ItemID=566

something like below...

Code:

SELECT TOP n-m Id
FROM Table1
WHERE Id in (SELECT TOP n Id
  FROM Table1
  ORDER BY Quantity DESCENDING)
ORDER BY Quantity ASCENDING

--------------------------------------------
Mehdi.:)

ham · August 21st, 2004, 06:44 AM

hi

i m nearing the solution.
wat i do to that query(that u mentioned in ur previous post), if there is multiple joins . then how i page for that , wat would be the query then?

waiting for the reply

dotnetMe · August 22nd, 2004, 11:16 AM

if you are using stored procedure maybe this one can help you:

http://www.dotnetjunkies.com/Article...BDB9B8845.dcik

the link is currently not working.. -> google -> search for "custom paging +stored procedure" read first hit from google cache.

just call the sp with current page and paging size.
you can insert your original query in the temporary table used in the sp.

ham · August 24th, 2004, 06:47 AM

hi

see the code below and fix the prob in it

<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="print.aspx.vb" Inherits="g.print"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
    <HEAD>
        <title>fcustprint</title>
        <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
        <meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
        <meta content="JavaScript" name="vs_defaultClientScript">
        <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
        <script runat="server">
    Sub next1(sender As Object, e As EventArgs)
         TextBox1.Text = Val(TextBox1.Text) + 5
    End Sub
    Sub prev1(sender As Object, e As EventArgs)
         TextBox1.Text = Val(TextBox1.Text) - 5
    End Sub
        </script>
    </HEAD>
    <body MS_POSITIONING="GridLayout">
        <form id="Form1" method="post" runat="server">
            <asp:textbox id="TextBox1" style="Z-INDEX: 103; LEFT: 296px; POSITION: absolute; TOP: 80px" runat="server"
                Height="16px" Width="112px"></asp:textbox><asp:linkbutton id="lb1" style="Z-INDEX: 105; LEFT: 240px; POSITION: absolute; TOP: 88px" onclick="next1"
                runat="server" Height="16px" Width="32px">Next</asp:linkbutton><asp:linkbutton id="LinkButton1" style="Z-INDEX: 106; LEFT: 168px; POSITION: absolute; TOP: 88px"
                onclick="prev1" runat="server" Height="16px" Width="40px">Previuos</asp:linkbutton></form>
        <strong>Geofman Pharmaceutical
        <br>
        Field Person Cities Customers Report</STRONG>
        <br>
        Report Date and Time :<%response.write(date.now()) %>
            <br>
            <br>
            Type :
                <% response.write(request.querystring("r"))%>

            <%
           dim m as string
           m = request.querystring("r")
           if m ="All" then
        %>
            <TABLE id="Table1" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 112px" cellSpacing="1"
                cellPadding="1" width="300" border="1">
                <%
            dim con1 as new oledb.oledbconnection
            dim command1 as new oledbcommand
            Dim sql1 As String
            dim dr1
            con1 = New OleDbConnection(ConfigurationSettings.AppSettings( "connectionstring1"))
            con1.Open()
            sql1="select top 5 city_name,city_id from city where city_id in (select top " & val(TextBox1.Text) & " city_id from city where city_id in (" & session("B") & ") order by city_id asc) order by city_id desc"
            command1.CommandText = sql1
            command1.Connection = con1
            dr1 = command1.ExecuteReader
            %>
                <% while dr1.read %>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <TR>
                    <TD><strong><% response.write(request.querystring("ft"))%> & nbsp;   <% response.write(dr1("city_name")) %></strong></TD>
                </TR>
                <%
            dim con as new oledb.oledbconnection
            dim command as new oledbcommand
            Dim sql As String
            dim dr
            con = New OleDbConnection(ConfigurationSettings.AppSettings( "connectionstring1"))
            con.Open()
            sql="Select top 5 c.customer_id,c.customer_name from customer c, field_under_customer fuc where fuc.customer_id in (select top " & val(TextBox1.Text) & " fuc.customer_id from customer c,field_under_customer fuc where c.city_id='" & dr1("city_id") & "'" & " and c.types=c.types and fuc.customer_id=c.customer_id and fuc.field_person_id ='"& request.querystring("fv") & "' order by c.customer_id asc) and fuc.field_person_id ='"& request.querystring("fv") & "' and fuc.customer_id=c.customer_id and c.city_id in (" & session("B") & ") and c.types=c.types order by c.customer_id desc"
            command.CommandText = sql
            command.Connection = con
            dr = command.ExecuteReader
            %>
                <TR>
                    <TD><STRONG>Customer Name</STRONG></TD>
                </TR>
                <% while dr.read %>
                <TR>
                    <TD><% response.write(dr("customer_name"))%></TD>
                </TR>
                <% end while %>
                <% end while %>
            </TABLE>
            <%

            elseif m ="Distributor" or m="Direct" then

            %>
            <TABLE id="Table2" style="Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 112px" cellSpacing="1"
                cellPadding="1" width="300" border="1">
                <%
            dim con as new oledb.oledbconnection
            dim command as new oledbcommand
            Dim sql As String
            dim dr
            con = New OleDbConnection(ConfigurationSettings.AppSettings( "connectionstring1"))
            con.Open()
            sql="Select c.customer_name,c.customer_id from customer c, field_under_customer fuc where fuc.field_person_id='" & request.querystring("fv") & "' and fuc.customer_id=c.customer_id and c.city_id in (" & request.form("l2") & ") and c.types='" & request.querystring("r") & "'"
            command.CommandText = sql
            command.Connection = con
            dr = command.ExecuteReader
            %>
                <%
            dim con1 as new oledb.oledbconnection
            dim command1 as new oledbcommand
            Dim sql1 As String
            dim dr1
            con1 = New OleDbConnection(ConfigurationSettings.AppSettings( "connectionstring1"))
            con1.Open()
            sql1="select city_name from city where city_id in (" & request.form("l2") & ")"
            command1.CommandText = sql1
            command1.Connection = con1
            dr1 = command1.ExecuteReader
            %>
                <% while dr1.read %>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <TR>
                    <TD><strong><% response.write(request.querystring("ft"))%> & nbsp;   <% response.write(dr1("city_name")) %></strong></TD>
                </TR>
                <TR>
                    <TD><STRONG>Customer Name</STRONG></TD>
                </TR>
                <% while dr.read %>
                <TR>
                    <TD><% response.write(dr("customer_name"))%></TD>
                </TR>
                <% end while %>
                <% end while %>
            </TABLE>
            <% end if %>

    </body>
</HTML>

ham · August 24th, 2004, 06:49 AM

hi

see the code below and fix the prob in it , wat changes i made to the code , so it give a desire result(i.e paging).

<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="print.aspx.vb" Inherits="g.print"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
    <HEAD>
        <title>fcustprint</title>
        <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
        <meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
        <meta content="JavaScript" name="vs_defaultClientScript">
        <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
        <script runat="server">
    Sub next1(sender As Object, e As EventArgs)
         TextBox1.Text = Val(TextBox1.Text) + 5
    End Sub
    Sub prev1(sender As Object, e As EventArgs)
         TextBox1.Text = Val(TextBox1.Text) - 5
    End Sub
        </script>
    </HEAD>
    <body MS_POSITIONING="GridLayout">
        <form id="Form1" method="post" runat="server">
            <asp:textbox id="TextBox1" style="Z-INDEX: 103; LEFT: 296px; POSITION: absolute; TOP: 80px" runat="server"
                Height="16px" Width="112px"></asp:textbox><asp:linkbutton id="lb1" style="Z-INDEX: 105; LEFT: 240px; POSITION: absolute; TOP: 88px" onclick="next1"
                runat="server" Height="16px" Width="32px">Next</asp:linkbutton><asp:linkbutton id="LinkButton1" style="Z-INDEX: 106; LEFT: 168px; POSITION: absolute; TOP: 88px"
                onclick="prev1" runat="server" Height="16px" Width="40px">Previuos</asp:linkbutton></form>
        <strong>Geofman Pharmaceutical
        <br>
        Field Person Cities Customers Report</STRONG>
        <br>
        Report Date and Time :<%response.write(date.now()) %>
            <br>
            <br>
            Type :
                <% response.write(request.querystring("r"))%>

            <%
           dim m as string
           m = request.querystring("r")
           if m ="All" then
        %>
            <TABLE id="Table1" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 112px" cellSpacing="1"
                cellPadding="1" width="300" border="1">
                <%
            dim con1 as new oledb.oledbconnection
            dim command1 as new oledbcommand
            Dim sql1 As String
            dim dr1
            con1 = New OleDbConnection(ConfigurationSettings.AppSettings( "connectionstring1"))
            con1.Open()
            sql1="select top 5 city_name,city_id from city where city_id in (select top " & val(TextBox1.Text) & " city_id from city where city_id in (" & session("B") & ") order by city_id asc) order by city_id desc"
            command1.CommandText = sql1
            command1.Connection = con1
            dr1 = command1.ExecuteReader
            %>
                <% while dr1.read %>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <TR>
                    <TD><strong><% response.write(request.querystring("ft"))%> & nbsp;   <% response.write(dr1("city_name")) %></strong></TD>
                </TR>
                <%
            dim con as new oledb.oledbconnection
            dim command as new oledbcommand
            Dim sql As String
            dim dr
            con = New OleDbConnection(ConfigurationSettings.AppSettings( "connectionstring1"))
            con.Open()
            sql="Select top 5 c.customer_id,c.customer_name from customer c, field_under_customer fuc where fuc.customer_id in (select top " & val(TextBox1.Text) & " fuc.customer_id from customer c,field_under_customer fuc where c.city_id='" & dr1("city_id") & "'" & " and c.types=c.types and fuc.customer_id=c.customer_id and fuc.field_person_id ='"& request.querystring("fv") & "' order by c.customer_id asc) and fuc.field_person_id ='"& request.querystring("fv") & "' and fuc.customer_id=c.customer_id and c.city_id in (" & session("B") & ") and c.types=c.types order by c.customer_id desc"
            command.CommandText = sql
            command.Connection = con
            dr = command.ExecuteReader
            %>
                <TR>
                    <TD><STRONG>Customer Name</STRONG></TD>
                </TR>
                <% while dr.read %>
                <TR>
                    <TD><% response.write(dr("customer_name"))%></TD>
                </TR>
                <% end while %>
                <% end while %>
            </TABLE>
            <%

            elseif m ="Distributor" or m="Direct" then

            %>
            <TABLE id="Table2" style="Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 112px" cellSpacing="1"
                cellPadding="1" width="300" border="1">
                <%
            dim con as new oledb.oledbconnection
            dim command as new oledbcommand
            Dim sql As String
            dim dr
            con = New OleDbConnection(ConfigurationSettings.AppSettings( "connectionstring1"))
            con.Open()
            sql="Select c.customer_name,c.customer_id from customer c, field_under_customer fuc where fuc.field_person_id='" & request.querystring("fv") & "' and fuc.customer_id=c.customer_id and c.city_id in (" & request.form("l2") & ") and c.types='" & request.querystring("r") & "'"
            command.CommandText = sql
            command.Connection = con
            dr = command.ExecuteReader
            %>
                <%
            dim con1 as new oledb.oledbconnection
            dim command1 as new oledbcommand
            Dim sql1 As String
            dim dr1
            con1 = New OleDbConnection(ConfigurationSettings.AppSettings( "connectionstring1"))
            con1.Open()
            sql1="select city_name from city where city_id in (" & request.form("l2") & ")"
            command1.CommandText = sql1
            command1.Connection = con1
            dr1 = command1.ExecuteReader
            %>
                <% while dr1.read %>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <tr>
                </tr>
                <TR>
                    <TD><strong><% response.write(request.querystring("ft"))%> & nbsp;   <% response.write(dr1("city_name")) %></strong></TD>
                </TR>
                <TR>
                    <TD><STRONG>Customer Name</STRONG></TD>
                </TR>
                <% while dr.read %>
                <TR>
                    <TD><% response.write(dr("customer_name"))%></TD>
                </TR>
                <% end while %>
                <% end while %>
            </TABLE>
            <% end if %>

    </body>
</HTML>

Imar · August 25th, 2004, 01:27 AM

When I look at your code, I don't understand two things:

1. Why are you using ASP.NET at all? You're not making use of any of the benefits of .NET. Instead, you're creating the old skool classic ASP spaghetti style code. If I were you, I'd try to stick to ASP.NET style, or go back using ASP again. Mixing those two concepts will get you in deep trouble.

2. If you insist on using .NET, take another look at the article I pointed you to. It uses a Repeater and an array. A Repeater is NOTHING more than the data / layout you define. Want an HTML table as output? Sure, put the opening <table> tag in the HeaderTemplate, and define a row in each ItemTemplate. I think you're sticking to old methods because you are unfamiliar with how things work in the .NET world. Take some time to investigate this new environment.

Also, binding shouldn't be too hard. The easiest (although maybe not the cleanest) way to do this, is to use the code from the article as is. And instead of assigning a bunch of numbers to the array as the datasource, fill the array with the records from the DataReader.
That will cause some overhead, but it will be clean and easy to implement.

You also seem to be using Visual Studio .NET. If you are, have you ever investigated the Code Behind feature?

If you insist to stick to mixing classic ASP coding with .NET technologies, I am afraid this paging application is going to be one big mess, and I cannot help you much more than this.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

ham · August 25th, 2004, 04:29 AM

thanx for ur reply

can u again point me to the article that u r mentionng in ur previous post?