how to update and delete data from two table using one gridview and one udate button and one delete button in asp.net.
my database is in sql server.
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
namespace csvfileupload2
{
public partial class demo : System.Web.UI.Page
{
private SqlConnection conn = new SqlConnection("Data Source=ANIRUDDHA-PC;Initial Catalog=sark;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
gvbind();
}
}
protected void gvbind()
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select p.[id],p.[phone_number],p.[title],p.[first_name],p.[middle_initial],p.[last_name],p.[address],p.[city],p.[state],p.[province],p.[postal_code],p.[country_code],c.[profilesrr_id],c.[call_in_date],c.[call_duration],c.[call_status] From profilesrr p inner join call_detailssr c on c.[profilesrr_id] = p.[id]", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
conn.Close();
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
GridView1.DataSource = ds;
GridView1.DataBind();
int columncount = GridView1.Rows[0].Cells.Count;
GridView1.Rows[0].Cells.Clear();
GridView1.Rows[0].Cells.Add(new TableCell());
GridView1.Rows[0].Cells[0].ColumnSpan = columncount;
GridView1.Rows[0].Cells[0].Text = "No Records Found";
}
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
Label lbldeleteid = (Label)row.FindControl("lblID");
conn.Open();
SqlCommand cmd = new SqlCommand("delete FROM profilesrr,call_detailssr where phone_number='" + Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString()) + "'", conn);
cmd.ExecuteNonQuery();
conn.Close();
gvbind();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
gvbind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int userphone_number = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
Label lblID = (Label)row.FindControl("lblID");
//TextBox txtname=(TextBox)gr.cell[].control[];
TextBox textpnumber = (TextBox)row.Cells[0].Controls[0];
TextBox texttitle = (TextBox)row.Cells[1].Controls[0];
TextBox textfname = (TextBox)row.Cells[2].Controls[0];
TextBox textmname = (TextBox)row.Cells[3].Controls[0];
TextBox textlname = (TextBox)row.Cells[4].Controls[0];
TextBox textadd = (TextBox)row.Cells[5].Controls[0];
TextBox textcity = (TextBox)row.Cells[6].Controls[0];
TextBox textstate = (TextBox)row.Cells[7].Controls[0];
TextBox textprovince = (TextBox)row.Cells[8].Controls[0];
TextBox textpcode = (TextBox)row.Cells[9].Controls[0];
TextBox textccode = (TextBox)row.Cells[10].Controls[0];
TextBox textcdate = (TextBox)row.Cells[11].Controls[0];
TextBox textcduration = (TextBox)row.Cells[12].Controls[0];
TextBox textcstatus = (TextBox)row.Cells[13].Controls[0];
//TextBox textadd = (TextBox)row.FindControl("txtadd");
//TextBox textc = (TextBox)row.FindControl("txtc");
GridView1.EditIndex = -1;
conn.Open();
//SqlCommand cmd = new SqlCommand("SELECT * FROM detail", conn);
SqlCommand cmd = new SqlCommand("update profilesr as p inner join call_detailssr as c on c.[profilesrr_id] = p.[id] set phone_number='" + textpnumber.Text + "',title='" + texttitle.Text + "',first_name='" + textfname.Text + "',middle_initial='" + textmname.Text + "',last_name='" + textlname.Text + "',address='" + textadd.Text + "',city='" + textcity.Text + "',state='" + textstate.Text + "',province='" + textprovince.Text + "',postal_code='" + textpcode.Text + "',country_code='" + textccode.Text + "',call_in_date='" + textcdate.Text + "',call_duration='" + textcduration.Text + "',call_status='" + textcstatus.Text + "' where phone_number='" + userphone_number + "'", conn);
cmd.ExecuteNonQuery();
// SqlCommand cmd = new SqlCommand("update call_detailssr set phone_number='" + textpnumber.Text + "',call_in_date='" + textcdate.Text + "',call_duration='" + textcduration.Text + "',call_status='" + textcstatus.Text + "'", conn);
//cmd.ExecuteNonQuery();
conn.Close();
gvbind();
//GridView1.DataBind();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
gvbind();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
gvbind();
}
public string userphone_number { get; set; }
}
}