Hi,
I am running VS 2013 against Access 2013 DB. But due to that I'm calling this event
...
BindData();
...
private void BindData()
{
int iteid;
bool b = int.TryParse(lb_iteid.Text, out iteid);
DataTable table = new DataTable();
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb"))
{
string sql = "Select * from ite_tab2 where ite_id=@ite_id";
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
//using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
using (OleDbDataAdapter ad = new OleDbDataAdapter(cmd))
{
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = iteid;
ad.Fill(table);
}
}
}
GridView1.DataSource = table;
GridView1.DataBind();
}
when saving the record, I've got this exception. why?
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).System.Data
Here is the Gridview
<asp:GridView ID="GridView1" runat="server" Width = "550px"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true"
ShowFooter = "true" OnPageIndexChanging="GridView1_PageIndexChanging"
OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing"
OnRowUpdating="GridView1_RowUpdating" PageSize = "10" OnRowDeleting="GridView1_RowDeleting" OnRowDataBound="GridView1_RowDataBound">
<Columns>
<asp:TemplateField HeaderText="Item">
<ItemTemplate>
<asp:DropDownList ID="ddl" OnSelectedIndexChanged="ddl_IndexChanged" AutoPostBack="true" runat="server">
</asp:DropDownList>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList ID="ddl2" OnSelectedIndexChanged="ddl_IndexChanged" AutoPostBack="true" runat="server">
</asp:DropDownList>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddl3" OnSelectedIndexChanged="ddl_IndexChanged" AutoPostBack="true" runat="server">
</asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField Visible="false">
<ItemTemplate>
<asp:TextBox id="tb_int_id" runat="server" Text='<%# Eval("int_id") %>' Visible="false" />
<asp:TextBox id="tb_int_desc" runat="server" Text='<%# Eval("int_desc") %>' Visible="false" />
<asp:TextBox id="tb_int_id2" runat="server" Text='<%# Eval("int_id") %>' Visible="false" />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="tb_int_id" runat="server" Text='<%# Eval("int_id") %>' Visible="false" />
<asp:TextBox id="tb_int_desc" runat="server" Text='<%# Eval("int_desc") %>' Visible="false" />
<asp:TextBox id="tb_int_id2" runat="server" Text='<%# Eval("int_id") %>' Visible="false" />
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox id="tb_int_id" runat="server" Text='<%# Eval("int_id") %>' Visible="false" />
<asp:TextBox id="tb_int_desc" runat="server" Text='<%# Eval("int_desc") %>' Visible="false" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField ItemStyle-Width = "150px" HeaderText = "Change Date">
<ItemTemplate>
<asp:Label ID="lblchangedate" runat="server"
Text='<%# Eval("change_date")%>'>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblchangedate" runat="server"
Text='<%# Eval("change_date")%>' >
</asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblchangedate" runat="server"
Text='<%# Eval("change_date")%>' >
</asp:Label>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete?">
<ItemTemplate>
<span onclick="return confirm('Are you sure to delete the record?')">
<asp:LinkButton ID="lnkdelete" runat="server" Text="Delete" ForeColor="Red" CommandName="Delete"/>
</span>
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="btnadd" runat="server" Text="Add" OnClick="AddNewRec" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="true" />
</Columns>
<AlternatingRowStyle BackColor="White"/>
<EditRowStyle BackColor="#efefef" />
<FooterStyle BackColor="#507CD1" Font-Bold="true" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="true" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="true" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
and the relevant events
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
lb_msg.Text = "";
TextBox tb = (TextBox)GridView1.Rows[e.NewEditIndex].FindControl("tb_int_id");
TextBox tb2 = (TextBox)GridView1.Rows[e.NewEditIndex].FindControl("tb_int_id2");
tb2.Text = tb.Text;
GridView1.EditIndex = e.NewEditIndex;
this.PopulateData();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
this.PopulateData();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
lb_msg.Text = "";
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString))
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb"))
{
GridViewRow row = GridView1.Rows[e.RowIndex] as GridViewRow;
int ite_id = Convert.ToInt16(lb_iteid.Text);
TextBox tb_int_id = row.FindControl("tb_int_id") as TextBox;
string sql = "Delete from ite_tab2" +
" where ite_id=@ite_id and int_id=@int_id";
//using (SqlCommand cmd = new SqlCommand(sql, conn))
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
cmd.Parameters.AddWithValue(
"@ite_id", ite_id);
cmd.Parameters.AddWithValue(
"@int_id", Convert.ToInt16(tb_int_id.Text));
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lb_msg2.Text =
"Record has been deleted successfully !";
lb_msg2.ForeColor = System.Drawing.
Color.Red;
this.PopulateData();
}
/*private DataSet GetDataSet()
{
/*string qry = @"select int_id,int_desc from tab2_master where datediff(yy,@birth,getdate())<=11 and int_id between 12 and 20 "+
"or datediff(yy,@birth2,getdate()) between 12 and 18 and int_id between 21 and 30 "+
"or @sex='M' and datediff(yy,@birth3,getdate())>18 and int_id between 21 and 30 ";*/
/*string qry = @"select int_id,int_desc from tab2_master order by 1";
SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
SqlDataAdapter da=new SqlDataAdapter(qry,sqlconn);
/*da.SelectCommand.Parameters.Add("@birth", OleDbType.DBDate).Value = Convert.ToDateTime(tb_birth.Text.Trim());
da.SelectCommand.Parameters.Add("@birth", OleDbType.DBDate).Value = Convert.ToDateTime(tb_birth.Text.Trim());
da.SelectCommand.Parameters.Add("@birth", OleDbType.DBDate).Value = Convert.ToDateTime(tb_birth.Text.Trim());
da.SelectCommand.Parameters.Add("@birth", OleDbType.DBDate).Value = Convert.ToDateTime(tb_birth.Text.Trim());
da.SelectCommand.Parameters.Add("@sex", OleDbType.VarChar).Value = rbl_sex.SelectedItem.Text;
da.SelectCommand.Parameters.Add("@sex2", OleDbType.VarChar).Value = rbl_sex.SelectedItem.Text;*/
/*DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}*/
private DataSet GetData(OleDbCommand cmd)
{
DataSet ds = new DataSet();
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
OleDbConnection con = null;
con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb");
cmd.Connection = con;
//SqlDataAdapter da = new SqlDataAdapter(cmd);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
protected void ddl_IndexChanged(object sender, EventArgs e)
{
DropDownList ddl = (sender as DropDownList);
GridViewRow row = ddl.NamingContainer as GridViewRow;
TextBox tb1= row.FindControl("tb_int_id") as TextBox;
TextBox tb2 = row.FindControl("tb_int_desc") as TextBox;
tb1.Text = ddl.SelectedItem.Value;
int pos=ddl.SelectedItem.Text.IndexOf(" ");
int len=ddl.SelectedItem.Text.Length;
tb2.Text = ddl.SelectedItem.Text.Substring(pos+1,len-pos-1);
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
/*DataTable tab1 = new DataTable();
DataColumn intid = new DataColumn("int_id");
DataColumn intdesc = new DataColumn("int_desc");
tab1.Columns.Add(intid);
tab1.Columns.Add(intdesc);
DataSet ds = new DataSet();*/
//ds = GetDataSet();
if (e.Row.RowType == DataControlRowType.DataRow)
{
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
DropDownList ddl = (DropDownList)e.Row.FindControl("ddl2");
if (ddl == null)
{
lb_msg2.Text = "Unable to bind Edit row in Grid.";
return;
}
else
this.BindDropDown(ddl);
Label lb_dt = (Label)e.Row.FindControl("lblchangedate");
//lb_dt.Text = DateTime.Now.ToString("G");
if (ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text) != null)
ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text).Selected = true;
}
else
{
DropDownList ddl = (DropDownList)e.Row.FindControl("ddl");
if (ddl == null)
{
lb_msg2.Text = "Unable to bind row in Grid." + Convert.ToString(e.Row.RowState);
return;
}
else
this.BindDropDown(ddl);
Label lb_dt = (Label)e.Row.FindControl("lblchangedate");
//lb_dt.Text = DateTime.Now.ToString("G");
if (ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text) != null)
ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text).Selected = true;
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
DropDownList ddl = (DropDownList)e.Row.FindControl("ddl3");
if (ddl == null)
{
lb_msg2.Text = "Unable to bind footer row in Grid.";
return;
}
else
this.BindDropDown(ddl);
Label lb_dt = (Label)e.Row.FindControl("lblchangedate");
lb_dt.Text = DateTime.Now.ToString("G");
if (ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text) != null)
ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text).Selected = true;
}
}
private void BindDropDown(DropDownList para_ddl)
{
//SqlCommand cmd = new SqlCommand();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select int_id,rtrim(ltrim(cast(int_id as varchar)))+' '+int_desc int_desc from tab2_master where int_id>=12 order by 1";
para_ddl.DataSource = GetData(cmd);
para_ddl.DataTextField = "int_desc";
para_ddl.DataValueField = "int_id";
para_ddl.DataBind();
para_ddl.Items.Insert(0, new ListItem("Please select"));
}
private void save_grid(object sender, EventArgs e)
{
foreach (GridViewRow row in GridView1.Rows)
{
int ite_id;
bool b = int.TryParse(lb_iteid.Text, out ite_id);
DropDownList ddl0 = (DropDownList)row.FindControl("ddl2");
int int_id = Convert.ToInt32(ddl0.DataValueField);
int pos, len;
Label lbldt = (Label)row.FindControl("lblchangedate");
DateTime dt = Convert.ToDateTime(lbldt.Text);
string str0 = Convert.ToString(ddl0.DataTextField);
string int_desc;
//SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
//SqlCommand cmd;
OleDbConnection conn = null;
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb");
OleDbCommand cmd;
if (int_id >= 12)
{
pos = str0.IndexOf(" ");
len = str0.Length;
int_desc = str0.Substring(pos + 1, len - 1 - pos);
conn.Open();
try
{
cmd = new OleDbCommand("insert into ite_tab2 (ite_id,int_id,int_desc,change_date) values (@ite_id,@int_id,@int_desc,@change_date)", conn);
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = ite_id;
cmd.Parameters.Add("@int_id", OleDbType.Integer).Value = int_id;
cmd.Parameters.Add("@int_desc", OleDbType.VarChar).Value = int_desc;
cmd.Parameters.Add("@change_date", OleDbType.DBDate).Value = dt;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
lb_msg2.Text = "Unable to save tab2 detail record." + ex.Message + ex.Source;
}
finally
{
conn.Close();
}
}
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
BindData();
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
private void BindData()
{
/*int ite_id = Convert.ToInt32(lb_iteid.Text);
string strQuery = "select ite_id,int_id,int_desc,change_date" +
//" from ite_tab2 where ite_id=@ite_id";
" from ite_tab2";
SqlCommand cmd = new SqlCommand(strQuery);
//cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = ite_id;
GridView1.DataSource = GetData(cmd);*/
int iteid;
bool b = int.TryParse(lb_iteid.Text, out iteid);
DataTable table = new DataTable();
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString))
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb"))
{
string sql = "Select * from ite_tab2 where ite_id=@ite_id";
//string sql = "Select * from ite_tab2";
//using (SqlCommand cmd = new SqlCommand(sql, conn))
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
//using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
using (OleDbDataAdapter ad = new OleDbDataAdapter(cmd))
{
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = iteid;
ad.Fill(table);
}
}
}
GridView1.DataSource = table;
GridView1.DataBind();
}
protected void AddNewRec(object sender, EventArgs e)
{
int ite_id = Convert.ToInt16(lb_iteid.Text);
DropDownList ddl=(DropDownList)GridView1.FooterRow.FindControl("ddl3");
int int_id=11;
if (ddl.Items.FindByValue((GridView1.FooterRow.FindControl("tb_int_id") as TextBox).Text) != null)
{
int_id = Convert.ToInt16((GridView1.FooterRow.FindControl("tb_int_id") as TextBox).Text);
}
string int_desc = ((TextBox)GridView1.FooterRow.FindControl("tb_int_desc")).Text;
DateTime change_date = Convert.ToDateTime(((Label)GridView1.FooterRow.FindControl("lblchangedate")).Text);
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
//SqlCommand cmd = new SqlCommand();
OleDbConnection conn = null;
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb");
OleDbCommand cmd=null;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into ite_tab2(ite_id, int_id, int_desc, change_date) " +
"values(@ite_id, @int_id, @int_desc, @change_date);" +
"select ite_id,int_id,int_desc,change_date from ite_tab2 where ite_id=@ite_id2";
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = ite_id;
cmd.Parameters.Add("@int_id", OleDbType.Integer).Value = int_id;
cmd.Parameters.Add("@int_desc", OleDbType.VarChar).Value = int_desc;
cmd.Parameters.Add("@change_date", OleDbType.DBDate).Value = change_date;
cmd.Parameters.Add("@ite_id2", OleDbType.Integer).Value = ite_id;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
lb_msg.Text = "";
GridViewRow row = GridView1.Rows[e.RowIndex] as GridViewRow;
int ite_id = Convert.ToInt16(lb_iteid.Text);
TextBox tb_int_id = row.FindControl("tb_int_id") as TextBox;
TextBox tb_int_id2 = row.FindControl("tb_int_id2") as TextBox;
TextBox tb_int_desc = row.FindControl("tb_int_desc") as TextBox;
Label lblchangedate = row.FindControl("lblchangedate") as Label;
lblchangedate.Text = DateTime.Now.ToString("G");
if (Convert.ToInt16(tb_int_id.Text) <= 11)
{
lb_msg2.Text = "You should have selected one relevant tab2 before you update the current record!";
return;
}
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString))
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb"))
{
string sql = "Update ite_tab2 set int_id = @int_id,int_desc=@int_desc, change_date= @change_date" + " where ite_id=@ite_id and int_id=@int_id2";
//using (SqlCommand cmd = new SqlCommand(sql, conn))
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
cmd.Parameters.AddWithValue(
"@int_id", Convert.ToInt16(tb_int_id.Text));
cmd.Parameters.AddWithValue(
"@int_desc", tb_int_desc.Text.Trim());
cmd.Parameters.AddWithValue(
"@change_date", Convert.ToDateTime(lblchangedate.Text));
cmd.Parameters.AddWithValue(
"@ite_id", ite_id);
cmd.Parameters.AddWithValue(
"@int_id2", Convert.ToInt16(tb_int_id2.Text));
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lb_msg2.Text = "Record updated successfully!";
GridView1.EditIndex = -1;
this.PopulateData();
}
protected void DeleteRec(object sender, EventArgs e)
{
int ite_id = Convert.ToInt16(lb_iteid.Text);
LinkButton lnkRemove = (LinkButton)sender;
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
//SqlCommand cmd = new SqlCommand();
OleDbConnection conn = null;
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb");
OleDbCommand cmd=null;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from ite_tab2 where " +
"ite_id=@ite_id and int_id=@int_id;" +
"select ite_id,int_id,int_desc,change_date from ite_tab2 where ite_id=@ite_id2";
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = ite_id;
cmd.Parameters.Add("@int_id", OleDbType.Integer).Value
= lnkRemove.CommandArgument;
cmd.Parameters.Add("@ite_id2", OleDbType.Integer).Value = ite_id;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
lb_msg.Text = "";
TextBox tb = (TextBox)GridView1.Rows[e.NewEditIndex].FindControl("tb_int_id");
TextBox tb2 = (TextBox)GridView1.Rows[e.NewEditIndex].FindControl("tb_int_id2");
tb2.Text = tb.Text;
GridView1.EditIndex = e.NewEditIndex;
this.PopulateData();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
this.PopulateData();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
lb_msg.Text = "";
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString))
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb"))
{
GridViewRow row = GridView1.Rows[e.RowIndex] as GridViewRow;
int ite_id = Convert.ToInt16(lb_iteid.Text);
TextBox tb_int_id = row.FindControl("tb_int_id") as TextBox;
string sql = "Delete from ite_tab2" +
" where ite_id=@ite_id and int_id=@int_id";
//using (SqlCommand cmd = new SqlCommand(sql, conn))
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
cmd.Parameters.AddWithValue(
"@ite_id", ite_id);
cmd.Parameters.AddWithValue(
"@int_id", Convert.ToInt16(tb_int_id.Text));
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lb_msg2.Text =
"Record has been deleted successfully !";
lb_msg2.ForeColor = System.Drawing.
Color.Red;
this.PopulateData();
}
/*private DataSet GetDataSet()
{
/*string qry = @"select int_id,int_desc from tab2_master where datediff(yy,@birth,getdate())<=11 and int_id between 12 and 20 "+
"or datediff(yy,@birth2,getdate()) between 12 and 18 and int_id between 21 and 30 "+
"or @sex='M' and datediff(yy,@birth3,getdate())>18 and int_id between 21 and 30 ";*/
/*string qry = @"select int_id,int_desc from tab2_master order by 1";
SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
SqlDataAdapter da=new SqlDataAdapter(qry,sqlconn);
/*da.SelectCommand.Parameters.Add("@birth", OleDbType.DBDate).Value = Convert.ToDateTime(tb_birth.Text.Trim());
da.SelectCommand.Parameters.Add("@birth", OleDbType.DBDate).Value = Convert.ToDateTime(tb_birth.Text.Trim());
da.SelectCommand.Parameters.Add("@birth", OleDbType.DBDate).Value = Convert.ToDateTime(tb_birth.Text.Trim());
da.SelectCommand.Parameters.Add("@birth", OleDbType.DBDate).Value = Convert.ToDateTime(tb_birth.Text.Trim());
da.SelectCommand.Parameters.Add("@sex", OleDbType.VarChar).Value = rbl_sex.SelectedItem.Text;
da.SelectCommand.Parameters.Add("@sex2", OleDbType.VarChar).Value = rbl_sex.SelectedItem.Text;*/
/*DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}*/
private DataSet GetData(OleDbCommand cmd)
{
DataSet ds = new DataSet();
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
OleDbConnection con = null;
con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb");
cmd.Connection = con;
//SqlDataAdapter da = new SqlDataAdapter(cmd);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
protected void ddl_IndexChanged(object sender, EventArgs e)
{
DropDownList ddl = (sender as DropDownList);
GridViewRow row = ddl.NamingContainer as GridViewRow;
TextBox tb1= row.FindControl("tb_int_id") as TextBox;
TextBox tb2 = row.FindControl("tb_int_desc") as TextBox;
tb1.Text = ddl.SelectedItem.Value;
int pos=ddl.SelectedItem.Text.IndexOf(" ");
int len=ddl.SelectedItem.Text.Length;
tb2.Text = ddl.SelectedItem.Text.Substring(pos+1,len-pos-1);
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
/*DataTable tab1 = new DataTable();
DataColumn intid = new DataColumn("int_id");
DataColumn intdesc = new DataColumn("int_desc");
tab1.Columns.Add(intid);
tab1.Columns.Add(intdesc);
DataSet ds = new DataSet();*/
//ds = GetDataSet();
if (e.Row.RowType == DataControlRowType.DataRow)
{
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
DropDownList ddl = (DropDownList)e.Row.FindControl("ddl2");
if (ddl == null)
{
lb_msg2.Text = "Unable to bind Edit row in Grid.";
return;
}
else
this.BindDropDown(ddl);
Label lb_dt = (Label)e.Row.FindControl("lblchangedate");
//lb_dt.Text = DateTime.Now.ToString("G");
if (ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text) != null)
ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text).Selected = true;
}
else
{
DropDownList ddl = (DropDownList)e.Row.FindControl("ddl");
if (ddl == null)
{
lb_msg2.Text = "Unable to bind row in Grid." + Convert.ToString(e.Row.RowState);
return;
}
else
this.BindDropDown(ddl);
Label lb_dt = (Label)e.Row.FindControl("lblchangedate");
//lb_dt.Text = DateTime.Now.ToString("G");
if (ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text) != null)
ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text).Selected = true;
}
}
if (e.Row.RowType == DataControlRowType.Footer)
{
DropDownList ddl = (DropDownList)e.Row.FindControl("ddl3");
if (ddl == null)
{
lb_msg2.Text = "Unable to bind footer row in Grid.";
return;
}
else
this.BindDropDown(ddl);
Label lb_dt = (Label)e.Row.FindControl("lblchangedate");
lb_dt.Text = DateTime.Now.ToString("G");
if (ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text) != null)
ddl.Items.FindByValue((e.Row.FindControl("tb_int_id") as TextBox).Text).Selected = true;
}
}
private void BindDropDown(DropDownList para_ddl)
{
//SqlCommand cmd = new SqlCommand();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select int_id,rtrim(ltrim(cast(int_id as varchar)))+' '+int_desc int_desc from tab2_master where int_id>=12 order by 1";
para_ddl.DataSource = GetData(cmd);
para_ddl.DataTextField = "int_desc";
para_ddl.DataValueField = "int_id";
para_ddl.DataBind();
para_ddl.Items.Insert(0, new ListItem("Please select"));
}
private void save_grid(object sender, EventArgs e)
{
foreach (GridViewRow row in GridView1.Rows)
{
int ite_id;
bool b = int.TryParse(lb_iteid.Text, out ite_id);
DropDownList ddl0 = (DropDownList)row.FindControl("ddl2");
int int_id = Convert.ToInt32(ddl0.DataValueField);
int pos, len;
Label lbldt = (Label)row.FindControl("lblchangedate");
DateTime dt = Convert.ToDateTime(lbldt.Text);
string str0 = Convert.ToString(ddl0.DataTextField);
string int_desc;
//SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
//SqlCommand cmd;
OleDbConnection conn = null;
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb");
OleDbCommand cmd;
if (int_id >= 12)
{
pos = str0.IndexOf(" ");
len = str0.Length;
int_desc = str0.Substring(pos + 1, len - 1 - pos);
conn.Open();
try
{
cmd = new OleDbCommand("insert into ite_tab2 (ite_id,int_id,int_desc,change_date) values (@ite_id,@int_id,@int_desc,@change_date)", conn);
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = ite_id;
cmd.Parameters.Add("@int_id", OleDbType.Integer).Value = int_id;
cmd.Parameters.Add("@int_desc", OleDbType.VarChar).Value = int_desc;
cmd.Parameters.Add("@change_date", OleDbType.DBDate).Value = dt;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
lb_msg2.Text = "Unable to save tab2 detail record." + ex.Message + ex.Source;
}
finally
{
conn.Close();
}
}
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
BindData();
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
private void BindData()
{
/*int ite_id = Convert.ToInt32(lb_iteid.Text);
string strQuery = "select ite_id,int_id,int_desc,change_date" +
//" from ite_tab2 where ite_id=@ite_id";
" from ite_tab2";
SqlCommand cmd = new SqlCommand(strQuery);
//cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = ite_id;
GridView1.DataSource = GetData(cmd);*/
int iteid;
bool b = int.TryParse(lb_iteid.Text, out iteid);
DataTable table = new DataTable();
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString))
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb"))
{
string sql = "Select * from ite_tab2 where ite_id=@ite_id";
//string sql = "Select * from ite_tab2";
//using (SqlCommand cmd = new SqlCommand(sql, conn))
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
//using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
using (OleDbDataAdapter ad = new OleDbDataAdapter(cmd))
{
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = iteid;
ad.Fill(table);
}
}
}
GridView1.DataSource = table;
GridView1.DataBind();
}
protected void AddNewRec(object sender, EventArgs e)
{
int ite_id = Convert.ToInt16(lb_iteid.Text);
DropDownList ddl=(DropDownList)GridView1.FooterRow.FindControl("ddl3");
int int_id=11;
if (ddl.Items.FindByValue((GridView1.FooterRow.FindControl("tb_int_id") as TextBox).Text) != null)
{
int_id = Convert.ToInt16((GridView1.FooterRow.FindControl("tb_int_id") as TextBox).Text);
}
string int_desc = ((TextBox)GridView1.FooterRow.FindControl("tb_int_desc")).Text;
DateTime change_date = Convert.ToDateTime(((Label)GridView1.FooterRow.FindControl("lblchangedate")).Text);
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
//SqlCommand cmd = new SqlCommand();
OleDbConnection conn = null;
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb");
OleDbCommand cmd=null;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into ite_tab2(ite_id, int_id, int_desc, change_date) " +
"values(@ite_id, @int_id, @int_desc, @change_date);" +
"select ite_id,int_id,int_desc,change_date from ite_tab2 where ite_id=@ite_id2";
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = ite_id;
cmd.Parameters.Add("@int_id", OleDbType.Integer).Value = int_id;
cmd.Parameters.Add("@int_desc", OleDbType.VarChar).Value = int_desc;
cmd.Parameters.Add("@change_date", OleDbType.DBDate).Value = change_date;
cmd.Parameters.Add("@ite_id2", OleDbType.Integer).Value = ite_id;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
lb_msg.Text = "";
GridViewRow row = GridView1.Rows[e.RowIndex] as GridViewRow;
int ite_id = Convert.ToInt16(lb_iteid.Text);
TextBox tb_int_id = row.FindControl("tb_int_id") as TextBox;
TextBox tb_int_id2 = row.FindControl("tb_int_id2") as TextBox;
TextBox tb_int_desc = row.FindControl("tb_int_desc") as TextBox;
Label lblchangedate = row.FindControl("lblchangedate") as Label;
lblchangedate.Text = DateTime.Now.ToString("G");
if (Convert.ToInt16(tb_int_id.Text) <= 11)
{
lb_msg2.Text = "You should have selected one relevant tab2 before you update the current record!";
return;
}
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString))
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb"))
{
string sql = "Update ite_tab2 set int_id = @int_id,int_desc=@int_desc, change_date= @change_date" + " where ite_id=@ite_id and int_id=@int_id2";
//using (SqlCommand cmd = new SqlCommand(sql, conn))
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
cmd.Parameters.AddWithValue(
"@int_id", Convert.ToInt16(tb_int_id.Text));
cmd.Parameters.AddWithValue(
"@int_desc", tb_int_desc.Text.Trim());
cmd.Parameters.AddWithValue(
"@change_date", Convert.ToDateTime(lblchangedate.Text));
cmd.Parameters.AddWithValue(
"@ite_id", ite_id);
cmd.Parameters.AddWithValue(
"@int_id2", Convert.ToInt16(tb_int_id2.Text));
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
lb_msg2.Text = "Record updated successfully!";
GridView1.EditIndex = -1;
this.PopulateData();
}
protected void DeleteRec(object sender, EventArgs e)
{
int ite_id = Convert.ToInt16(lb_iteid.Text);
LinkButton lnkRemove = (LinkButton)sender;
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn2"].ConnectionString);
//SqlCommand cmd = new SqlCommand();
OleDbConnection conn = null;
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=?????;Data Source=C:\inetpub\VS2012\DB1.accdb");
OleDbCommand cmd=null;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from ite_tab2 where " +
"ite_id=@ite_id and int_id=@int_id;" +
"select ite_id,int_id,int_desc,change_date from ite_tab2 where ite_id=@ite_id2";
cmd.Parameters.Add("@ite_id", OleDbType.Integer).Value = ite_id;
cmd.Parameters.Add("@int_id", OleDbType.Integer).Value
= lnkRemove.CommandArgument;
cmd.Parameters.Add("@ite_id2", OleDbType.Integer).Value = ite_id;
GridView1.DataSource = GetData(cmd);
GridView1.DataBind();
}
