Update Command Not Working

[pendragon]

Hi All

 

I have a strange problem, I have a routine for creating an update command based on the table I send it, If I update a record on one table all is OK if I try to update a record on another table I get the following error

 

Concurrency Violation : The Update Command Affected 0 Records

 

Is there any way to find out why no records where affected (I did check to make sure that there was a change to make)

 

What I do is this (There is a lot of code but I am hoping that someone can see a problem that I can't)

 

Setup table

public static void OpenSalesOrders(int tableno, string ASCorDESC) 
{
SQL = "SELECT * FROM SOPHeader ORDER BY SOPOrdNo " + ASCorDESC;
cn = new OleDbConnection(dataaccess.SLConString);
cn.Open();

da = new OleDbDataAdapter(SQL, cn);
da.TableMappings.Add("Table", "SOPHeader");

Application.DoEvents();

tbl = ds.Tables.Add("SOPHeader");
col = tbl.Columns.Add("SOPOrdNo", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = -1;
col.AutoIncrementStep = -1;

col = tbl.Columns.Add("SOPAccID", typeof(string));
col.AllowDBNull = false;
col.MaxLength = 16;

col = tbl.Columns.Add("SOPCustRef", typeof(string));
col = tbl.Columns.Add("SOPEntry", typeof(string));

col = tbl.Columns.Add("SOPProcess", typeof(string));

col = tbl.Columns.Add("SOPDate", typeof(DateTime));
col.AllowDBNull = true;

col = tbl.Columns.Add("SOPDelDate", typeof(DateTime));
col.AllowDBNull = true;

col = tbl.Columns.Add("SOPDueDate", typeof(DateTime));
col.AllowDBNull = true;

col = tbl.Columns.Add("SOPTotal", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPDiscPer", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPDiscAmt", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPNett", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPGross", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT1", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT2", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT3", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT4", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT5", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT6", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT7", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT8", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVAT9", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVATZ", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVATE", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPVATX", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPValInv", typeof(decimal));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPPOP", typeof(bool));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPDelAddKey", typeof(int));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPDelAddName", typeof(string));
col = tbl.Columns.Add("SOPDelAdd", typeof(string));
col = tbl.Columns.Add("SOPDelPostCode", typeof(string));
col = tbl.Columns.Add("SOPNar1", typeof(string));
col = tbl.Columns.Add("SOPNar2", typeof(string));
col = tbl.Columns.Add("SOPProj", typeof(int));
col.DefaultValue = 0;
col = tbl.Columns.Add("SOPTool", typeof(bool));
col.DefaultValue = false;
col = tbl.Columns.Add("SOPPOPKey", typeof(int));
col = tbl.Columns.Add("SOPInvoice", typeof(bool));
col = tbl.Columns.Add("Locked", typeof(bool));
col = tbl.Columns.Add("LockedBy", typeof(string));
col = tbl.Columns.Add("LineCount", typeof(int));
col.AutoIncrement = true;
col.AutoIncrementSeed = 0;
col.AutoIncrementStep = 1;

tbl.PrimaryKey = new DataColumn[] {tbl.Columns["SOPOrdNo"]};

tblSOPHeader = ds.Tables[tableno];

colSOPOrdNo = tblSOPHeader.Columns["SOPOrdNo"];
colSOPAccID = tblSOPHeader.Columns["SOPAccID"];
colSOPCustRef = tblSOPHeader.Columns["SOPCustRef"];
colSOPEntry = tblSOPHeader.Columns["SOPEntry"];
colSOPProcess = tblSOPHeader.Columns["SOPProcess"];
colSOPDate = tblSOPHeader.Columns["SOPDate"];
colSOPDelDate = tblSOPHeader.Columns["SOPDelDate"];
colSOPDueDate = tblSOPHeader.Columns["SOPDueDate"];
colSOPTotal = tblSOPHeader.Columns["SOPTotal"];
colSOPDiscPer = tblSOPHeader.Columns["SOPDiscPer"];
colSOPDiscAmt = tblSOPHeader.Columns["SOPDiscAmt"];
colSOPNett = tblSOPHeader.Columns["SOPNett"];
colSOPVAT = tblSOPHeader.Columns["SOPVAT"];
colSOPGross = tblSOPHeader.Columns["SOPGross"];
colSOPVAT1 = tblSOPHeader.Columns["SOPVAT1"];
colSOPVAT2 = tblSOPHeader.Columns["SOPVAT2"];
colSOPVAT3 = tblSOPHeader.Columns["SOPVAT3"];
colSOPVAT4 = tblSOPHeader.Columns["SOPVAT4"];
colSOPVAT5 = tblSOPHeader.Columns["SOPVAT5"];
colSOPVAT6 = tblSOPHeader.Columns["SOPVAT6"];
colSOPVAT7 = tblSOPHeader.Columns["SOPVAT7"];
colSOPVAT8 = tblSOPHeader.Columns["SOPVAT8"];
colSOPVAT9 = tblSOPHeader.Columns["SOPVAT9"];
colSOPVATZ = tblSOPHeader.Columns["SOPVATZ"];
colSOPVATE = tblSOPHeader.Columns["SOPVATE"];
colSOPVATX = tblSOPHeader.Columns["SOPVATX"];
colSOPValInv = tblSOPHeader.Columns["SOPValInv"];
colSOPPOP = tblSOPHeader.Columns["SOPPOP"];
colSOPDelAddKey = tblSOPHeader.Columns["SOPDELAddKey"];
colSOPDelAddName = tblSOPHeader.Columns["SOPDelAddName"];
colSOPDelAdd = tblSOPHeader.Columns["SOPDelAdd"];
colSOPDelPostCode = tblSOPHeader.Columns["SOPDelPostCode"];
colSOPNar1 = tblSOPHeader.Columns["SOPNar1"];
colSOPNar2 = tblSOPHeader.Columns["SOPNar2"];
colSOPProj = tblSOPHeader.Columns["SOPProj"];
colSOPTool = tblSOPHeader.Columns["SOPTool"];
colSOPPOPKey = tblSOPHeader.Columns["SOPPOPKey"];
colSOPInvoice = tblSOPHeader.Columns["SOPInvoice"];
colSOPLocked = tblSOPHeader.Columns["Locked"];
colSOPLockedBy = tblSOPHeader.Columns["LockedBy"];
colSOPLineCount = tblSOPHeader.Columns["LineCount"];

da.Fill(ds, "SOPHeader");
dataaccess.rcSOPHeader = (int)dataaccess.tblSOPHeader.Rows.Count - 1;

da.Dispose();
tbl.Dispose();
col.Dispose();
cn.Close();
cn.Dispose();
Application.DoEvents();
}

 

Change Something

 

private void button4_Click(object sender, System.EventArgs e)
{
this.SOPHeader[dataaccess.colSOPNar1] += Environment.NewLine + "Has this Worked All Done in the Dataaccess class";
dataaccess.FormCallBack c = new Library.dataaccess.FormCallBack(ShowFields); 
dataaccess.UpdateRowCommand(dataaccess.tblSOPHeader, c);
}

 

Then Update

public static void UpdateRowCommandNoUnlock(DataTable theTable) 
{
string TableName = theTable.TableName;
string sql = "UPDATE [" + TableName + "] SET ";
OleDbCommand cmd = null;
int totalrows = theTable.Columns.Count;
bool started = false;

SQLStatement = GetSQLStatement(TableName);
cn = new OleDbConnection(ADOConnectionString);
cn.Open();

dataaccess.da = new OleDbDataAdapter(dataaccess.SQLStatement, dataaccess.cn);

for (int rowcount = 1; rowcount < totalrows; rowcount++) 
{
	if (theTable.Columns[rowcount].ColumnName != "LineCount") 
	{
		if (started == false) 
		{
			sql += theTable.Columns[rowcount].ColumnName + " = ?";
			started = true;
		}
		else 
		{
			sql += ", " + theTable.Columns[rowcount].ColumnName + " = ?";
		}
	}
}

sql += " WHERE ";
started = false;

for (int rowcount = 0; rowcount < totalrows; rowcount++) 
{
	if (theTable.Columns[rowcount].ColumnName != "LineCount")
	{
		if (started == false) 
		{
			sql += theTable.Columns[rowcount].ColumnName + " = ?";
			started = true;
		}
		else 
		{
			sql += " AND " + theTable.Columns[rowcount].ColumnName + " = ?";
		}
	}
}

cn = new OleDbConnection(ADOConnectionString);
cmd = new OleDbCommand(sql, cn);
OleDbParameterCollection pc = cmd.Parameters;
OleDbParameter param;

for (int rowcount = 1; rowcount < totalrows; rowcount++) 
{
	if (theTable.Columns[rowcount].ColumnName != "LineCount")
	{
		RowDataType = GetDataType(theTable.Columns[rowcount].DataType);
		pc.Add(theTable.Columns[rowcount].ColumnName + "_New", RowDataType, 0, theTable.Columns[rowcount].ColumnName);
	}
}

for (int rowcount = 0; rowcount < totalrows; rowcount++) 
{
	if (theTable.Columns[rowcount].ColumnName != "LineCount") 
	{
		RowDataType = GetDataType(theTable.Columns[rowcount].DataType);
		param = pc.Add(theTable.Columns[rowcount].ColumnName + "_Orig", RowDataType, 0, theTable.Columns[rowcount].ColumnName);
		param.SourceVersion = DataRowVersion.Original;
	}
}
da.UpdateCommand = cmd;

try
{
	da.Update(ds, TableName);
	ds.AcceptChanges();
}
catch (DBConcurrencyException ex)
{
	MessageBox.Show("Update Failed", ex.GetType().ToString());
	MessageBox.Show(ex.ToString());
	MessageBox.Show(ex.Message.ToString());
	MessageBox.Show(ex.TargetSite.ToString());
	MessageBox.Show(ex.Source.ToString());
}
finally 
{
	cn.Dispose();
	da.Dispose();
}
}

 

I have checked to see that the update command is correct and it is

 

What am I doing wrong or how can I find out the why ?????????????????

 

Thanks

[Diesel]

Ack.. I can't get over that you are creating so many adapters and connection objects! Inefficient. Also, calling Dispose is inefficient also, you don't need to. Besides the way you are doing it is wrong.

 

da.Dispose();

tbl.Dispose();

col.Dispose();

cn.Close();

cn.Dispose();

 

You should start with the innermost and dispose outwardly, and calling dispose calls Connection.Close(); automatically.

 

I have 3 questions:

1. Do the tables have the same schema?

 

tbl = ds.Tables.Add("SOPHeader");

col = tbl.Columns.Add("SOPOrdNo", typeof(int));

col.AutoIncrement = true;

col.AutoIncrementSeed = -1;

col.AutoIncrementStep = -1;

 

2. Why did you set AutoIncrementStep = -1?

 

3. Can you show the UpdateRowCommand function

 

dataaccess.UpdateRowCommand(dataaccess.tblSOPHeader, c);

[pendragon]

Diesel

 

Thanks for your comments.

 

Ack.. I can't get over that you are creating so many adapters and connection objects! Inefficient. Also, calling Dispose is inefficient also, you don't need to. Besides the way you are doing it is wrong.

 

I have been learning VB.NET then C# from Books for about a year and have not had anyone to bounce questions, comments and Ideas against (except for this forum, which is very good :D ) so I doubt that I am doing everything in the best way yet, I have only been playing with ADO.NET for the passed month so am no where near an expert on it. You say I am creating to many adapters and connection objects, I have 9 databases with just over 100 tables, are you saying that I should just define cn and da once and then redefine them as I use them? The sales order program alone looks at a table for Order Header, Order Lines, Sales Accounts, Delivery Header, Delivery Lines and Sales Analysis these are all in the sales ledger databases but it also looks at Stock Parts and production from the stock databases and Route card file from the costings database so three different connection definitions.

 

One of the books I have read (Can't remember if it was a VB.NET or C#.NET book) says that it is a good Idea to get into the habit of calling the dispose method to get rid of no longer needed objects instead of leaving it to the GC, one of the reasons given was that they can sometimes hang around for a long time.

 

If I am doing things wrong then I would really appreciate being told and also how I should be doing it.

 

As for your questions

 

1) No, the other table is for Quarantine Stock.

 

2) SOPOrdNo is an Auto Incrementing Access field, step is set to -1 so that any new orders that are added will not be given a tempory order number that already exists.

 

3) This is a typo

dataaccess.UpdateRowCommand(dataaccess.tblSOPHeader, c);

 

Should Read

dataaccess.UpdateRowCommandNoUnlock(dataaccess.tblSOPHeader, c);

 

 

Ack.. I can't get over that you are creating so many adapters and connection objects! Inefficient

 

Thanks to your first comment I have seen something in the UpdateRowCommandNoUnlock that Is wrong.

 

   cn = new OleDbConnection(ADOConnectionString);   // THIS SHOULD NOT BE HERE
   cmd = new OleDbCommand(sql, cn); 
   OleDbParameterCollection pc = cmd.Parameters; 
   OleDbParameter param; 

 

I am suprised that I did not notice this before as I had a problem with @@IDENTITY not returning newly created auto numbers because I defined cn again half way down the routine that fetched it.

 

I don't know if this will solve the problem but will take it out and let you know. (I am supposed to be on holiday next week but will pop in on Monday to find out as this has been bugging me for 3 days).

 

Thanks.

[Joe Mamma]

2. Why did you set AutoIncrementStep = -1?

Incrementing step and seed at -1 prevents multiusers from stepping on each other. on inserts the DB will fill the resulting datatable with the generated values

 

this doesn't only work with access as there is one more step needed.

 

everyone should own O'Reilly's Ado.Net Cookbook

[pendragon]

Taking out the extra connection definition did not solve the problem.

 

I am importing the data from a csv file and I had a problem before with a text field that was new in the access database and I was not setting it to anything in the import, this caused the update command not to work and I had to set it to "" to get it working.

 

Hopefully this is the same sort of problem so I think I will write a test program and build up the command bit by bit to see if it falls over on one of the fields :o