Data source does not support server-side data loading

Question

I'm currently trying to swap for my gridview, but as soon as I enable paging in my gridview, it will give me this error: the data source does not support server side swapping.

this is my code for gridview:

SqlDataReader reader = cmd.ExecuteReader(); GridView1.DataSource = reader; GridView1.DataSourceID = null; GridView1.Visible = true; GridView1.AllowPaging= true; GridView1.DataBind(); conn.Close(); 

Answer 1

SqlDataReader - just go ahead. Server-side paging should be able to move the data source back and forth. Use a different data source, such as SqlDataAdapter , which supports bidirectional crawl.

Example (on request):

 string query = string.Empty; SqlConnection conn = null; SqlCommand cmd = null; SqlDataAdapter da = null; DataSet ds = null; try { query = "SELECT * FROM table WHERE field = @value"; conn = new SqlConnection("your connection string"); cmd = new SqlCommand(query, conn); cmd.Parameters.Add("value", SqlDbType.VarChar, 50).Value = "some value"; da = new SqlDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); if (ds.Tables.Count > 0) { GridView1.DataSource = ds.Tables(0); GridView1.AllowPaging = true; GridView1.DataBind(); } } catch (SqlException ex) { //handle exception } catch (Exception ex) { //handle exception } finally { if (da != null) { da.Dispose(); } if (cmd != null) { cmd.Dispose(); } if (conn != null) { conn.Dispose(); } } 

SqlDataAdapter also located in the System.Data.SqlClient namespace.

Answer 2

Have you tried using SqlDataAdapter to populate a DataSet / DataTable with your SQL results? Then use this DataTable as the data source for the GridView. The main framework for populating your DataTable:

 public DataTable GetDataTable(String connectionString, String query) { DataTable dataTable = new DataTable(); try { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand(query, connection)) { using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command)) { dataAdapter.Fill(dataTable); } } } } catch { } return dataTable; } 

And then you can use this DataTable as a GridView data source:

 String connectionString = "Data Source=<datasource>;Initial Catalog=<catalog>;User Id=<userID>;Password=<password>;"; String query = "SELECT * FROM TABLE_NAME WHERE ID=BLAH"; GridView1.DataSource = GetDataTable(connectionString, query); GridView1.DataSourceID = null; GridView1.Visible = true; GridView1.AllowPaging= true; GridView1.DataBind(); 

Hope this helps.

Answer 3

You can apply a search call to gridview in two ways.

(1) Use an object data source using gridview

(2) Use jquery datatable