HOW TO INSERT DELETE and UPDATE a record

Question

hi Can any one help me for my query. In sample table we are having just 2 or 3 columns so we can added text box for that columns and whatever changes we can do through that text box. but in real time project we are having a N number of column so we can not keep text box for that many columns in visual studio 2010. So Please can you help me HOW TO INSERT DELETE and UPDATE a record directly in grid view and that will appear on database simultaneously. I am waiting for your reply

Amit Gupta · Answer

Hey Using SqlDataAdapter class allow you to accomplish your task much easier Please read the steps carefully 1. Make a global variable of DataTable dt; SqlDataAdapter adapter; 2. At FormLoad initialize both the global variables and fill your dt with adapter by specifying your query like dt = new DataTable(); adapter = new SqlDataAdapter(Select * from tablename connection); adapter.Fill(dt); // Set the datasource to gridview 3. Create a Save method void Save() { var builder = new SqlBuilder(adapter); adapter.Update(dt); } 4. Allow user to directly edit and update the row generate a RowEditEnding event and write the below code Save(); 5. Generate a keyup event of gridview and write the code Keyup_event( xx sender xx e) { if (e.Key == Keys.Delete) Save(); } Do rectify syntax error as I am not with my system currently. Moreover please prompt user before deleting any data That all you have to do Good Luck

sujitha m · Answer

thanks manikandan murugesan. i need windows application c#. you send me link for ASP.NET

sujitha m · Answer

thanks sir but this is sql query i need c# coding

Jothish Kumar · Answer

Hi Inserting Records into a Table There are essentially two methods for adding records to a table. The first is to add one record at a time; the second is to add many records at a time. In both cases you use the SQL statement INSERT INTO to accomplish the task. INSERT INTO statements are commonly referred to as append queries. To add one record to a table you must use the field list to define which fields to put the data in and then you must supply the data itself in a value list. To define the value list use the VALUES clause. For example the following statement will insert the values 1 Kelly and Jill into the CustomerID Last Name and First Name fields respectively. INSERT INTO tblCustomers (CustomerID [Last Name] [First Name]) VALUES (1 'Kelly' 'Jill') You can omit the field list but only if you supply all the values that record can contain. INSERT INTO tblCustomers VALUES (1 Kelly 'Jill' '555-1040' 'someone@microsoft.com') To add many records to a table at one time use the INSERT INTO statement along with a SELECT statement. When you are inserting records from another table each value being inserted must be compatible with the type of field that will be receiving the data. The following INSERT INTO statement inserts all the values in the CustomerID Last Name and First Name fields from the tblOldCustomers table into the corresponding fields in the tblCustomers table. INSERT INTO tblCustomers (CustomerID [Last Name] [First Name]) SELECT CustomerID [Last Name] [First Name] FROM tblOldCustomers If the tables are defined exactly alike you can leave out the field lists. INSERT INTO tblCustomers SELECT * FROM tblOldCustomers Updating Records in a Table To modify the data that is currently in a table you use the UPDATE statement which is commonly referred to as an update query. The UPDATE statement can modify one or more records and generally takes this form: UPDATE table name SET field name = some value To update all the records in a table specify the table name and then use the SET clause to specify the field or fields to be changed. UPDATE tblCustomers SET Phone = 'None' In most cases you will want to qualify the UPDATE statement with a WHERE clause to limit the number of records changed. UPDATE tblCustomers SET Email = 'None' WHERE [Last Name] = 'Smith' Deleting Records from a Table To delete the data that is currently in a table you use the DELETE statement which is commonly referred to as a delete query. This is also known as truncating a table. The DELETE statement can remove one or more records from a table and generally takes this form: DELETE FROM table list The DELETE statement does not remove the table structure—only the data that is currently being held by the table structure. To remove all the records from a table use the DELETE statement and specify which table or tables from which you want to delete all the records. DELETE FROM tblInvoices In most cases you will want to qualify the DELETE statement with a WHERE clause to limit the number of records to be removed. DELETE FROM tblInvoices WHERE InvoiceID = 3 If you want to remove data only from certain fields in a table use the UPDATE statement and set those fields equal to NULL  but only if they are nullable fields. UPDATE tblCustomers SET Email = Null

Krishna Rajput Singh · Answer

Hi sujitha m Thank you writing your concern i have solution for your question using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; namespace DynamicallyGridviewApps { public partial class GridView2 : Form { SqlConnection sqlCon = new SqlConnection( Data Source=KRISHNA\SQLEXPRESS;Initial Catalog=EmployeesDB;Integrated Security=true; ); SqlCommandBuilder sqlCommand = null ; SqlDataAdapter sqlAdapter = null ; DataSet dataset = null ; public GridView2() { InitializeComponent(); } private void LoadData() { try { sqlAdapter = new SqlDataAdapter( SELECT * 'Delete' AS [Delete] FROM Employees  sqlCon); sqlCommand = new SqlCommandBuilder(sqlAdapter); sqlAdapter.InsertCommand = sqlCommand.GetInsertCommand(); sqlAdapter.UpdateCommand = sqlCommand.GetUpdateCommand(); sqlAdapter.DeleteCommand = sqlCommand.GetDeleteCommand(); dataset = new DataSet(); sqlAdapter.Fill(dataset Employees ); dgvEmployee.DataSource = null ; dgvEmployee.DataSource = dataset.Tables[ Employees ]; for ( int i = 0; i { DataGridViewLinkCell linkCell = new DataGridViewLinkCell(); dgvEmployee[6 i] = linkCell; } } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void GridView2_Load( object sender EventArgs e) { try { sqlCon.Open(); LoadData(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void dgvEmployee_CellContentClick( object sender DataGridViewCellEventArgs e) { try { if (e.ColumnIndex == 6) { string Task = dgvEmployee.Rows[e.RowIndex].Cells[6].Value.ToString(); if (Task == Delete ) { if (MessageBox.Show( Are you sure to delete?  Deleting...  MessageBoxButtons.YesNo MessageBoxIcon.Question) == DialogResult.Yes) { int rowIndex = e.RowIndex; dgvEmployee.Rows.RemoveAt(rowIndex); dataset.Tables[ Employees ].Rows[rowIndex].Delete(); sqlAdapter.Update(dataset Employees ); } } else if (Task == Insert ) { int row = dgvEmployee.Rows.Count - 2; DataRow dr = dataset.Tables[ Employees ].NewRow(); dr[ LastName ] = dgvEmployee.Rows[row].Cells[ LastName ].Value; dr[ FirstName ] = dgvEmployee.Rows[row].Cells[ FirstName ].Value; dr[ Title ] = dgvEmployee.Rows[row].Cells[ Title ].Value; dr[ HireDate ] = dgvEmployee.Rows[row].Cells[ HireDate ].Value; dr[ PostalCode ] = dgvEmployee.Rows[row].Cells[ PostalCode ].Value; dataset.Tables[ Employees ].Rows.Add(dr); dataset.Tables[ Employees ].Rows.RemoveAt(dataset.Tables[ Employees ].Rows.Count - 1); dgvEmployee.Rows.RemoveAt(dgvEmployee.Rows.Count - 2); dgvEmployee.Rows[e.RowIndex].Cells[6].Value = Delete ; sqlAdapter.Update(dataset Employees ); } else if (Task == Update ) { int r = e.RowIndex; dataset.Tables[ Employees ].Rows[r][ LastName ] = dgvEmployee.Rows[r].Cells[ LastName ].Value; dataset.Tables[ Employees ].Rows[r][ FirstName ] = dgvEmployee.Rows[r].Cells[ FirstName ].Value; dataset.Tables[ Employees ].Rows[r][ Title ] = dgvEmployee.Rows[r].Cells[ Title ].Value; dataset.Tables[ Employees ].Rows[r][ HireDate ] = dgvEmployee.Rows[r].Cells[ HireDate ].Value; dataset.Tables[ Employees ].Rows[r][ PostalCode ] = dgvEmployee.Rows[r].Cells[ PostalCode ].Value; sqlAdapter.Update(dataset Employees ); dgvEmployee.Rows[e.RowIndex].Cells[6].Value = Delete ; } } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } } private void button1_Click( object sender EventArgs e) { Application.Exit(); } private void dgvEmployee_UserAddedRow( object sender DataGridViewRowEventArgs e) { try { int lastRow = dgvEmployee.Rows.Count - 2; DataGridViewRow nRow = dgvEmployee.Rows[lastRow]; DataGridViewLinkCell linkCell = new DataGridViewLinkCell(); dgvEmployee[6 lastRow] = linkCell; nRow.Cells[ Delete ].Value = Insert ; } catch (Exception ex) { MessageBox.Show(ex.Message); } } } }

sujitha m · Answer

Friends please understand my question.... i dont want text box in my windows application form(visual studio 2010).... i want to directly insert delete update a row in grid view with the help of C# coding... everyone giving a link for using textbox i can add delete update a data like that... i dont want that method...

Krishna Rajput Singh · Answer

Hi sujitha m thank you for writing below links helpful for you as per your questions... if find helpful anser mark accept my answer http://www.c-sharpcorner.com/UploadFile/1e050f/insert-update-and-delete-record-in-datagridview-C-Sharp/ if any question again ask me any time. Thanks krishna

Manikandan Murugesan · Answer

http://www.c-sharpcorner.com/UploadFile/9582c9/insert-update-delete-display-data-in-mysql-using-C-Sharp/ https://social.msdn.microsoft.com/Forums/en-US/04f1e480-8acf-450a-ba68-616d27628384/database-insertdeleteupdate-using-windows-forms?forum=winformsdatacontrols https://code.msdn.microsoft.com/windowsdesktop/InsertUpdateDeleteDisplay-26926e70 Refer the following articles

Manikandan Murugesan · Answer

Hai sujitha  Refer the following Link : http://www.c-sharpcorner.com/UploadFile/8c19e8/dynamically-adding-and-deleting-rows-in-gridview-and-saving/ http://www.c-sharpcorner.com/UploadFile/1e050f/insert-update-and-delete-record-in-datagridview-C-Sharp/

Gokhul Varman · Answer

Please refer the link for detailed explantion:https://msdn.microsoft.com/en-us/library/bb243852(v=office.12).aspx

HOW TO INSERT, DELETE and UPDATE a record