asked on

Stored procedure using C# and sqlDataReader

Hi

I posted this question in the Dreamweaver section, but unfortunatley received no reply.

I'm trying to set up paging through a repeater region.
I can do it using a sql statement, but the books I have briefly touch on stored procedures.

As this is custom code, Dreamweaver does not create the code for you and as a result i'm stuck.

I have supplied the code below for the repeater region and paging. Could anyone help and create the sqlDataReader so that I can complete this page. Database is SQL2000

<script runat="server">
string FirstWord, SecondWord, ThirdWord, FourthWord, FifthWord, SixthWord, SeventhWord, QueryPhrase;
PagedDataSource Pager = new PagedDataSource();
void Page_Load(Object Src, EventArgs E)
{

            Pager.AllowPaging = true;
            Pager.PageSize = 10;
            Pager.CurrentPageIndex = 0;

            if(!Page.IsPostBack)
            {
                  DoDataBinding();
            }
      }

      void ChangePage(object sender, System.EventArgs e)
      {
            LinkButton Btn = (LinkButton)sender;
            Pager.CurrentPageIndex = Convert.ToInt32(Btn.CommandArgument);
            DoDataBinding();
      }
      void DoDataBinding()
      {

//Need to change this section to stored procedure

            System.Data.OleDb.OleDbConnection ObjConn;
            System.Data.OleDb.OleDbDataAdapter ObjDa;
            System.Data.DataTable DisplayResults = new System.Data.DataTable();

            ObjConn = new System.Data.OleDb.OleDbConnection("*****");
            ObjConn.Open();

            ObjDa = new System.Data.OleDb.OleDbDataAdapter("*****, ObjConn);
            ObjDa.Fill(DisplayResults);
//End stored procedure section

      if(Pager.CurrentPageIndex < 0)
            {
                  Pager.CurrentPageIndex = 0;
            }

            Pager.DataSource = DisplayResults.DefaultView;
            ViewResults.DataSource = Pager;
            Page.DataBind();

            lnkFirst.Enabled = Pager.CurrentPageIndex > 0;
            lnkBack.Enabled = Pager.CurrentPageIndex > 0;
            lnkNext.Enabled = Pager.CurrentPageIndex < (Pager.PageCount - 1);
            lnkLast.Enabled = Pager.CurrentPageIndex < (Pager.PageCount - 1);

      }
</script>
</head>
<body>
<form name="QueryResults" id="ViewQueryResults" runat="server">
<ASP:Repeater ID="ViewResults" runat="server">
<ItemTemplate><br><br><strong>Name:</strong> <%# DataBinder.Eval(Container.DataItem, "name") %><br>
<strong>Address:</strong> <%# DataBinder.Eval(Container.DataItem, "Address") %>
</ItemTemplate>
</ASP:Repeater>
<asp:LinkButton ID="lnkFirst" Text="First" CommandArgument="0" OnClick="ChangePage" runat="server"></asp:LinkButton>
<asp:LinkButton Text="Next" CommandArgument="<%# Pager.CurrentPageIndex + 1 %>" OnClick="ChangePage" runat="server" ID="lnkNext"></asp:LinkButton>
<asp:LinkButton Text="Back" CommandArgument="<%# Pager.CurrentPageIndex - 1 %>" OnClick="ChangePage" runat="server" ID="lnkBack"></asp:LinkButton>
<asp:LinkButton Text="Last" CommandArgument="<%# Pager.PageCount - 1 %>" OnClick="ChangePage" runat="server" ID="lnkLast"></asp:LinkButton>
Page <%# Pager.CurrentPageIndex + 1 %> Of <%# Pager.PageCount %>
</form>

The stored procedure takes the following parameters.

<Parameter Name="@QueryPhrase" Value='<%# ((Request.QueryString["Query"] != null) && (Request.QueryString["Query"].Length > 0)) ? Request.QueryString["Query"] : "Default Value" %>' Type="VarChar" Size="100" Direction="Input" />
<Parameter Name="@WordOne" Value='<%# (FirstWord != "") ? FirstWord : "Default Value" %>' Type="VarChar" Size="100" Direction="Input" />
<Parameter Name="@WordTwo" Value='<%# (SecondWord != "") ? SecondWord : "Default Value" %>' Type="VarChar" Size="100" Direction="Input" />
<Parameter Name="@Wordthree" Value='<%# (ThirdWord != "") ? ThirdWord : "Default Value" %>' Type="VarChar" Size="100" Direction="Input" />
<Parameter Name="@Wordfour" Value='<%# (FourthWord != "") ? FourthWord : "Default Value" %>' Type="VarChar" Size="100" Direction="Input" />
<Parameter Name="@Wordfive" Value='<%# (FifthWord != "") ? FifthWord : "Default Value" %>' Type="VarChar" Size="100" Direction="Input" />
<Parameter Name="@Wordsix" Value='<%# (SixthWord != "") ? SixthWord : "Default Value" %>' Type="VarChar" Size="100" Direction="Input" />

Any help would be appreciated.
George

ASKER CERTIFIED SOLUTION

glsac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial