Click here to Skip to main content
15,881,803 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a table.I want to update the table.Actually I have a gridview which retrieve values from SQL table.When page load then gridview load the values .I want that when i insert new values in gridview then in SQL Table existing values update and also new values insert in the same table using SINGLE query.How can i do this?Just tell me SQL query which works in C#,ASP.NET.I'm asking for UPDATE existing record and insert new record in the same table at same time using one query. Thanks

What I have tried:

C#
public void insert(object sender, EventArgs e)
{
 string user = Session["name"].ToString();
 SqlConnection cnn = new SqlConnection("Data Source=HAMEED_KHAN\\SQLEXPRESS;Initial Catalog=db_compiler;Integrated Security=True");
SqlCommand cmd3 = new SqlCommand("SELECT User_ID from tbl_user WHERE User_Name='" + user + "'", cnn);
cnn.Open();
string id = cmd3.ExecuteScalar().ToString();
int ID = Int32.Parse(id);
Session["ID"] = ID;
string d = Session["value"].ToString();
SqlCommand cmd2 = new SqlCommand("SELECT Database_id FROM Create_db WHERE Database_Name='" + d + "'", cnn);
Response.Write("<script>Var Z=Prompt('Enter Table Name');</script>");
string dbid = cmd2.ExecuteScalar().ToString();
cnn.Close();
int D_ID = Int32.Parse(dbid);
string str = "";
string type = "";
for (int i = 0; i < GridView2.Rows.Count; i++)
{
 str = GridView2.Rows[i].Cells[1].Text.ToString();
 type = GridView2.Rows[i].Cells[2].Text.ToString();
 string Name = GridView2.Rows[i].Cells[1].Text.ToString();
 string Type = GridView2.Rows[i].Cells[2].Text.ToString();
 string size = GridView2.Rows[i].Cells[3].Text.ToString();
 CheckBox allow = GridView2.Rows[i].Cells[4].Controls[0] as CheckBox;
 CheckBox primary = GridView2.Rows[i].Cells[5].Controls[0] as CheckBox;
 string UserID = Session["ID"].ToString();
 int UID = Int32.Parse(UserID);
 string date = DateTime.Now.ToString();
 string A = (allow.Checked == true ? "NULL" : "NOT NULL");
 string P = (primary.Checked == true ? "PRIMARY KEY" : "");
 string Table = Session["TBL_NAME"].ToString();
 string queryy ="USE db_compiler UPDATE tbl_field SET Column_Name='" + Name + "', Data_Type='" + Type + "',Size='" + size + "',Database_id='" + D_ID + "',Allow_Null_='" + (allow.Checked == true ? "true" : "false") + "',Primary_Key_='" + (primary.Checked == true ? "true" : "false") + "',User_id='" + UID + "',Date='" + date + "' WHERE Table_Name='" + Table + "' IF @@ROWCOUNT=0 insert into tbl_field (Table_Name,Column_Name,Data_Type,Size,Database_id,Allow_Null_,Primary_Key_,User_id,Date) VALUES('" + Table + "','" + Name + "','" + Type + "','" + size + "','" + D_ID + "','" + (allow.Checked == true ? "true" : "false") + "','" + (primary.Checked == true ? "true" : "false") + "','" + UID + "','" + date + "')";
  SqlCommand cmd = new SqlCommand(queryy, cnn);
  SqlDataAdapter ad = new SqlDataAdapter(cmd);
 cnn.Open();
  cmd.ExecuteNonQuery();
  cnn.Close();
}
}
gridview-image Table name is 'e

gridview_imageTable name is 'employee' first i have 3 rows in gridview 'Name','id','address' when i insert new row 'ph' and click on 'update tabe' then i update all rows with 'ph'db_image
Posted
Updated 4-Jun-16 9:11am
v2

1 solution

Maybe the MERGE command which can be used as "UPSERT" command is what you need.
See this article: Using the MERGE Statement to Perform an UPSERT &mdash; DatabaseJournal.com[^]
 
Share this answer
 
Comments
Member 12565497 8-Jun-16 6:24am    
can u provide me just query according to my question please
RickZeeland 8-Jun-16 14:26pm    
Sorry I never used the MERGE command myself, so I can not give you the query. Now I'm looking closer at the MERGE command I think it is not suited for your purposes as you only have one table and this command is meant to work with two tables.
Member 12565497 9-Jun-16 1:54am    
Then can you provide me solution of my problem?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900