pass parameters into a url after executing a SQL Query

Question

I recently asked a questtion on how o write a query and thankfully you guys answered me (Thank you)

My Last Question

Im working on a WML site so I need to pass the values from the query I found a way to do so (shown below in code) , but It didnt work.

    <%   
//this Query was provided earlier in the previous question.

        string queryString = "select st.firstname + ' ' + st.lastname,se.year, c.coursename,c.NumberOfCredits,ri.mark  from Students st inner join RegisteredIn ri on ri.StudentId=st.id inner join Semester se on se.id=ri.SemesterId inner join Courses c on c.id=se.id ";

the Way I found but didnt work

         using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
         {

            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(queryString, connection);

            command.Parameters.AddWithValue("@StudentId",StudentId);

                connection.Open();
                System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    string _firstname = reader[0].ToString();
                    string _lastname = reader[1].ToString();
                    string _coursename = reader[2].ToString();
                    string _credits = reader[3].ToString();
                    string _mark = reader[4].ToString();

                    string url = string.Format("~/StudentInfo.aspx?StudentId={0}&firstname={1}&lastname={2}&coursename={3}&credits={4}&mark={5}", StudentId, _firstname, _lastname, _coursename, _credits, _mark);
                    Response.Redirect(url);

    %>

The page reciving the paramaters will be somthing like

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="StudentInfo.aspx.cs" Inherits="StudentInfo" %>

<wml>
    <card>



        <p><% Response.Write(Request.Params["StudentId"]);%></p>
        <p><b>firstname:</b> <% Response.Write(Request.Params["firstname"]);%></p>
        <p><b>lastname:</b> <% Response.Write(Request.Params["lastname"]);%></p>
        <p><b>coursename:</b> <% Response.Write(Request.Params["coursename"]);%></p>
       <p><b>credits:</b> <% Response.Write(Request.Params["credits"]);%></p>
        <p><b>Mark:</b> <% Response.Write(Request.Params["mark"]);%></p>

    </card>
</wml>

the result will be somthing like

First Semester  2010
Student : Arin Rizk

Course Name     No. of Credit      Mark
    AAA                3            65
    BBB                3            23
    CCC                3            65
    DDD                3            58
    EEE                3            70

Your GPA for this semster is 3.12

My Questions are: - How to extract the values from the query and into the URL ? - How I will display multiple values in WML or in ASP ? (as the results will be multiple lines and of values)

Thank you in advance

Answer 1

Your code as it is now will read only first row from database. To read all rows, you must call Reader.Read() in a loop, something like:

while(reader.Read())
{
    string _firstname = reader[0].ToString();
    string _lastname = reader[1].ToString()
    ...
}

But I'm afraid that passing all this information as parameter in URL really is not good idea. You would have to join somehow results from all rows and on the other page split them back to individual values, which seems to me superfluous. Moreover, URL length is usually limited so if your query returns more than just a few rows, it will not work. Why do you want to transfer whole query result in URL? It's more common (and much easier to do) to transfer in URL just identifier of student (or whatever you want to show) and on the second page read data again from database.

UPDATE: If I understand you correctly, your actual question is not how to pass result of database query in querystring to another page, but you want to display result of your query on page. First step is to add appropriate control (Label, DataGridView, etc.) on the form of second page. Then depending on what control you have used you can either bind control to data source, or add data to control manually in method such as Page_Load in StudentInfo.aspx file. But since this is completely different topic than what you were asking first, I suggest you to ask another question. Also this topic was dicsussed here many times, so you may take a look on these questions first.