odbcdataadapter.update "concurrency exception" due to fields number?

[citytroop]

Hi,
I am creating a generic data access module for an application and I
have come across the following problem:


A form calls the function that loads data from a table to a grid. Then
the code below is called in order to effect any changes in the dataset.
The problem appears with a table that has 43 columns. When in the
selectCommand I pass a select statement containing up to 32 columns it
works without problems. When I increase the number of columns it gives
a "Concurrency violation: the UpdateCommand affected 0 records."
exception. Is this some because of some limitation or am I making an
error in the code?


The table contains 4555 records


public void SaveDataFromGrid(DataSet dsName, string dtName, string
selectCommand)


{

//DGM 20040109
//This code works only for single table updates.


//Create new Connection
OdbcConnection dbConnection = new OdbcConnection(strConnectionString);


//Set the sql command that will fill the dataset
OdbcCommand sqlCommand = new OdbcCommand(selectCommand, dbConnection);
sqlCommand.CommandTimeout = 30;


//Set the sql command that will update the dataset


//Set the data adapter
OdbcDataAdapter dbDataAdapter = new OdbcDataAdapter();
OdbcCommandBuilder cb = new OdbcCommandBuilder(dbDataAdapter);
dbDataAdapter.SelectCommand = sqlCommand;


//Open the connection
dbConnection.Open();


//Fill the dataset with the data
dbDataAdapter.Update(dsName, dtName);


//Close the database connection
dbConnection.Close();
//return true;



}


Thanks in advance for the help

 

[David Sceppa]

This might be a driver-specific issue. If you could provide a little
more information about your specific scenario (back-end, ODBC driver, etc.)
someone may be able to offer some suggestions.

I've enclused the code I used to successfully submit an update against
a SQL Server table with up to 1024 columns using an OdbcDataAdapter and an
OdbcCommandBuilder.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.


string strConn = ...;
OdbcConnection cn = new OdbcConnection(strConn);
cn.Open();

int intNumColumns = 200;
PrepDb(cn, intNumColumns);
RunTest(cn);

static void RunTest(OdbcConnection Connection)
{
if (Connection.State != ConnectionState.Open)
Connection.Open();

string strSQL = "SELECT * FROM ManyColumns";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, Connection);
DataTable tbl = new DataTable();
da.Fill(tbl);
tbl.Rows[0]["Column1"] = -1;
OdbcCommandBuilder cb = new OdbcCommandBuilder(da);
try
{
da.Update(tbl);
Console.WriteLine("Success!");
}
catch (Exception ex)
{
Console.WriteLine("Failure!");
Console.WriteLine(ex.ToString());
}
}

static void PrepDb(OdbcConnection Connection, int NumColumns)
{
if (Connection.State != ConnectionState.Open)
Connection.Open();

OdbcCommand cmd = Connection.CreateCommand();
cmd.CommandText = "DROP TABLE ManyColumns";
try {cmd.ExecuteNonQuery();} catch{}

StringBuilder sbSql;
sbSql = new StringBuilder("CREATE TABLE ManyColumns(ID int PRIMARY KEY");
for (int intCounter = 1; intCounter < NumColumns; intCounter++)
sbSql.Append(string.Format(", Column{0} int", intCounter.ToString()));
sbSql.Append(")");
cmd.CommandText = sbSql.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Attempt to create table failed!");
Console.WriteLine(ex.ToString());
throw ex;
}

sbSql = new StringBuilder("INSERT INTO ManyColumns (ID");
for (int intCounter = 1; intCounter < NumColumns; intCounter++)
sbSql.Append(string.Format(", Column{0}", intCounter.ToString()));
sbSql.Append(") VALUES (0");
for (int intCounter = 1; intCounter < NumColumns; intCounter++)
sbSql.Append(", 0");
sbSql.Append(")");
cmd.CommandText = sbSql.ToString();
cmd.ExecuteNonQuery();
}

 

[citytroop]

David hi,
thanks for the reply. the back end is mysql and i am using the mysql
odbc driver 3.51. I will try the code you sent me but if anyone reads
the message and has any suggestions I would be gratefull.