Gani tpt
asked on
OleDbDataAdapter UPDATE ERROR
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.OL EDB.4.0;Da ta Source=" + mdbname + ";";
OleDbCommand command = new OleDbCommand();
OleDbTransaction transaction = null;
OleDbConnection cn1 = new OleDbConnection(connection string1);
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.OL EDB.4.0;Da ta Source=" + mdbname + ";";
//using (OleDbConnection cn = new OleDbConnection(connection string))
using (OleDbConnection connection = new OleDbConnection(ConStr))
{
connection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("SELECT * FROM [" + TABLENAME + "]", connection);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapte r);
adapter.Fill(newdt);
adapter.Update(newdt);
}
What is the problem while inserting....?
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.OL
OleDbCommand command = new OleDbCommand();
OleDbTransaction transaction = null;
OleDbConnection cn1 = new OleDbConnection(connection
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.OL
//using (OleDbConnection cn = new OleDbConnection(connection
using (OleDbConnection connection = new OleDbConnection(ConStr))
{
connection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("SELECT * FROM [" + TABLENAME + "]", connection);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapte
adapter.Fill(newdt);
adapter.Update(newdt);
}
What is the problem while inserting....?
ASKER
nothing will happen after executing the below code. no update occurs in the database.
newdt.AcceptChanges();
adapter.Update(newdt);
simply executing the code. nothing will happen..?
Thanks for the update.
below is my requirement and queries.
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
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
In this case I don’t want to maintain the old data in my database. I want to store only the modified data.
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.OL EDB.4.0;Da ta Source=" + mdbname + ";";
OleDbConnection cn1 = new OleDbConnection(connection string1);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("SELECT * FROM [" + tablename + "]", cn1);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapte r);
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.
newdt.AcceptChanges();
adapter.Update(newdt);
simply executing the code. nothing will happen..?
Thanks for the update.
below is my requirement and queries.
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
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
In this case I don’t want to maintain the old data in my database. I want to store only the modified data.
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.OL
OleDbConnection cn1 = new OleDbConnection(connection
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("SELECT * FROM [" + tablename + "]", cn1);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapte
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.
ASKER
Are you catching my points...? if not pls. let me know..
ASKER
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..?
Hi Ganesh;
Looking at your code you seem to delete all rows from the table with the following code.
Looking at your code you seem to delete all rows from the table with the following code.
command.Connection = cn1;
string sqlTrunc = "DELETE FROM [" + tableName + "]";
OleDbCommand cmd = new OleDbCommand(sqlTrunc, cn);
cmd.ExecuteNonQuery();
But what I am having trouble with is this line of code
adapter.SelectCommand = new OleDbCommand("SELECT * FROM [" + (Schema.Rows[i]["TABLE_NAME"]).ToString() + "]", cn1);
More specifically with this part of the line
SELECT * FROM [" + (Schema.Rows[i]["TABLE_NAME"]
Not sure how the table name is being selected from that and does that match the table name used in the first code block of this post?
ASKER
pls. consider the below line for table name.
string tablename = "STUDENTTABLE";
SELECT * FROM [" + tablename + "];
PS : we have multiple tables to pass this case. so the deletion and updation is same for all the tables.
i have given one sample table for your reference.
see my completed code.
string tablename = "STUDENTTABLE";
SELECT * FROM [" + tablename + "];
PS : we have multiple tables to pass this case. so the deletion and updation is same for all the tables.
i have given one sample table for your reference.
see my completed code.
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();
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);
return dt;
}
}
catch (Exception ex)
{
}
}
cn.Close();
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent..
ASKER
still i am facing the problem to insert the data table values in to db.
see my complete code.
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...?
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...?
To your question, "i want to insert a record in the below place..??", what below place?
To your question, "How to insert instead of adapter.fill and update...?", you can create a Connection object and Command object, specify a SQL command in the Command object and then you can execute one of the ExecuteXXX methods of the Command object.
To your question, "If i use the above code it says either error or i am unable to do (insert) anything...?", you will need to post the exception and inner exception that it is giving you and on which line of code it is happening on in order for me to be able to help with this.
To your question, "How to insert instead of adapter.fill and update...?", you can create a Connection object and Command object, specify a SQL command in the Command object and then you can execute one of the ExecuteXXX methods of the Command object.
To your question, "If i use the above code it says either error or i am unable to do (insert) anything...?", you will need to post the exception and inner exception that it is giving you and on which line of code it is happening on in order for me to be able to help with this.
ASKER
below is my answer.
1. i want to insert a record in the below place.??", what below place?
ANS : adapter.Update(NDT); // Here i want to insert a record instead of update method.
2. ok..
3. getting error in adapter.Update(NDT);
pls. let me know if you need any more details...
1. i want to insert a record in the below place.??", what below place?
ANS : adapter.Update(NDT); // Here i want to insert a record instead of update method.
2. ok..
3. getting error in adapter.Update(NDT);
pls. let me know if you need any more details...
To your statement, "ANS : adapter.Update(NDT); // Here i want to insert a record instead of update method.", the DataAdapter called adapter in your code does the following when called
To your statement, "getting error in adapter.Update(NDT); ", don't know the error you are getting but suspect that this is a possible reason
If this does not answer your new quest please open a net question. Thanks
Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table."So that one method does all of them. Now in your code as I stated in an earlier post will do nothing when these two commands are executed after each other.
adapter.Fill(NDT);
adapter.Update(NDT);
The reason being is that you fill the DataTable from the database then immediately call the Update method, you have made no changes, no inserted new rows, and no rows were deleted. Therefore no changes will be made to the database.To your statement, "getting error in adapter.Update(NDT); ", don't know the error you are getting but suspect that this is a possible reason
From Microsoft Documentation
If INSERT, UPDATE, or DELETE statements have not been specified, the Update method generates an exception. However, you can create a SqlCommandBuilder or OleDbCommandBuilder object to automatically generate SQL statements for single-table updates if you set the SelectCommand property of a .NET Framework data provider. Then, any additional SQL statements that you do not set are generated by the CommandBuilder. This generation logic requires key column information to be present in the DataSet. For more information see Generating Commands with CommandBuilders.
If this does not answer your new quest please open a net question. Thanks
Open in new window
so you fill the DataTable with rows from the database and then immediately update the database but you have not change anything? Also once you update rows or insert rows into the DataTable and before you send the Update command you need to accept the changes to the DataTable, something like thisOpen in new window