OleDbDataAdapter UPDATE ERRORRSS

• • Reply
  gani7787

  Member

  165 Points

  648 Posts

  OleDbDataAdapter UPDATE ERROR

  Aug 11, 2017 09:33 AM|gani7787|LINK

  Hi,

  I am trying to update table values using OLEDATA ADAPTER.

  But, i am getting following Error

   it will throw the error in adp1.Update(newdt)

  Error : Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

  My datatable structrue below :

  Subjects	Marks	Department	Name
  Maths	100	IT	ABC
  Science	80		ABC
  GK	95		ABC
  History	78		ABC

  Complete source code :

                                                                                                                                          

   using (OleDbConnection cn = new OleDbConnection(connectionstring))
                  {
                      cn.Open();
                      DataTable Schema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                      for (int i = 0; i <= Schema.Rows.Count - 1; i++)
                      {
                          try
                          {                            
                              if ((Schema.Rows[i]["TABLE_NAME"]).ToString() == tableName)
                              {
  
  
                                  using (OleDbDataAdapter adp1 = new OleDbDataAdapter("SELECT * FROM [" + (Schema.Rows[i]["TABLE_NAME"]).ToString() + "]", cn))
                                  {
  
                                      OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adp1);
                                      adp1.Update(newdt);
  
                                      commandBuilder.QuotePrefix = "[";
                                      commandBuilder.QuoteSuffix = "]";
                                      dt = newdt;
  
                                      return dt;
                                  }
                              }                            
                          }
                          catch (Exception ex)
                          {
  
                          }
                      }
                      cn.Close();
                  }
              }
              return dt;

  Can you tell us where is the problem.

  I'm am new to update in Access through OLEDB Adapter for update.

• • Reply
  Mikesdotnett...

  All-Star

  187621 Points

  27188 Posts

  Moderator

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 11, 2017 07:17 PM|Mikesdotnetting|LINK

  You shouldn't use DataSets for updating records in ASP.NET. You should SQL statements instead:

  https://www.mikesdotnetting.com/article/26/parameter-queries-in-asp-net-with-ms-access

  Cheers, Mike
  
  ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
• • Reply
  Cathy Zou

  Star

  8640 Points

  2882 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 14, 2017 02:13 AM|Cathy Zou|LINK

  Hi gani7787

  The table you're running the SELECT against doesn't have a primary key column. It cannot create any other UPDATE or DETELE statements because you have no way of uniquely identifying every single record in your table.

  You Have to Mention primary key in your Table.

  https://social.msdn.microsoft.com/Forums/en-US/5dec5633-ac84-48d9-8fd6-5c7601be4ccd/exception-dynamic-sql-generation-for-the-updatecommand-is-not-supported-against-a-selectcommand?forum=Vsexpressvcs

  https://www.codeproject.com/Questions/55136/SQL-problem-in-saving-SOLVED

  Then before executing update command of dataApdapter you have to set update command of dataAdapter with CommandBuilder object like this

  public DataTable CreateCmdsAndUpdate(string connectionString,
      string queryString) 
  {
      using (OleDbConnection connection = new OleDbConnection(connectionString))
      {
          OleDbDataAdapter adapter = new OleDbDataAdapter();
          adapter.SelectCommand = new OleDbCommand(queryString, connection);
          OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
  
          connection.Open();
  
          DataTable customers = new DataTable();
          adapter.Fill(customers);
  
          // code to modify data in DataTable here
  
          adapter.Update(customers);
  
          return customers;
      }
  }

  Related links:

  http://www.c-sharpcorner.com/uploadfile/mahesh/updating-the-database-using-the-update-method-in-ado-net/

  https://msdn.microsoft.com/en-us/library/z1z2bkx2(v=vs.110).aspx

  Best Regards

  Cathy

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  gani7787

  Member

  165 Points

  648 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 16, 2017 12:04 PM|gani7787|LINK

  Thanks for your update.

  my current database is MS-Access.

  I used primary key in my table.

  Also, First i am deleting record and then followed by inserting.

  But, while inserting i am getting Error : Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

  I am unable to find out the exact problem for this.

  See my complete code.

  string connectionstring1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbname + ";";
  OleDbCommand command = new OleDbCommand();
  OleDbTransaction transaction = null;
  OleDbConnection cn1 = new OleDbConnection(connectionstring1);
  cn1.Open();

  // Start a local transaction
  transaction = cn1.BeginTransaction();

  // Assign transaction object for a pending local transaction.
  command.Connection = cn1;
  command.Transaction = transaction;

  // Execute the commands.
  string sqlTrunc = "DELETE FROM [" + tableName + "]";
  OleDbCommand cmd = new OleDbCommand(sqlTrunc, cn);
  
  cmd.ExecuteNonQuery();

  transaction.Commit();
  

  string ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbname + ";";
  //using (OleDbConnection cn = new OleDbConnection(connectionstring))
  using (OleDbConnection connection = new OleDbConnection(ConStr))
  {
  connection.Open();
  OleDbDataAdapter adapter = new OleDbDataAdapter();
  adapter.SelectCommand = new OleDbCommand("SELECT * FROM [" + TABLENAME + "]", connection);
  OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

  adapter.Fill(newdt);

  adapter.Update(newdt);

  }

  What is the problem while inserting....?

• • Reply
  gani7787

  Member

  165 Points

  648 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 17, 2017 06:39 AM|gani7787|LINK

  below is my requirement and queries.

  1.        By default, my table data and structure is below.

  Datatable newdt = new datatable();

  Table 1

  CATEGORY	PNUMBER	CPOS	CLEN
  AREA
  CTYPE
  FCODE	6
  ITYPE	3
  LSIZE	4
  LSP
  UNIT	1
  AMS

  1.        After some point of time I am changing/modifying the fields value through my front end screen. So my datatable value is below.

  Table 2

  CATEGORY	PNUMBER	CPOS	CLEN
  AREA
  CTYPE
  FCODE	6
  ITYPE	30
  LSIZE	4
  LSP
  UNIT	50
  AMS

  1.        In this case I don’t want to maintain the old data in my database.  I want to store only the modified data.
  2.        For that purpose only, I am deleting the data and trying to insert the new data using datatable.

  If I am using the below I am not able to maintain the modify datatable values in newdt.

  Below is your code.

  using (var adp1 = new OleDbAdapter("SELECT * FROM ..."))

  {  

     adp1.Fill(newdt);

     //Remove all the rows (delete from...)

     //newdt.Rows.Clear();

     //(Optional) Add some new rows

  ./**********************************************

  I want to maintain the datatable values. I can’t add rows one by one. Because it is bunch of record which is available in datatable like newdt.

  Error : Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

  /************************************************

     //Commit changes

     adp1.Update(newdt);

  };

  Finally i modified the below changes. but, not working. (Overwriting the exisitng table values)
  
    string connectionstring1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbname + ";";
                                  OleDbConnection cn1 = new OleDbConnection(connectionstring1);
   OleDbDataAdapter adapter = new OleDbDataAdapter();
                                  adapter.SelectCommand = new OleDbCommand("SELECT * FROM [" + tablename + "]", cn1);
                                  OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
  
                                  DataTable DT1 = new DataTable();
                                  DT1 = newdt.Copy();
  
                                  newdt.Rows.Clear();
  
                                 
  
                                  //adapter.Fill(newdt);
  
                                  newdt.AcceptChanges();
  
                                  //newdt.Rows.Clear();
  
                                  newdt = DT1.Copy();
  
                                  adapter.Update(newdt);
  
  Error : The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

• • Reply
  gani7787

  Member

  165 Points

  648 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 17, 2017 11:53 AM|gani7787|LINK

  Are you catching my points...? if not pls. let me know..

• • Reply
  gani7787

  Member

  165 Points

  648 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 17, 2017 01:01 PM|gani7787|LINK

  The below code is partially working. But, i don't want to add/hardcode values in to datatable.

  updating datatable values already formed and stored into newdt.

  My final partial working code :

                                                                                                                                                                                            

   string connectionstring1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbname + ";";
                                  OleDbConnection cn1 = new OleDbConnection(connectionstring1);
                                  OleDbCommand command = new OleDbCommand();
  
  
                                  command.Connection = cn1;
                                  string sqlTrunc = "DELETE FROM [" + tableName + "]";
                                  OleDbCommand cmd = new OleDbCommand(sqlTrunc, cn);
                                  cmd.ExecuteNonQuery();
  
  
                                  OleDbDataAdapter adapter = new OleDbDataAdapter();
                                  adapter.SelectCommand = new OleDbCommand("SELECT * FROM [" + (Schema.Rows[i]["TABLE_NAME"]).ToString() + "]", cn1);
                                  OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
  
  
  
                                  adapter.Fill(newdt);
  
                                  ////Remove all the rows (delete from...)
                                  newdt.Rows.Clear(); 

                                   /*************START : HIGHLIGHT ******************************
  
                                  newdt.Rows.Add("AREA", null, null, null);
                                  newdt.Rows.Add("CTYPE", null, null, null);
                                  newdt.Rows.Add("FCODE", "3", null, null);
                                  newdt.Rows.Add("ITYPE", "6", null, null);
                                  newdt.Rows.Add("LSIZE", "2", null, null);
                                  newdt.Rows.Add("LSP", "5", null, null);
                                  newdt.Rows.Add("UNIT", "5", null, null);
                                  newdt.Rows.Add("AMS", null, null, null);
  
  /************* END : HIGHLIGHT *******************************

  
  
  
                                  //Commit changes
                                  adapter.Update(newdt);

  The Highlighted portion is manually added new rows and hardcoded.

  But,i already formed and stored new values in newdt table. So, i just want to call the portion only.

  Also, First i am deleting the existing record. It means, the same kind of record i want to insert which is some fields modifications.

  The above code is working. But, instead of adding the hardcode values, i just want to call the stored datatable values in newdt.

  How to do that..?

• • Reply
  Mikesdotnett...

  All-Star

  187621 Points

  27188 Posts

  Moderator

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 21, 2017 07:37 PM|Mikesdotnetting|LINK

  Please stop trying to use a DataSet for this operation. I am happy to help you if you want to try my suggestion.

  Cheers, Mike
  
  ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
• • Reply
  gani7787

  Member

  165 Points

  648 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 22, 2017 09:02 AM|gani7787|LINK

  Yes. i need your help...

• • Reply
  Mikesdotnett...

  All-Star

  187621 Points

  27188 Posts

  Moderator

  Re: OleDbDataAdapter UPDATE ERROR

  Aug 22, 2017 12:38 PM|Mikesdotnetting|LINK

  Where are the updated values coming from? A Form? Can you show some code?

  Cheers, Mike
  
  ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages
• • Reply
  gani7787

  Member

  165 Points

  648 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Sep 19, 2017 11:13 AM|gani7787|LINK

  sorry for the late reply.

  see my complete code.

  	    
  	  private void btnupdate_Click(object sender, EventArgs e)
         	  {
              DataTable Dt1 = new DataTable();
  
             
              dbData.INSERTUPDATEdb("Employee", ref Dt1, true); // This is for to fetch data to display in dropdown
              Dt1.Rows[0][0] = "1001";
              Dt1.Rows[1][0] = "1002";
              Dt1.Rows[2][0] = "1003";
              Dt1.Rows[3][0] = "1004";
  
              
  
              Dt1.Rows[0][1] = CboBox1.Text; //A1
              Dt1.Rows[1][1] = CboBox2.Text; //A2
              Dt1.Rows[2][1] = CboBox3.Text;
              Dt1.Rows[3][1] = CboBox4.Text; //A4
  
  
              Dt1.Rows[0][2] = CboBox5.Text; //100
              Dt1.Rows[1][2] = CboBox6.Text; //200
              Dt1.Rows[2][2] = CboBox7.Text;
              Dt1.Rows[3][2] = CboBox8.Text; //500
      
  
              Dt1.Rows[0][3] = CboBox8.Text; //A
              Dt1.Rows[1][3] = CboBox9.Text; //B
              Dt1.Rows[2][3] = CboBox10.Text;
              Dt1.Rows[3][3] = CboBox11.Text; //D
  
              dbData.INSERTUPDATEdb("Employee", ref Dt1, false);
  	 }
  
  
  
  	  public static DataTable INSERTUPDATEdb(string tableName, ref DataTable NDT, bool flag)
            {
              DataTable retVal = new DataTable();
              DataTable dt  = new DataTable();
      	    string mdbname = "MVM/Local/Project1";
  
              string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbname + ";";
              if (flag == true)
              {
                  using (OleDbConnection cn = new OleDbConnection(connectionstring))
                  {
                      cn.Open();
                      DataTable Schema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                      for (int i = 0; i <= Schema.Rows.Count - 1; i++)
                      {
                          try
                          {
                              NDT.TableName = (Schema.Rows[i]["TABLE_NAME"]).ToString();
                              if ((Schema.Rows[i]["TABLE_NAME"]).ToString() == tableName)
                              {
                                  using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + (Schema.Rows[i]["TABLE_NAME"]).ToString() + "]", cn))
                                  {
                                     
                                      adapter.Fill(NDT);
  
                                  }
                                  return NDT;
                              }
                          }
                          catch (Exception ex)
                          {
  
                          }
                      }
                      cn.Close();
                  }
              }
              else
              {
                  using (OleDbConnection cn = new OleDbConnection(connectionstring))
                  {
                      cn.Open();
                      DataTable Schema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                      for (int i = 0; i <= Schema.Rows.Count - 1; i++)
                      {
                          try
                          {                            
                              if ((Schema.Rows[i]["TABLE_NAME"]).ToString() == tableName)
                              {
  
                                  string connectionstring1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbname + ";";
                                  OleDbConnection cn1 = new OleDbConnection(connectionstring1);
                                  OleDbCommand command = new OleDbCommand();
  
  
                                 
                                  OleDbDataAdapter adapter = new OleDbDataAdapter();
                                  adapter.SelectCommand = new OleDbCommand("SELECT Empnumber,Name,Marks,Grade FROM [" + (Schema.Rows[i]["TABLE_NAME"]).ToString() + "]", cn1);
                                  OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
  
                                  adapter.Fill(NDT);
  
                                  adapter.Update(NDT);
  
                                
  
  
                                return dt;
  
                              }                            
                          }
                          catch (Exception ex)
                          {
  
                          }
                      }
                      cn.Close();
                  }
              }
              return dt;
          }

  i want to insert a record in the below place..??

  How to insert instead of adapter.fill and update...?

  If i use the above code it says either error or i am unable to do  (insert) anything...?

• • Reply
  Cathy Zou

  Star

  8640 Points

  2882 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Sep 20, 2017 02:04 AM|Cathy Zou|LINK

  Hi kalyanns,

  From your description, you want to insert data to database.

  If that the case, you could use the following code:

  using(OleDbConnection myCon = new OleDbConnection(ConfigurationManager.ConnectionStrings["DbConn"].ToString()))
  {
     OleDbCommand cmd = new OleDbCommand(); 
     cmd.CommandType = CommandType.Text; 
     cmd.CommandText = "insert into Items ([Item_Name],[Item_Price]) values (?,?);
     cmd.Parameters.AddWithValue("@item", itemNameTBox.Text);
     cmd.Parameters.AddWithValue("@price", Convert.ToDouble(itemPriceTBox.Text)); 
     cmd.Connection = myCon; 
     myCon.Open(); 
     cmd.ExecuteNonQuery(); 
     System.Windows.Forms.MessageBox.Show("An Item has been successfully added", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); 
  }

  Related links:

  https://stackoverflow.com/questions/10941284/how-to-insert-a-record-into-a-access-table-using-oledb

  http://csharp.net-informations.com/dataadapter/insertcommand-oledb.htm

  Best regards

  Cathy

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  gani7787

  Member

  165 Points

  648 Posts

  Re: OleDbDataAdapter UPDATE ERROR

  Sep 20, 2017 09:21 AM|gani7787|LINK

  I will try your code for insertion.

  what is values (?,?)

  cmd.CommandText = "insert into Items ([Item_Name],[Item_Price]) values (?,?); // ?,?

  is it values (@item,@price)..? Am i right..?

• • Reply
  Mikesdotnett...

  All-Star

  187621 Points

  27188 Posts

  Moderator

  Re: OleDbDataAdapter UPDATE ERROR

  Sep 20, 2017 03:39 PM|Mikesdotnetting|LINK

  You didn't bother to look at the article I linked to? And is this actually a web application?

  Cheers, Mike
  
  ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages