datatable.Select() and datagrid results

jcochran · Aug 21, 2005

Greetings,

I am using a datatable and a datagrid. What I am trying to do is allow
a person to search the datagrid. I have the select working but it
reverts back to the default datatable view when I try to edit a row in
the datagrid; even when I clear the original data. the dtLeases table
is what I am concerned about.

BEGIN CODE ==================================

public class leases_list : System.Web.UI.Page
{
DataTable dtLeases;
DataTable dtCounties = new DataTable();
DataTable dtShippers = new DataTable();

protected System.Web.UI.WebControls.DataGrid MyDataGrid;
protected System.Web.UI.WebControls.Button btnRefresh;
protected System.Web.UI.WebControls.Button btnSearch;
protected System.Web.UI.WebControls.DropDownList ddlColumn;
protected System.Web.UI.WebControls.TextBox txtKeyword;
protected System.Web.UI.WebControls.Label lblStatus;
protected System.Web.UI.WebControls.RequiredFieldValidator
rfvKeyword;
protected System.Web.UI.WebControls.Panel pnlLeases;

void Page_Load(Object sender, EventArgs E )
{
if(dtLeases == null)
{
dtLeases = new DataTable();
GetLeaseData();
BindGrid();
}
}

public void MyDataGrid_PageIndexChanged(Object sender,
DataGridPageChangedEventArgs e)
{
MyDataGrid.CurrentPageIndex = e.NewPageIndex;
MyDataGrid.EditItemIndex = -1;

BindGrid();
}

protected void Sort_Grid(Object sender, DataGridSortCommandEventArgs
e)
{
MyDataGrid.CurrentPageIndex = 0;

string sSortOrder = "";

if(ViewState["SortOrder"] == null)
{
sSortOrder = "DESC";
}
else if(ViewState["SortOrder"].ToString() == "ASC")
{
sSortOrder = "DESC";
}
else if(ViewState["SortOrder"].ToString() == "DESC")
{
sSortOrder = "ASC";
}

ViewState["SortOrder"] = sSortOrder;

DataView dv = new DataView(dtLeases);
dv.Sort = e.SortExpression.ToString() + " " + sSortOrder;

MyDataGrid.DataSource = dv;
MyDataGrid.DataBind();
}

public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs
e)
{
MyDataGrid.EditItemIndex = e.Item.ItemIndex;
BindGrid();

txtKeyword.Enabled = false;
ddlColumn.Enabled = false;
btnSearch.Enabled = false;
btnRefresh.Enabled = false;
rfvKeyword.Enabled = false;
}

public void MyDataGrid_Cancel(Object sender, DataGridCommandEventArgs
e)
{
MyDataGrid.EditItemIndex = -1;
BindGrid();

txtKeyword.Enabled = true;
ddlColumn.Enabled = true;
btnSearch.Enabled = true;
btnRefresh.Enabled = true;
rfvKeyword.Enabled = true;
}

public void MyDataGrid_Update(Object sender, DataGridCommandEventArgs
e)
{
// update the lease information
// set up the connection
SqlConnection oCon = new
SqlConnection(ConfigurationSettings.AppSettings["sqlConString"]);
SqlCommand oCom;

try
{
// open up the connection
oCon.Open();

// create the command
oCom = new SqlCommand("spUpdateLease", oCon);
oCom.CommandType = CommandType.StoredProcedure;

// set up the parameters
SqlParameter parmLeaseID = oCom.Parameters.Add(new
SqlParameter("@LeaseID",SqlDbType.Int));
parmLeaseID.Direction = ParameterDirection.Input;
parmLeaseID.Value =
(int)MyDataGrid.DataKeys[(int)e.Item.ItemIndex];

SqlParameter parmAssocName = oCom.Parameters.Add( new
SqlParameter("@LeaseName",SqlDbType.VarChar,50));
parmAssocName.Direction = ParameterDirection.Input;
parmAssocName.Value = ((TextBox)e.Item.Cells[1].Controls[0]).Text;

SqlParameter parmCountyID = oCom.Parameters.Add( new
SqlParameter("@CountyID",SqlDbType.Int));
parmCountyID.Direction = ParameterDirection.Input;
parmCountyID.Value =
Int32.Parse(((DropDownList)(e.Item.FindControl("ddlCountyID"))).SelectedItem.Value);

SqlParameter parmShipperID = oCom.Parameters.Add( new
SqlParameter("@ShipperID",SqlDbType.Int));
parmShipperID.Direction = ParameterDirection.Input;
parmShipperID.Value =
Int32.Parse(((DropDownList)(e.Item.FindControl("ddlShipperID"))).SelectedItem.Value);

SqlParameter parmAccountID = oCom.Parameters.Add( new
SqlParameter("@AccountID",SqlDbType.Int));
parmAccountID.Direction = ParameterDirection.Input;
parmAccountID.Value = Int32.Parse(Session["AccountID"].ToString());

oCom.ExecuteNonQuery();
}
catch(Exception oError)
{
Response.Write("ERROR: " + oError.Message + " " +
oError.StackTrace + "");
}
finally
{
oCon.Close();
}

MyDataGrid.EditItemIndex = -1;

BindGrid();

lblStatus.Text = "Lease Updated!";

txtKeyword.Enabled = true;
ddlColumn.Enabled = true;
btnSearch.Enabled = true;
btnRefresh.Enabled = true;
rfvKeyword.Enabled = true;
}

public void MyDataGrid_ItemCreated(object sender,
DataGridItemEventArgs e)
{

}

private void btnSearch_Click(object sender, System.EventArgs e)
{
lblStatus.Text = "";

// columns and keyword
string sColumn = ddlColumn.SelectedValue.ToString();
string sKeyword = txtKeyword.Text.Trim();

DataRow[] foundRows;

// search for a match
foundRows = dtLeases.Select(sColumn + " LIKE '%" + sKeyword + "%'");

// if we found a match
if(foundRows.Length != 0)
{
DataTable dtTemp = dtLeases.Clone();
foreach (DataRow row in foundRows)
{
dtTemp.ImportRow(row);
}

dtLeases = dtTemp.Copy();

MyDataGrid.DataSource = dtLeases;
MyDataGrid.DataBind();

lblStatus.Text = foundRows.Length + " records found for that
search.";
}
else
{
lblStatus.Text = "Nothing found for that search.";
}
}

private void btnRefresh_Click(object sender, System.EventArgs e)
{
txtKeyword.Text = "";
lblStatus.Text = "";

GetLeaseData();
BindGrid();
}

private void GetLeaseData()
{
SqlConnection oCon = new
SqlConnection(ConfigurationSettings.AppSettings["sqlConString"]);

SqlCommand oCom = new SqlCommand("spGetLeases", oCon);
oCom.CommandType = CommandType.StoredProcedure;

// set up the parameters
if(User.IsInRole("Manager"))
{
SqlParameter parmAccountID = oCom.Parameters.Add( new
SqlParameter("@AccountID",SqlDbType.Int));
parmAccountID.Direction = ParameterDirection.Input;
parmAccountID.Value = Int32.Parse(Session["AccountID"].ToString());
}

try
{
SqlDataAdapter da = new SqlDataAdapter(oCom);
da.Fill(dtLeases);
}
catch(Exception oError)
{
Response.Write("ERROR: " + oError.Message + " " +
oError.StackTrace + "");
}
}

public DataTable GetCounties()
{
SqlConnection oCon = new
SqlConnection(ConfigurationSettings.AppSettings["sqlConString"]);

SqlCommand oCom = new SqlCommand("spGetCounties", oCon);
oCom.CommandType = CommandType.StoredProcedure;

try
{
SqlDataAdapter da = new SqlDataAdapter(oCom);
da.Fill(dtCounties);
}
catch(Exception oError)
{
Response.Write("ERROR: " + oError.Message + " " +
oError.StackTrace + "");
}

return dtCounties;
}

public DataTable GetShippers()
{
SqlConnection oCon = new
SqlConnection(ConfigurationSettings.AppSettings["sqlConString"]);

SqlCommand oCom = new SqlCommand("spGetShippers", oCon);
oCom.CommandType = CommandType.StoredProcedure;

try
{
SqlDataAdapter da = new SqlDataAdapter(oCom);
da.Fill(dtShippers);
}
catch(Exception oError)
{
Response.Write("ERROR: " + oError.Message + " " +
oError.StackTrace + "");
}

return dtShippers;
}

void BindGrid()
{
MyDataGrid.DataSource = dtLeases;
MyDataGrid.DataBind();
}

public int GetCountyIndex(string sCountyID)
{
int iCountyID = 0;

// Loop through each row in the DataSet
for(int i = 0; i < dtCounties.Rows.Count - 1; i++)
{
if(Int32.Parse(sCountyID) ==
Int32.Parse(dtCounties.Rows["CountyID"].ToString()))
{
iCountyID = i;
}
}

return iCountyID;
}

public int GetShipperIndex(string sShipperID)
{
int iShipperID = 0;

// Loop through each row in the DataSet
for(int i = 0; i < dtShippers.Rows.Count - 1; i++)
{
if(Int32.Parse(sShipperID) ==
Int32.Parse(dtShippers.Rows["ShipperID"].ToString()))
{
iShipperID = i;
}
}

return iShipperID;
}

END CODE ====================================

jcochran · Aug 21, 2005

Here is the main page code

BEGIN CODE ============================================

<form id="lease_list" method="post" runat="server">
<a href="leases_add.aspx">New Lease</a>
 
 
<asp

anel id="pnlLeases" runat="server">
<table cellspacing="1" cellpadding="1" border="0">
<tr>
<td>Search for:
</td>
<td>
<asp:textbox id="txtKeyword" runat="server"></asp:textbox></td>
<td>in</td>
<td>
<asp:dropdownlist id="ddlColumn" runat="server">
<asp:listitem value="LeaseName">Lease</asp:listitem>
<asp:listitem value="ShipperName">Shipper</asp:listitem>
<asp:listitem value="CountyName">County</asp:listitem>
<asp:listitem value="Manager">Creator</asp:listitem>
</asp:dropdownlist></td>
<td>
<asp:button id="btnSearch" runat="server" text="Search"
width="96px"></asp:button> 
<asp:button id="btnRefresh" runat="server" text="Refresh"
width="96px" causesvalidation="False"></asp:button></td>
<td>
<asp:requiredfieldvalidator id="rfvKeyword" runat="server"
errormessage="Please enter a keyword(s)." display="Dynamic"
controltovalidate="txtKeyword"></asp:requiredfieldvalidator></td>
</tr>
</table>
<asp:label id="lblStatus" runat="server" forecolor="Red"
font-bold="True"></asp:label>
 
 
<asp:datagrid id="MyDataGrid" runat="server"
onupdatecommand="MyDataGrid_Update" oncancelcommand="MyDataGrid_Cancel"
oneditcommand="MyDataGrid_Edit" gridlines="Horizontal"
autogeneratecolumns="False" cellpadding="3"
borderwidth="1px" pagesize="40" font-name="Verdana"
font-size="8pt" onsortcommand="Sort_Grid"
allowpaging="True" pagerstyle-mode="NumericPages"
pagerstyle-horizontalalign="Right"
onpageindexchanged="MyDataGrid_PageIndexChanged"
bordercolor="Black" datakeyfield="LeaseID"
headerstyle-backcolor="#aaaadd"
alternatingitemstyle-backcolor="#eeeeee"
allowsorting="True" font-names="Verdana" enableviewstate="true">
<alternatingitemstyle backcolor="#EEEEEE"></alternatingitemstyle>
<headerstyle verticalalign="Top"
backcolor="Gainsboro"></headerstyle>
<columns>
<asp:editcommandcolumn buttontype="LinkButton"
canceltext="Cancel" edittext="Edit" headertext=""
updatetext="Update"></asp:editcommandcolumn>
<asp:boundcolumn datafield="LeaseName" sortexpression="Lease"
headertext="Lease"></asp:boundcolumn>
<asp:templatecolumn headertext="Shipper">
<itemtemplate>
<%# DataBinder.Eval(Container, "DataItem.ShipperName") %>
</itemtemplate>
<edititemtemplate>
<asp:dropdownlist id="ddlShipperID" runat="server"
datasource="<%# GetShippers() %>" datatextfield="Name"
datavaluefield="ShipperID" selectedindex='<%#
GetShipperIndex(DataBinder.Eval(Container,
"DataItem.ShipperID").ToString()) %>' >
</asp:dropdownlist>
</edititemtemplate>
</asp:templatecolumn>
<asp:templatecolumn headertext="County">
<itemtemplate>
<%# DataBinder.Eval(Container, "DataItem.CountyName") %>
</itemtemplate>
<edititemtemplate>
<asp:dropdownlist id="ddlCountyID" runat="server"
datasource="<%# GetCounties() %>" datatextfield="Name"
datavaluefield="CountyID" selectedindex='<%#
GetCountyIndex(DataBinder.Eval(Container,
"DataItem.CountyID").ToString()) %>' >
</asp:dropdownlist>
</edititemtemplate>
</asp:templatecolumn>
<asp:boundcolumn datafield="Manager" sortexpression="Manager"
headertext="Creator" readonly="True"></asp:boundcolumn>
<asp:boundcolumn datafield="Date" sortexpression="Date"
headertext="Date" readonly="True"></asp:boundcolumn>
</columns>
<pagerstyle horizontalalign="Right"
mode="NumericPages"></pagerstyle>
</asp:datagrid>
</asp

anel>
</form>

END CODE ==========================================

W.G. Ryan MVP · Aug 22, 2005

It looks like the problem is with the PostBack. You are redoing the same
fill routine each time the page loads irrespective of if it's a postback or
not. If you store the DataTable in viewstate or session and then on
subsequent loads, use it instead, I think your problem will correct itself.

Greetings,

I am using a datatable and a datagrid. What I am trying to do is allow
a person to search the datagrid. I have the select working but it
reverts back to the default datatable view when I try to edit a row in
the datagrid; even when I clear the original data. the dtLeases table
is what I am concerned about.

BEGIN CODE ==================================

public class leases_list : System.Web.UI.Page
{
DataTable dtLeases;
DataTable dtCounties = new DataTable();
DataTable dtShippers = new DataTable();

protected System.Web.UI.WebControls.DataGrid MyDataGrid;
protected System.Web.UI.WebControls.Button btnRefresh;
protected System.Web.UI.WebControls.Button btnSearch;
protected System.Web.UI.WebControls.DropDownList ddlColumn;
protected System.Web.UI.WebControls.TextBox txtKeyword;
protected System.Web.UI.WebControls.Label lblStatus;
protected System.Web.UI.WebControls.RequiredFieldValidator
rfvKeyword;
protected System.Web.UI.WebControls.Panel pnlLeases;

void Page_Load(Object sender, EventArgs E )
{
if(dtLeases == null)
{
dtLeases = new DataTable();
GetLeaseData();
BindGrid();
}
}

public void MyDataGrid_PageIndexChanged(Object sender,
DataGridPageChangedEventArgs e)
{
MyDataGrid.CurrentPageIndex = e.NewPageIndex;
MyDataGrid.EditItemIndex = -1;

BindGrid();
}

protected void Sort_Grid(Object sender, DataGridSortCommandEventArgs
e)
{
MyDataGrid.CurrentPageIndex = 0;

string sSortOrder = "";

if(ViewState["SortOrder"] == null)
{
sSortOrder = "DESC";
}
else if(ViewState["SortOrder"].ToString() == "ASC")
{
sSortOrder = "DESC";
}
else if(ViewState["SortOrder"].ToString() == "DESC")
{
sSortOrder = "ASC";
}

ViewState["SortOrder"] = sSortOrder;

DataView dv = new DataView(dtLeases);
dv.Sort = e.SortExpression.ToString() + " " + sSortOrder;

MyDataGrid.DataSource = dv;
MyDataGrid.DataBind();
}

public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs
e)
{
MyDataGrid.EditItemIndex = e.Item.ItemIndex;
BindGrid();

txtKeyword.Enabled = false;
ddlColumn.Enabled = false;
btnSearch.Enabled = false;
btnRefresh.Enabled = false;
rfvKeyword.Enabled = false;
}

public void MyDataGrid_Cancel(Object sender, DataGridCommandEventArgs
e)
{
MyDataGrid.EditItemIndex = -1;
BindGrid();

txtKeyword.Enabled = true;
ddlColumn.Enabled = true;
btnSearch.Enabled = true;
btnRefresh.Enabled = true;
rfvKeyword.Enabled = true;
}

public void MyDataGrid_Update(Object sender, DataGridCommandEventArgs
e)
{
// update the lease information
// set up the connection
SqlConnection oCon = new
SqlConnection(ConfigurationSettings.AppSettings["sqlConString"]);
SqlCommand oCom;

try
{
// open up the connection
oCon.Open();

// create the command
oCom = new SqlCommand("spUpdateLease", oCon);
oCom.CommandType = CommandType.StoredProcedure;

// set up the parameters
SqlParameter parmLeaseID = oCom.Parameters.Add(new
SqlParameter("@LeaseID",SqlDbType.Int));
parmLeaseID.Direction = ParameterDirection.Input;
parmLeaseID.Value =
(int)MyDataGrid.DataKeys[(int)e.Item.ItemIndex];

SqlParameter parmAssocName = oCom.Parameters.Add( new
SqlParameter("@LeaseName",SqlDbType.VarChar,50));
parmAssocName.Direction = ParameterDirection.Input;
parmAssocName.Value = ((TextBox)e.Item.Cells[1].Controls[0]).Text;

SqlParameter parmCountyID = oCom.Parameters.Add( new
SqlParameter("@CountyID",SqlDbType.Int));
parmCountyID.Direction = ParameterDirection.Input;
parmCountyID.Value =
Int32.Parse(((DropDownList)(e.Item.FindControl("ddlCountyID"))).SelectedItem.Value);

SqlParameter parmShipperID = oCom.Parameters.Add( new
SqlParameter("@ShipperID",SqlDbType.Int));
parmShipperID.Direction = ParameterDirection.Input;
parmShipperID.Value =
Int32.Parse(((DropDownList)(e.Item.FindControl("ddlShipperID"))).SelectedItem.Value);

SqlParameter parmAccountID = oCom.Parameters.Add( new
SqlParameter("@AccountID",SqlDbType.Int));
parmAccountID.Direction = ParameterDirection.Input;
parmAccountID.Value = Int32.Parse(Session["AccountID"].ToString());

oCom.ExecuteNonQuery();
}
catch(Exception oError)
{
Response.Write("ERROR: " + oError.Message + " " +
oError.StackTrace + "");
}
finally
{
oCon.Close();
}

MyDataGrid.EditItemIndex = -1;

BindGrid();

lblStatus.Text = "Lease Updated!";

txtKeyword.Enabled = true;
ddlColumn.Enabled = true;
btnSearch.Enabled = true;
btnRefresh.Enabled = true;
rfvKeyword.Enabled = true;
}

public void MyDataGrid_ItemCreated(object sender,
DataGridItemEventArgs e)
{

}

private void btnSearch_Click(object sender, System.EventArgs e)
{
lblStatus.Text = "";

// columns and keyword
string sColumn = ddlColumn.SelectedValue.ToString();
string sKeyword = txtKeyword.Text.Trim();

DataRow[] foundRows;

// search for a match
foundRows = dtLeases.Select(sColumn + " LIKE '%" + sKeyword + "%'");

// if we found a match
if(foundRows.Length != 0)
{
DataTable dtTemp = dtLeases.Clone();
foreach (DataRow row in foundRows)
{
dtTemp.ImportRow(row);
}

dtLeases = dtTemp.Copy();

MyDataGrid.DataSource = dtLeases;
MyDataGrid.DataBind();

lblStatus.Text = foundRows.Length + " records found for that
search.";
}
else
{
lblStatus.Text = "Nothing found for that search.";
}
}

private void btnRefresh_Click(object sender, System.EventArgs e)
{
txtKeyword.Text = "";
lblStatus.Text = "";

GetLeaseData();
BindGrid();
}

private void GetLeaseData()
{
SqlConnection oCon = new
SqlConnection(ConfigurationSettings.AppSettings["sqlConString"]);

SqlCommand oCom = new SqlCommand("spGetLeases", oCon);
oCom.CommandType = CommandType.StoredProcedure;

// set up the parameters
if(User.IsInRole("Manager"))
{
SqlParameter parmAccountID = oCom.Parameters.Add( new
SqlParameter("@AccountID",SqlDbType.Int));
parmAccountID.Direction = ParameterDirection.Input;
parmAccountID.Value = Int32.Parse(Session["AccountID"].ToString());
}

try
{
SqlDataAdapter da = new SqlDataAdapter(oCom);
da.Fill(dtLeases);
}
catch(Exception oError)
{
Response.Write("ERROR: " + oError.Message + " " +
oError.StackTrace + "");
}
}

public DataTable GetCounties()
{
SqlConnection oCon = new
SqlConnection(ConfigurationSettings.AppSettings["sqlConString"]);

SqlCommand oCom = new SqlCommand("spGetCounties", oCon);
oCom.CommandType = CommandType.StoredProcedure;

try
{
SqlDataAdapter da = new SqlDataAdapter(oCom);
da.Fill(dtCounties);
}
catch(Exception oError)
{
Response.Write("ERROR: " + oError.Message + " " +
oError.StackTrace + "");
}

return dtCounties;
}

public DataTable GetShippers()
{
SqlConnection oCon = new
SqlConnection(ConfigurationSettings.AppSettings["sqlConString"]);

SqlCommand oCom = new SqlCommand("spGetShippers", oCon);
oCom.CommandType = CommandType.StoredProcedure;

try
{
SqlDataAdapter da = new SqlDataAdapter(oCom);
da.Fill(dtShippers);
}
catch(Exception oError)
{
Response.Write("ERROR: " + oError.Message + " " +
oError.StackTrace + "");
}

return dtShippers;
}

void BindGrid()
{
MyDataGrid.DataSource = dtLeases;
MyDataGrid.DataBind();
}

public int GetCountyIndex(string sCountyID)
{
int iCountyID = 0;

// Loop through each row in the DataSet
for(int i = 0; i < dtCounties.Rows.Count - 1; i++)
{
if(Int32.Parse(sCountyID) ==
Int32.Parse(dtCounties.Rows["CountyID"].ToString()))
{
iCountyID = i;
}
}

return iCountyID;
}

public int GetShipperIndex(string sShipperID)
{
int iShipperID = 0;

// Loop through each row in the DataSet
for(int i = 0; i < dtShippers.Rows.Count - 1; i++)
{
if(Int32.Parse(sShipperID) ==
Int32.Parse(dtShippers.Rows["ShipperID"].ToString()))
{
iShipperID = i;
}
}

return iShipperID;
}

END CODE ====================================

jcochran · Aug 22, 2005

Yes, you are correct. I did create session storage and that seemed to
fix the problem. I appreciate your help.

W.G. Ryan MVP · Aug 23, 2005

Glad it worked. That same issue caused me a lot of grief at first...
migrating from Winforms to ASP.NET wasn't all that fun at first.

datatable.Select() and datagrid results

jcochran

jcochran

W.G. Ryan MVP

jcochran

W.G. Ryan MVP