solved this error Incorrect syntax near the keyword 'INNER'.

Question

i want to update two table using one sql server statement and one update button . but when i am run my code a error will be occur and that is error:Incorrect syntax near the keyword 'INNER'. my code is bellow . SqlCommand cmd = new SqlCommand(UPDATE profilesrr INNER JOIN call_detailssr ON profilesrr.id = call_detailessr.profilesrr_id SET profilesrr.phone_number = '+ textpnumber.Text +' profilesrr.title = ' + texttitle.Text +'profilesrr.first_name = '+ textfname.Text +'profilesrr.middle_initial = '+ textmname.Text +'profilesrr.last_name = '+ textlname.Text +'profilesrr.address = '+ textadd.Text +'profilesrr.city = '+ textcity.Text +'profilesrr.state = '+ textstate.Text +'profilesrr.province = '+ textprovince.Text +'profilesrr.postal_code = '+ textpcode.Text +'profilesrr.country_code = '+ textccode.Text +'call_detailssr.call_in_date = '+ textcdate.Text +'call_detailssr.call_duration = '+ textcduration.Text +'call_detailssr.call_status = '+ textcstatus.Text +'WHERE profilesrr.id = 1 conn); cmd.ExecuteNonQuery(); please help me thanks in advance

Manas Mohapatra · Answer

string sqlQuery = @UPDATE t1 INNER JOIN t2 SET t1.phone_number = '+ textpnumber.Text +' t1.title = ' + texttitle.Text +' t1.first_name = '+ textfname.Text +'t1.middle_initial = '+ textmname.Text +' t1.last_name = '+ textlname.Text +'t1.address = '+ textadd.Text +' t1.city = '+ textcity.Text +'t1.state = '+ textstate.Text +' t1.province = '+ textprovince.Text +'t1.postal_code = '+ textpcode.Text +' t1.country_code = '+ textccode.Text +'t2.call_in_date = '+ textcdate.Text +' t2.call_duration = '+ textcduration.Text +'t2.call_status = '+ textcstatus.Text +' FROM profilesrr t1 INNER JOIN call_detailssr t2 ON t1.id = t2.profilesrr_id WHERE t1.id = 1; SqlCommand cmd = new SqlCommand (sqlQuery conn); cmd.ExecuteNonQuery();

Santhosh Subramaniam · Answer

Hi  Try to use the same alias name which table u want to update.(e.i update table and column used in update).

saroj bhattacharya · Answer

@upendra  but in that case i am not able to use stored procedure can it is not possible using table join???????????????????????????????????????????????????????????????????????????????

Upendra Pratap Shahi · Answer

You have done this with query also.

saroj bhattacharya · Answer

thanks for your advice . i am trying it ltr but that case i am not able to use stored procedure so if you have any better idea then give me reply. sorry

saroj bhattacharya · Answer

it not working error: The multi-part identifier profilesrr.id could not be bound. The multi-part identifier call_detailessr.profilesrr_id could not be bound. The multi-part identifier profilesrr.id could not be bound. will be occur

Santhosh Subramaniam · Answer

Hi  Pls try this. UPDATE p SET profilesrr.phone_number = '+ textpnumber.Text +' profilesrr.title = ' + texttitle.Text +' profilesrr.first_name = '+ textfname.Text +' profilesrr.middle_initial = '+ textmname.Text +'profilesrr.last_name = '+ textlname.Text +' profilesrr.address = '+ textadd.Text +'profilesrr.city = '+ textcity.Text +' profilesrr.state = '+ textstate.Text +'profilesrr.province = '+ textprovince.Text +' profilesrr.postal_code = '+ textpcode.Text +'profilesrr.country_code = '+ textccode.Text +' call_detailssr.call_in_date = '+ textcdate.Text +'call_detailssr.call_duration = '+ textcduration.Text +' call_detailssr.call_status = '+ textcstatus.Text +' From profilesrr p INNER JOIN call_detailssr ON profilesrr.id = call_detailessr.profilesrr_id WHERE profilesrr.id = 1

Rajeesh Menoth · Answer

Hi Ref this : http://stackoverflow.com/questions/24636350/t-sql-the-multi-part-identifier-could-not-be-bound

saroj bhattacharya · Answer

thanks for giving me a reply but your code are not working when i am run your code error: The multi-part identifier c.call_in_date could not be bound. will be occur. please help me

Rajeesh Menoth · Answer

Try this SqlCommand cmd = new SqlCommand(@ UPDATE p SET p.phone_number = '+ textpnumber.Text +' p.title = ' + texttitle.Text +' p.first_name = '+ textfname.Text +' p.middle_initial = '+ textmname.Text +' p.last_name = '+ textlname.Text +' p.address = '+ textadd.Text +' p.city = '+ textcity.Text +' p.state = '+ textstate.Text +' p.province = '+ textprovince.Text +' p.postal_code = '+ textpcode.Text +' p.country_code = '+ textccode.Text +' c.call_in_date = '+ textcdate.Text +' c.call_duration = '+ textcduration.Text +' c.call_status = '+ textcstatus.Text +' FROM profilesrr p INNER JOIN call_detailssr c ON p.id = c.profilesrr_id WHERE p.id = 2 conn);

saroj bhattacharya · Answer

yes @ Rajeesh Menoth both datatype are same

Rajeesh Menoth · Answer

Hi You can check your version or check both part are same datatype  profilesrr.id = call_detailessr.profilesrr_id .

saroj bhattacharya · Answer

please help me

saroj bhattacharya · Answer

dear upendra sir i am sending you my full code ...my issue is i am want to update and delete from two table using one sql server statement and one update and one delete button. in asp.net my demo.aspx.cs page code is bellow 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 profilesrrcall_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 profilesrr SET profilesrr.phone_number = '+ textpnumber.Text +' profilesrr.title = ' + texttitle.Text +'profilesrr.first_name = '+ textfname.Text +' profilesrr.middle_initial = '+ textmname.Text +'profilesrr.last_name = '+ textlname.Text +'profilesrr.address = '+ textadd.Text +' profilesrr.city = '+ textcity.Text +'profilesrr.state = '+ textstate.Text +'profilesrr.province = '+ textprovince.Text +' profilesrr.postal_code = '+ textpcode.Text +'profilesrr.country_code = '+ textccode.Text +'call_detailssr.call_in_date = '+ textcdate.Text +' call_detailssr.call_duration = '+ textcduration.Text +'call_detailssr.call_status = '+ textcstatus.Text +' FROM profilesrr INNER JOIN call_detailssr ON profilesrr.id = call_detailessr.profilesrr_id WHERE profilesrr.id = 2 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; } } } and my demo.aspx page code is bellow DataKeyNames=phone_number OnPageIndexChanging=GridView1_PageIndexChanging OnRowCancelingEdit=GridView1_RowCancelingEdit OnRowDeleting=GridView1_RowDeleting OnRowEditing=GridView1_RowEditing OnRowUpdating=GridView1_RowUpdating onselectedindexchanged=GridView1_SelectedIndexChanged> please solved my issue using any query or any method but using one update button and one delete button.... i am fresher in this field please help me

saroj bhattacharya · Answer

dear upendra sir when i am use ur code this error will be occur .the error is bellow error:The multi-part identifier call_detailessr.profilesrr_id could not be bound.

Upendra Pratap Shahi · Answer

update from two query with using transaction.

saroj bhattacharya · Answer

same error will be occur using ur code

Upendra Pratap Shahi · Answer

SqlCommand cmd = new SqlCommand ( @UPDATE profilesrr SET profilesrr.phone_number = '+ textpnumber.Text +' profilesrr.title = ' + texttitle.Text +'profilesrr.first_name = '+ textfname.Text +' profilesrr.middle_initial = '+ textmname.Text +'profilesrr.last_name = '+ textlname.Text +'profilesrr.address = '+ textadd.Text +' profilesrr.city = '+ textcity.Text +'profilesrr.state = '+ textstate.Text +'profilesrr.province = '+ textprovince.Text +' profilesrr.postal_code = '+ textpcode.Text +'profilesrr.country_code = '+ textccode.Text +'call_detailssr.call_in_date = '+ textcdate.Text +' call_detailssr.call_duration = '+ textcduration.Text +'call_detailssr.call_status = '+ textcstatus.Text +' FROM profilesrr INNER JOIN call_detailssr ON profilesrr.id = call_detailessr.profilesrr_id WHERE profilesrr.id = 1  conn); cmd.ExecuteNonQuery();

Pankaj Kumar Choudhary · Answer

hello @Saroj syntax of your query is wrong actual syntax is UPDATE table1 SET table1.OrderAmount = table2.Amount FROM dbo.Customers CUST INNER JOIN dbo.table2ORDR ON table1.CustomerID = table2.CustomerID so please modify your query as GO UPDATE profilesrr SET profilesrr.phone_number = '+ textpnumber.Text +' profilesrr.title = ' + texttitle.Text +'profilesrr.first_name = '+ textfname.Text +' profilesrr.middle_initial = '+ textmname.Text +'profilesrr.last_name = '+ textlname.Text +'profilesrr.address = '+ textadd.Text +' profilesrr.city = '+ textcity.Text +'profilesrr.state = '+ textstate.Text +'profilesrr.province = '+ textprovince.Text +' profilesrr.postal_code = '+ textpcode.Text +'profilesrr.country_code = '+ textccode.Text +'call_detailssr.call_in_date = '+ textcdate.Text +' call_detailssr.call_duration = '+ textcduration.Text +'call_detailssr.call_status = '+ textcstatus.Text +' FROM profilesrr INNER JOIN call_detailssr ON profilesrr.id = call_detailessr.profilesrr_id WHERE profilesrr.id = 1

Niranjan Poddar · Answer

Please paste this code SqlCommand cmd = new SqlCommand(UPDATE profilesrr SET profilesrr.phone_number = '+ textpnumber.Text +' profilesrr.title = ' + texttitle.Text +'profilesrr.first_name ='+ textfname.Text +'profilesrr.middle_initial = '+ textmname.Text +'profilesrr.last_name = '+ textlname.Text +'profilesrr.address = '+ textadd.Text +'profilesrr.city = '+ textcity.Text +'profilesrr.state = '+ textstate.Text +'profilesrr.province = '+ textprovince.Text +'profilesrr.postal_code = '+ textpcode.Text +'profilesrr.country_code = '+ textccode.Text +'call_detailssr.call_in_date = '+ textcdate.Text +'call_detailssr.call_duration = '+ textcduration.Text +'call_detailssr.call_status = '+ textcstatus.Text +' INNER JOIN call_detailssr ON profilesrr.id = call_detailessr.profilesrr_id WHERE profilesrr.id = 1 conn); cmd.ExecuteNonQuery(); I think it will work...............

Jaipal Reddy · Answer

Use stored procedure and transaction in database it makes much easier the coding part and as well debugging.