Problem with SqlCeCommandBuilder

[Tomer]

Hi,

I've created a table in run time that has a primary key composed of two
fields.

The table creation sql is this:
"Create table CertDetail (AIndex int, Barcode nvarchar(20), Primary Key
(AIndex,Barcode))"



I'm using a SqlCeCommandBuilder to create the DataAdapter sql commands
(insert, delete, update)

CertHeadAdapter = new SqlCeDataAdapter("Select * from CertHead",Conn);

CmdBuilder = new SqlCeCommandBuilder(CertDetailAdapter);

CertDetailAdapter.InsertCommand = CmdBuilder.GetInsertCommand();

CertDetailAdapter.UpdateCommand = CmdBuilder.GetUpdateCommand();

CertDetailAdapter.DeleteCommand = CmdBuilder.GetDeleteCommand();

CmdBuilder.Dispose();

Then I use a DataAdapter to fill a DataSet with the table's Schema and it's
Data.

CertDetailAdapter.FillSchema(ds,SchemaType.Source,"CertDetail"); // needed
for find function
TblCertDetail = ds.Tables["CertDetail"];

When I'm looking at the DataAdapter's Delete Command, I see that its delete
according to only one key (the first) with the other.

The Sql command goes like this:

Delete from CertDetail where AIndex=?

where it should be:

Delete from CertDetail where AIndex=? and Barcode=?


This Issue is causing me problems when I try to delete a row from the
DataTable. What happens is that the delete operation deletes all the rows
that have the AIndex value of the row I'm trying to delete.

Whats going on here? Am I doing something wrong or Am I using it the wrong
way?

Tomer.

 

[Alex Feinman [MVP]]

So far whatever I've done I was not able to force a SqlCeCommandBuilder to
read the primary key information from the table. Check the table.PrimaryKey
after the call to FillSchema. I bet it has only one column. If that's the
case, try this:


CertHeadAdapter = new SqlCeDataAdapter("Select * from CertHead",Conn);

CertDetailAdapter.Fill(ds, 0, 0,"CertDetail"); // Actually does not fill the
table, but extracts schema
TblCertDetail = ds.Tables["CertDetail"];

TblCertDetail.PrimaryKey = new DataColumn[] ({ TblCertDetail.Columns[0],
TblCertDetail.Columns[1] });

CmdBuilder = new SqlCeCommandBuilder(CertDetailAdapter);

CertDetailAdapter.InsertCommand = CmdBuilder.GetInsertCommand();

CertDetailAdapter.UpdateCommand = CmdBuilder.GetUpdateCommand();

CertDetailAdapter.DeleteCommand = CmdBuilder.GetDeleteCommand();

CmdBuilder.Dispose();

 

[Tomer]

Hi,

I'm afraid that your suggestion didn't work, the Delete command still
remains the same.
I've also checked the FillSchema function and saw that it fills the
PrimaryKey propery currectly (with two keys).
So that is very wierd, could this mean that the CommandBuilder does work on
this case?

Tomer.

So far whatever I've done I was not able to force a SqlCeCommandBuilder to
read the primary key information from the table. Check the table.PrimaryKey
after the call to FillSchema. I bet it has only one column. If that's the
case, try this:


CertHeadAdapter = new SqlCeDataAdapter("Select * from CertHead",Conn);

CertDetailAdapter.Fill(ds, 0, 0,"CertDetail"); // Actually does not fill the
table, but extracts schema
TblCertDetail = ds.Tables["CertDetail"];

TblCertDetail.PrimaryKey = new DataColumn[] ({ TblCertDetail.Columns[0],
TblCertDetail.Columns[1] });

CmdBuilder = new SqlCeCommandBuilder(CertDetailAdapter);

CertDetailAdapter.InsertCommand = CmdBuilder.GetInsertCommand();

CertDetailAdapter.UpdateCommand = CmdBuilder.GetUpdateCommand();

CertDetailAdapter.DeleteCommand = CmdBuilder.GetDeleteCommand();

CmdBuilder.Dispose();


--
Alex Feinman
---
Visit http://www.opennetcf.org

Hi,

I've created a table in run time that has a primary key composed of two
fields.

The table creation sql is this:
"Create table CertDetail (AIndex int, Barcode nvarchar(20), Primary Key
(AIndex,Barcode))"



I'm using a SqlCeCommandBuilder to create the DataAdapter sql commands
(insert, delete, update)

CertHeadAdapter = new SqlCeDataAdapter("Select * from CertHead",Conn);

CmdBuilder = new SqlCeCommandBuilder(CertDetailAdapter);

CertDetailAdapter.InsertCommand = CmdBuilder.GetInsertCommand();

CertDetailAdapter.UpdateCommand = CmdBuilder.GetUpdateCommand();

CertDetailAdapter.DeleteCommand = CmdBuilder.GetDeleteCommand();

CmdBuilder.Dispose();

Then I use a DataAdapter to fill a DataSet with the table's Schema and
it's
Data.

CertDetailAdapter.FillSchema(ds,SchemaType.Source,"CertDetail"); // needed
for find function
TblCertDetail = ds.Tables["CertDetail"];

When I'm looking at the DataAdapter's Delete Command, I see that its
delete
according to only one key (the first) with the other.

The Sql command goes like this:

Delete from CertDetail where AIndex=?

where it should be:

Delete from CertDetail where AIndex=? and Barcode=?


This Issue is causing me problems when I try to delete a row from the
DataTable. What happens is that the delete operation deletes all the rows
that have the AIndex value of the row I'm trying to delete.

Whats going on here? Am I doing something wrong or Am I using it the
wrong
way?

Tomer.

 

[Swin]

Hi,

Use my function to make Commands will solve the problem.

C# code:

private System.Data.SqlServerCe.SqlCeDataAdapter SetCommandBuilder(
System.Data.SqlServerCe.SqlCeConnection cn, string sSQL )
{
System.Data.SqlServerCe.SqlCeDataAdapter da = new
System.Data.SqlServerCe.SqlCeDataAdapter( sSQL, cn );
System.Data.SqlServerCe.SqlCeCommandBuilder cb = new
System.Data.SqlServerCe.SqlCeCommandBuilder( da );
da.DeleteCommand = cb.GetDeleteCommand();
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
cb.Dispose();

//Check DeleteCommand Parameters and Fix It
int iValue = da.UpdateCommand.Parameters.Count - (
da.InsertCommand.Parameters.Count + da.DeleteCommand.Parameters.Count );
if ( ( iValue > 0 )
&& ( da.UpdateCommand.Parameters[da.UpdateCommand.Parameters.Count -
iValue - 1].SourceColumn ==
da.DeleteCommand.Parameters[da.DeleteCommand.Parameters.Count -
iValue].SourceColumn ) )
{
da.DeleteCommand.CommandText =
da.DeleteCommand.CommandText.Substring( 0,
da.DeleteCommand.CommandText.IndexOf( "WHERE" ) )
+ da.UpdateCommand.CommandText.Substring(
da.UpdateCommand.CommandText.IndexOf( "WHERE" ) );

iValue = da.UpdateCommand.Parameters.Count -
da.InsertCommand.Parameters.Count;

//MessageBox.Show( iValue.ToString() + "\n" +
da.DeleteCommand.CommandText );

da.DeleteCommand.Parameters.Clear();
for ( int i=0;i<iValue;i++ )
{
System.Data.SqlServerCe.SqlCeParameter p =
da.UpdateCommand.Parameters[da.UpdateCommand.Parameters.Count - iValue + i];
da.DeleteCommand.Parameters.Add( p.ParameterName, p.SqlDbType, p.Size,
p.SourceColumn );
}//endfor
}//endif

return da;
}

Hi,

I'm afraid that your suggestion didn't work, the Delete command still
remains the same.
I've also checked the FillSchema function and saw that it fills the
PrimaryKey propery currectly (with two keys).
So that is very wierd, could this mean that the CommandBuilder does work
on
this case?

Tomer.

So far whatever I've done I was not able to force a SqlCeCommandBuilder
to
read the primary key information from the table. Check the table.PrimaryKey
after the call to FillSchema. I bet it has only one column. If that's the
case, try this:


CertHeadAdapter = new SqlCeDataAdapter("Select * from CertHead",Conn);

CertDetailAdapter.Fill(ds, 0, 0,"CertDetail"); // Actually does not fill the
table, but extracts schema
TblCertDetail = ds.Tables["CertDetail"];

TblCertDetail.PrimaryKey = new DataColumn[] ({ TblCertDetail.Columns[0],
TblCertDetail.Columns[1] });

CmdBuilder = new SqlCeCommandBuilder(CertDetailAdapter);

CertDetailAdapter.InsertCommand = CmdBuilder.GetInsertCommand();

CertDetailAdapter.UpdateCommand = CmdBuilder.GetUpdateCommand();

CertDetailAdapter.DeleteCommand = CmdBuilder.GetDeleteCommand();

CmdBuilder.Dispose();


--
Alex Feinman
---
Visit http://www.opennetcf.org

Hi,

I've created a table in run time that has a primary key composed of two
fields.

The table creation sql is this:
"Create table CertDetail (AIndex int, Barcode nvarchar(20), Primary Key
(AIndex,Barcode))"



I'm using a SqlCeCommandBuilder to create the DataAdapter sql commands
(insert, delete, update)

CertHeadAdapter = new SqlCeDataAdapter("Select * from CertHead",Conn);

CmdBuilder = new SqlCeCommandBuilder(CertDetailAdapter);

CertDetailAdapter.InsertCommand = CmdBuilder.GetInsertCommand();

CertDetailAdapter.UpdateCommand = CmdBuilder.GetUpdateCommand();

CertDetailAdapter.DeleteCommand = CmdBuilder.GetDeleteCommand();

CmdBuilder.Dispose();

Then I use a DataAdapter to fill a DataSet with the table's Schema and
it's
Data.

CertDetailAdapter.FillSchema(ds,SchemaType.Source,"CertDetail"); // needed
for find function
TblCertDetail = ds.Tables["CertDetail"];

When I'm looking at the DataAdapter's Delete Command, I see that its
delete
according to only one key (the first) with the other.

The Sql command goes like this:

Delete from CertDetail where AIndex=?

where it should be:

Delete from CertDetail where AIndex=? and Barcode=?


This Issue is causing me problems when I try to delete a row from the
DataTable. What happens is that the delete operation deletes all the rows
that have the AIndex value of the row I'm trying to delete.

Whats going on here? Am I doing something wrong or Am I using it the
wrong
way?

Tomer.