BUG: CommandBuilder updates do not succeed because of non-ANSI syntax (326602)

SYMPTOMS

When you use the System.Data.OleDb.OleDbCommandBuilder class in OLE DB .NET Provider to automatically generate INSERT, DELETE, or UPDATE statements in an application, the application may not update the data with some OLE DB providers. The error message that you receive depends on which OLE DB provider that you use, but the message indicates that the statement that is used has incorrect syntax. For example, the error message that you receive from the IBM AS400 OLE DB Provider is as follows:

System.Data.OleDb.OleDbException: SQL0417: Combination of parameter markers not valid. Cause . . . . . : The statement string specified as the object of a PREPARE statement contains a predicate or expression where parameter markers have been used as operands of the same operator. The following restrictions apply to the use of parameter markers: -- Both the operands in a predicate cannot be parameter markers. For example, specifying predicates of the form: ? = ? or ? = ( SELECT ? FROM x ) are not valid. -- Both the operands in a expression cannot be parameter markers. For example, specifying an expression of the form: ? + ? is not valid. -- At least one of the operands in the BETWEEN predicate cannot be a parameter marker. For example, specifying the predicate of the form: ? BETWEEN ? and ? is not valid. -- At least, one of the operands of the IN predicate must not be a parameter marker. For example, specifying the predicate of the form: ? IN (?, ?, ?) is not valid. Recovery . . . : Correct the statement so that all operands of the predicate or expression are not parameter markers. Try the request again.

The following OLE DB providers do not update because of incorrect syntax:

IBM AS/400 OLE DB Provider
Informix OLE DB Provider

MORE INFORMATION

Steps to Reproduce the Behavior

The following Microsoft Visual C# sample code demonstrates the problem:

DataSet ds = new DataSet();
String cnStr = "Provider=IBMDA400.DataSource.1;Password=PWD;Persist Security Info=True;User ID=USER;Data Source=DataSource";
OleDbConnection cn = new OleDbConnection(cnStr);
OleDbDataAdapter da = new OleDbDataAdapter("select id,stringcol from schema.table1", cn);
OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
OleDbCommand insert = new OleDbCommand("", cn);
OleDbCommand deleteAll = new OleDbCommand("DELETE FROM schema.table1", cn);
int j;
try 
{
	cn.Open();
	deleteAll.ExecuteNonQuery();
	for (j = 0; j<4; j++)
	{
		insert.CommandText = "INSERT INTO schema.table1 (id,stringcol) VALUES (" + j.ToString() + ",'string" + j.ToString() + "')";
		if ( j==1)
		{
			insert.CommandText = "INSERT INTO schema.table1 (id) VALUES (" + j.ToString() + ")";
		}
		insert.ExecuteNonQuery();
	}


	da.FillSchema(ds, SchemaType.Source, "table1");
	da.Fill(ds, "table1");
	OleDbCommand update = builder.GetUpdateCommand();

	ds.Tables[0].Rows[0]["stringcol"] = "New Value";
	ds.Tables[0].Rows[1]["stringcol"] = "New Value2";
	int i = da.Update(ds, "table1"); 
}
catch(Exception ex)
{
	MessageBox.Show(ex.ToString());
}

The table that is used for this code has the following format:

CREATE TABLE table1
(
id int,
stringcol varchar(20),
PRIMARY KEY(id)
)

Workaround

Use one of the following methods to work around the problem:

Method 1: Do not use the CommandBuilder to automatically generate INSERT, DELETE, or UPDATE commands. Generate the commands explicitly instead.
Method 2: Modify the command object that is generated by the CommandBuilder to modify the text and the parameters of the command.

The following code demonstrates one way to modify the command object that is generated by the CommandBuilder to update successfully:

DataSet ds = new DataSet();
String cnStr = "Provider=IBMDA400.DataSource.1;Password=PWD;Persist Security Info=True;User ID=USER;Data Source=DataSource";

OleDbConnection cn = new OleDbConnection(cnStr);
OleDbDataAdapter da = new OleDbDataAdapter("select id,stringcol from schema.table1", cn);
OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
OleDbCommand insert = new OleDbCommand("", cn);
OleDbCommand deleteAll = new OleDbCommand("DELETE FROM schema.table1", cn);
int j;
try 
{
	cn.Open();
	deleteAll.ExecuteNonQuery();
	for (j = 0; j<4; j++)
	{
		insert.CommandText = "INSERT INTO schema.table1 (id,stringcol) VALUES (" + j.ToString() + ",'string" + j.ToString() + "')";
		if ( j==1)
		{
			insert.CommandText = "INSERT INTO schema.table1 (id) VALUES (" + j.ToString() + ")";
		}
		insert.ExecuteNonQuery();
	}

	da.FillSchema(ds, SchemaType.Source, "table1");
	da.Fill(ds, "table1");
	OleDbCommand update = builder.GetUpdateCommand();
				
	update.CommandText = 
		"UPDATE schema.table1 SET ID = ? , STRINGCOL = ? WHERE" +
		"( (ID = ?) AND ( ( (?=1) AND STRINGCOL IS NULL) OR (STRINGCOL = ?)))";

	update.Connection = cn;

	OleDbParameter p = update.Parameters[3];
	
	p.DbType = DbType.Int32;
	p.OleDbType = OleDbType.Integer;
	p.SourceColumn = "";

	OleDbDataAdapter da2 = new OleDbDataAdapter();
	da2.UpdateCommand = update;
	da2.RowUpdating += new System.Data.OleDb.OleDbRowUpdatingEventHandler(this.MyOleDbRowUpdatingEventHandler);

	ds.Tables[0].Rows[0]["stringcol"] = "New Value";
	ds.Tables[0].Rows[1]["stringcol"] = "New Value2";
	int i = da2.Update(ds, "table1"); 
}
catch(Exception ex)
{
	MessageBox.Show(ex.ToString());
}

The command text is modified and replaces the following:

? IS NULL

with:

(?=1)

You must also modify the corresponding parameter to change its type to an Integer. Set the value to 0 if the original value of STRINGCOL was non-null, or to 1 if it was null:


public void MyOleDbRowUpdatingEventHandler(	object sender,OleDbRowUpdatingEventArgs e)
{
	OleDbDataAdapter da = (OleDbDataAdapter) sender;
	Object o  = e.Row["stringcol", DataRowVersion.Original];
	if (o is System.DBNull)
	{
		da.UpdateCommand.Parameters[3].Value = 1;
	}
	else
	{
		da.UpdateCommand.Parameters[3].Value = 0;
	}

}