CODEWITHSUNDEEP
c#asp.netsqldatasource
KingOfHypocrites
KingOfHypocrites

Reputation: 9547

Binding GridView to SqlDataSource programmatically

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

Upvotes: 1

Views: 11883

Answers (1)

Shyju
Shyju

Reputation: 218942

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

Upvotes: 2

Related Questions