Pass parameter id to sql query using url routing

Question

I'm a beginner asp.net developer

Trying to create my first simple news portal pages.

Here is what I have:

Database
Admin_News.aspx to add news into database
Default.aspx to display all news with the title linkable
NewsDetails.aspx to display the details by ID when they click on the title in Default.aspx

How I did That:

in Default.aspx I used this code:

<div>
    <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
        <HeaderTemplate>
            <table border="1">
                <tr>
                    <td>
                        <b>title</b>
                    </td>
                    <td>
                        <b>news</b>
                    </td>
                    <td>
                        <b>imges</b>
                    </td>

                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td>
                    <asp:HyperLink runat="server" ID="hl" NavigateUrl='<%#"~/NewsDetails.aspx?id=" + Eval("id")%>' Text='<%# Eval("title") %>'></asp:HyperLink>
                </td>
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "news")%>
                </td>
                <td>
                    <img alt="" src='<%# DataBinder.Eval(Container.DataItem, "imageurl")%>' />
                </td>

            </tr>

        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MoneyHolderConnectionString %>"
        SelectCommand="SELECT [id], [title], [news], [imageurl], [detail] FROM [News]"></asp:SqlDataSource>
</div>

NewsDetails.aspx code:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:MoneyHolderConnectionString %>" 
        SelectCommand="SELECT [title], [news], [imageurl], [detail] FROM [News] WHERE ([id] = @id)">
        <SelectParameters>
            <asp:QueryStringParameter Name="id" QueryStringField="id" Type="Int64" />
        </SelectParameters>
    </asp:SqlDataSource>


    <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2">
            <HeaderTemplate>

            </HeaderTemplate>
            <ItemTemplate>
               <h1 align="right"><br />
                 <strong><p style="color:#000">
                     <%# DataBinder.Eval(Container.DataItem, "title")%>
                 </strong></h1><p/><br />
                 <br />

                 <p align="center"><img src='<%# DataBinder.Eval(Container.DataItem, "imageurl")%>'/>
                 <p/>
                 <br />
                 <br />

                 <p id="detail" align="right" style="font-size:25px"><%# DataBinder.Eval(Container.DataItem, "detail")%><p/>
                 <br />

            </ItemTemplate>
            <FooterTemplate>

            </FooterTemplate>
  </asp:Repeater>

so what I'm doing is displaying the article according to the ID in .aspx?id= in the url. everything was working fine.. until I used the url routing.

I changed/added some codes to change the structure of the url I don't want it to appear like ~/NewsDetails.aspx?id=1 I want to be like ~/News/1 instead with the same result.

So I have added this code to Global.asax:

void Application_Start(object sender, EventArgs e) 
{
    // Code that runs on application startup
    RegisterRoutes(RouteTable.Routes);
}
public static void RegisterRoutes(RouteCollection routeCollection)
{
    routeCollection.MapPageRoute("RouteForNews", "News/{id}", "~/NewsDetails.aspx");
}

and I added this code to the NewsDetails.aspx.cs code behind:

string id = Page.RouteData.Values["id"].ToString();

and I changed the NavigateUrl in NewsDetails.aspx to:

NavigateUrl='<%#"~/News/" + Eval("id")%>'

now when I open Default.aspx the news appear with the titles linkable to ~/News/"id number", when i click on a title, NewsDetails.aspx opens with the link of /News/idnumber but no data inside..its empty i only can see the design of the master page.

I would appreciate any help from you what should I do to display the news, the id value in the url it goes to string id variable but I don't know how to pass it to the sql query (i'm not sure if this is the problem)

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

You should not use the asp:QueryStringParameter in the asp:SqlDataSource in the NewsDetails page, as your parameter is no longer in the QueryString. A QueryString is the part of URL that comes after the question mark.

Use this link to know how to pass parameters to select statement:

How to pass variable to SelectCommand of SqlDataSource?

edited May 23 '17 at 12:27

Community

1
1

answered Jul 08 '12 at 12:48

Zasz

12,330
9
43
63

I have added this code in NewsDetails.aspx.cs behind code: `SqlDataSource1.SelectParameters.Add("@id", id);` and changed the selectparameter to this: ` ` i get this error when i run the page: Server Error in '/' Application. Error converting data type nvarchar to bigint. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. – ElieLB Jul 08 '12 at 13:12
I removed the selectparameter code and added this code in .cs: `SqlDataSource2.SelectParameters.Add("id", DbType.Int32, id);` it worked ! thanks alot – ElieLB Jul 08 '12 at 13:28

score 0 · Answer 2 · answered Jul 11 '13 at 06:31

0

connectionstring="<%$ ConnectionStrings:MyConnection %>"
selectcommand="SELECT Name, Age FROM ContactInfo where State=@StateCode">

answered Jul 11 '13 at 06:31

Mr. Balbinder

1
1

Pass parameter id to sql query using url routing

2 Answers2