HOW TO DELETE RECORD IN A DATABASE ?

HOW TO DELETE RECORD IN A DATABASE ?

• David d48701

  if you bind the datatable/dataset to a datagridview and you modify the datagridview, changes are automatically made to the underlying datasource (dataset/datatable) - when you finally want to commit the changes to the database, call the Update() method, giving it the dataset.

   

  Also do not use the AcceptChanges() before hand otherwise nothing will get updated, the dataadapter looks for changes to be made and executes the command appropriately but since you are calling AcceptChanges, it tells the dataset that yes, ive accepted changes therefore nothing to update in the database - this is not what you want. Remove acceptchanges().

   

  If you still wish to manually update the dataset by removing a record, what you have done should be ok (take out the acceptchanges()) but you then need to call the update() method, as suggested before but making sure that you have an UpdateCommand() on the dataAdapter created, using something like the SQLCommandBuilder (Or OLEDBCommandBuilder if you are using OleDb datasource)

  
  
  

• Evan Mulawski

  cmd.CommandText = "DELETE MTN_Request WHERE ReqNo ="+zzz.ToString();

  
  
  

• ahmedilyas

  Hi,

  I am using data set and this data bound to datagridview. With data adapter I have to know one field for delete key. I want base on row position in datagridview and we can delete also in database. I did in VB6 and very simple.

  Thanks.

  Joseph

  
  
  

• minibus

  VB "My command string" & " my arg"

  C# "My command string" + " my arg";

  
  

• barkingdog

  I used data adapter and it works and here my code, but I have another problem with variable in SQL statement, variable zzz does not work. How to use variable in SQL statement in C#. In VB6 i use '" & zzz & "' but it can not work in C#.

  cmd.CommandText = "DELETE MTN_Request WHERE ReqNo = zzz";

  Complete code:

  string zzz;

  zzz = dataGridView1.CurrentRow.Cells[0].Value.ToString();

  string sConnectionString1;

  sConnectionString1 = "Password=; user id=sa"

  + "provider=SQLOLEDB;"

  + "Trusted_Connection=yes;"

  + "Initial Catalog=MAINTENANCE;"

  + "Data Source=SQLSERV";

  System.Data.SqlClient.SqlConnection sqlConnection1 =

  new System.Data.SqlClient.SqlConnection(sConnectionString1);

  System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

  cmd.CommandType = System.Data.CommandType.Text;

  cmd.CommandText = "DELETE MTN_Request WHERE ReqNo = zzz";

  cmd.Connection = sqlConnection1;

  sqlConnection1.Open();

  cmd.ExecuteNonQuery();

  sqlConnection1.Close();

  Thanks. Joseph

  
  
  

• Joe Morel - MSFT

  This is my coding for delete record in a database and it works.

  string zzz;

  zzz = dataGridView1.CurrentRow.Cells[0].Value.ToString();

  string sConnectionString1;

  sConnectionString1 = "Password=; user id=sa"

  + "provider=SQLOLEDB;"

  + "Trusted_Connection=yes;"

  + "Initial Catalog=MAINTENANCE;"

  + "Data Source=SQLSERV";

  System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection(sConnectionString1);

  System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

  cmd.CommandType = System.Data.CommandType.Text;

  cmd.CommandText = "DELETE MTN_Request WHERE ReqNo ="+zzz.ToString();

  cmd.Connection = sqlConnection1;

  sqlConnection1.Open();

  cmd.ExecuteNonQuery();

  sqlConnection1.Close();

  Thanks.

  Joseph

  
  
  

• kaborka

  Thanks, It works.

  Joseph

  
  
  

• vimaljonn

  If you are using a DataAdapter, you need to use the Update method on it in order to have the changes reflected into the database.
  

• Deldy

  Please, get in the habit of using Parameters with your database queries. They make coding easier, in the longrun, because they will handle all the formatting. Furthermore, they will handle strings in such a way as to prevent SQL injection hacker attacks when you are writing a Web based application.

  cmd.CommandText="DELETE MTN_Request WHERE ReqNo = @reqNO";

  cmd.Parameters.Add( "@reqNo", zzz );

  cmd.ExecuteNonQuery();

  etc.

  
  

• MWatts

  If you just use a simple dataapdapter by hand like as : oleBbdata=new OleDbDataAdapter(select, conn); you can not update to database

  Im solve this problem like this.

  fill to a dataSet by hand

  DataSet ds =new DataSet();

  oleDbdata.Fill(ds,"NameyourTable");

  and you use System.windows.Forms.BindingmanagerBase bmb ;

  bmb=this.bindingContext[ds,"YourTableName"];

  when button3 -Click

  {

  // delete it from datatabse

  // for example your key column as first column (col1) in your select statement

  string delsql=" delete from YourTable where col1 like + ds.Tables["your tablename"].row[bmb.Position][0].Tostring()//

  execute this delsql

  remove from dataset

  }

  does it help you

  dongmt,Vietnam

  
  
  
• HOW TO DELETE RECORD IN A DATABASE ?