current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm trying to replicate the binding in my markup with something programmatic. It seems I am forced to use a dataset to bind to rather than a SqlDataSource as shown in my markup. Furthermore, when using a dataset, I suddenly lose paging, sorting, etc., and get errors that I need to handle them manually. They were handled automatically before??

So here is my markup:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" 
    CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" 
    GridLines="None" AllowPaging="True" AllowSorting="True" Width="520px">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
    </Columns>
</asp:GridView>   
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" 
    SelectCommand="CustOrdersOrders" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
        <asp:ControlParameter ControlID="txtCustId" DefaultValue="AROUT" 
            Name="CustomerID" PropertyName="Text" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

I replaced it with something like this:

SqlDataAdapter adapter = new SqlDataAdapter();                
conn.Open();
using (SqlConnection conn = new SqlConnection(connStr))
{
    SqlDataAdapter adapter = new SqlDataAdapter();

    conn.Open();

    using (SqlCommand cmd = new SqlCommand("CustOrdersOrders", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("CustomerID", "AROUT");
        cmd.ExecuteScalar();
        adapter.SelectCommand = cmd;
    }

    GridView1.DataSource = adapter;                
    GridView1.DataBind();
}

This ofcourse raises an error that that the data source is not of the correct type, yet this is exactly what the markup does. The only thing I can think of is that is uses DataSource instead of DataSourceID. So how do I replicate this markup and get all the sorting and paging automatically? If I have to use a dataset, why does the markup not require one? But, even with the dataset, it doesn't seem to handle the paging and sorting automatically like I said.

Thanks! Levi

share|improve this question
    
Don't misunderstand dataset, you are not using dataset. DataSet allows both sorting and paging. It is the datareader that doesn't allow these stuffs. DataReader is unidirectional. Either use the SqlDataSource which by default uses DataSet or follow the instructions in the Shyju's answer that also suggests you to use dataset. –  nuux Jan 20 '12 at 3:28
add comment

1 Answer

up vote 2 down vote

You can not set the DataAdapter as the DataSource property value.SqlDataAdapter provides the communication between the Dataset and the SQL database. You must be using the data adapter to fill a DataTable /DataSet and use that as the DataSource.

Your code had some other problems

1) You are trying to open a connection before declaring it.

2) Sql Data adapter object is created twice with same name.

The below code will work 

using (SqlConnection conn = new SqlConnection(connStr))
{
    SqlDataAdapter adapter = new SqlDataAdapter();    
    conn.Open();
    DataTable dt = new DataTable();
    using (SqlCommand cmd = new SqlCommand("CustOrdersOrders", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("CustomerID", "AROUT");           
        adapter.SelectCommand = cmd;
        adapter.Fill(dt);
    }

    GridView1.DataSource = dt;                
    GridView1.DataBind();
}

http://www.dotnetperls.com/sqldataadapter

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx

share|improve this answer
    
Thanks, i will give this a try. The duplicate variables items is just something weird with the cut and paste. So why in the markup is it able to assign the gridview datasource directly to the sqldataadapter? I tried using a dataset instead of a datatable in my earlier efforts... Maybe that is why the sorting and so forth were not handled by default. Thanks again –  KingOfHypocrites Jan 20 '12 at 0:35
1  
When I use datatable, it doesn't seem to support paging, etc. automatically. When I try to go to the next page, I get this error: The GridView 'GridView1' fired event PageIndexChanging which wasn't handled. This is confusing since it is handled automatically when I bind through the markup. Any ideas? –  KingOfHypocrites Jan 23 '12 at 15:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.