Strange Behavoir With OleDbDataAdapter

  • Thread starter Thread starter Chris Mayers
  • Start date Start date
C

Chris Mayers

Hi,

Hope someone can explain this...

OK, here is the simplest subset of my code that shows the problem:

I have a C# Method:

Private void UpdateDataTable(DataTable myDT)
{
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "AddComponentsForJobT";
comm.CommandType = CommandType.StoredProcedure;
OleDbConnection conn = new OleDbConnection(CONNECTION_STRING);
conn.Open();
comm.Connection = conn;
comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
= DataRowVersion.Current;
da.InsertCommand = comm;
da.Update(myDT);
}

An Access Query:

AddComponentsForJobT
==================
PARAMETERS pPartNo Text ( 255 );
INSERT INTO PropJobParts ( PartNo )
SELECT [pPartNo] AS Expr2;

If I pass into the above method a DataTable containing 3 rows with a
RowState of 'Added'

PartNo
=====
AAA
BB
C

The code runs successfully, but what finished up in my Access table is:

AAA
BBA
CBA

Anyone tell me what I'm doing wrong or missing out??? It seems like the
Automagic stuff inside OledbDataAdapter.Update that runs the Insert command
is not clearing the parameters between each subsequent insert.

Please!

Thanks,

Chris.
 
Chris - the Automagic stuff is really just calling the Update command you
specify/or insert or delete and filling in the params for you. Are you sure
that the values that are in the params are correct? Also, if that's the
exact query, where are the params listed in the INsert statement? Typically
a ? is used for OleDb so you may be passing in a hard coded Sql Statement
which would possibly be the problem.

Let me know if not though and we'll take it from there.
 
Hi,

Thanks for your response.

Not sure if I made it that clear, but the query I'm talking about is in an
MS-Access database. It is declared in the database, and referenced in my
code only by the query name, so I'm not passing any SQL from my code. (More
or less a stored procedure if it were SQL Server).
The parameter IS declared (in Access) as follows:
PARAMETERS pPartNo Text ( 255 );

So just to clarify it for me, the DataAdapter.Update method takes each row
in the DataTable, determines if it is a newly added row, a changed row or a
deleted row, then calls the appropriate query on it passing the values from
that DataRow to the parameters, the field from the DataRow that is passed to
each parameter being declared in the Parameter.Add method.

I have looked at the DataTable that is being passed to the DataAdapter, and
the values in the DataRows are what I would expect them to be, ie, in this
example, 'AAA', 'BB', 'C' etc.

I am about to try building a simple DataTable by hand, and passing that to
the Update method, just to rule out any strangeness in the data that I'm
using. I'll post back here with my findings...

Thanks,

Chris.





W.G. Ryan MVP said:
Chris - the Automagic stuff is really just calling the Update command you
specify/or insert or delete and filling in the params for you. Are you sure
that the values that are in the params are correct? Also, if that's the
exact query, where are the params listed in the INsert statement? Typically
a ? is used for OleDb so you may be passing in a hard coded Sql Statement
which would possibly be the problem.

Let me know if not though and we'll take it from there.

Chris Mayers said:
Hi,

Hope someone can explain this...

OK, here is the simplest subset of my code that shows the problem:

I have a C# Method:

Private void UpdateDataTable(DataTable myDT)
{
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "AddComponentsForJobT";
comm.CommandType = CommandType.StoredProcedure;
OleDbConnection conn = new OleDbConnection(CONNECTION_STRING);
conn.Open();
comm.Connection = conn;

comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
= DataRowVersion.Current;
da.InsertCommand = comm;
da.Update(myDT);
}

An Access Query:

AddComponentsForJobT
==================
PARAMETERS pPartNo Text ( 255 );
INSERT INTO PropJobParts ( PartNo )
SELECT [pPartNo] AS Expr2;

If I pass into the above method a DataTable containing 3 rows with a
RowState of 'Added'

PartNo
=====
AAA
BB
C

The code runs successfully, but what finished up in my Access table is:

AAA
BBA
CBA

Anyone tell me what I'm doing wrong or missing out??? It seems like the
Automagic stuff inside OledbDataAdapter.Update that runs the Insert
command
is not clearing the parameters between each subsequent insert.

Please!

Thanks,

Chris.
Click to expand...
Click to expand...
 
OK,

To rule out any strangeness that may be being caused by the DataTable I'm
trying to update, I'm creating my own just for this test:

So:

private void CreateDataTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("PartNo",Type.GetType("System.String"));
dt.Rows.Add(new string[]{"AAA"});
dt.Rows.Add(new string[]{"BB"});
dt.Rows.Add(new string[]{"C"});
UpdateDataTable(dt); // This is the method that is in my OP.
}

This still gives exactly the same problem, ie the data written into the
database is:
AAA
BBA
CBA

To (hopefully) reproduce the problem, you need a windows form with a button
that calls the above method,
plus the earlier method:

Private void UpdateDataTable(DataTable myDT)
{
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "AddComponentsForJobT";
comm.CommandType = CommandType.StoredProcedure;
OleDbConnection conn = new OleDbConnection(CONNECTION_STRING);
conn.Open();
comm.Connection = conn;
comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
= DataRowVersion.Current;
da.InsertCommand = comm;
da.Update(myDT);
}

plus an Access database containing a one table called 'PropJobParts' with a
single(text) field called 'PartNo'
And one update query called AddComponentsForJobT:

PARAMETERS pPartNo Text ( 255 );
INSERT INTO PropJobParts ( PartNo )
SELECT [pPartNo] AS Expr2;

If anyone has the time and the inclination to check this out, I would be
most grateful, I'm sure that I'm probably just missing somthing daft.

Thanks,

Chris.
Chris Mayers said:
Hi,

Hope someone can explain this...

OK, here is the simplest subset of my code that shows the problem:

I have a C# Method:

Private void UpdateDataTable(DataTable myDT)
{
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "AddComponentsForJobT";
comm.CommandType = CommandType.StoredProcedure;
OleDbConnection conn = new OleDbConnection(CONNECTION_STRING);
conn.Open();
comm.Connection = conn;

comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
= DataRowVersion.Current;
da.InsertCommand = comm;
da.Update(myDT);
}

An Access Query:

AddComponentsForJobT
==================
PARAMETERS pPartNo Text ( 255 );
INSERT INTO PropJobParts ( PartNo )
SELECT [pPartNo] AS Expr2;

If I pass into the above method a DataTable containing 3 rows with a
RowState of 'Added'

PartNo
=====
AAA
BB
C

The code runs successfully, but what finished up in my Access table is:

AAA
BBA
CBA

Anyone tell me what I'm doing wrong or missing out??? It seems like the
Automagic stuff inside OledbDataAdapter.Update that runs the Insert
command
is not clearing the parameters between each subsequent insert.

Please!

Thanks,

Chris.
Click to expand...
Click to expand...
Click to expand...
 
OK, looks like the fault is with MS-Access.

I've tried hard coding the query into the program, rather than calling an
Access Query,
ie I replaced:

comm.CommandText = "AddComponentsForJobT";
comm.CommandType = CommandType.StoredProcedure;

in the code with:

comm.CommandText = "insert into propjobparts (partno) values(?)";
comm.CommandType = CommandType.Text;

and it seems to work correctly.

OK,

To rule out any strangeness that may be being caused by the DataTable I'm
trying to update, I'm creating my own just for this test:

So:

private void CreateDataTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("PartNo",Type.GetType("System.String"));
dt.Rows.Add(new string[]{"AAA"});
dt.Rows.Add(new string[]{"BB"});
dt.Rows.Add(new string[]{"C"});
UpdateDataTable(dt); // This is the method that is in my OP.
}

This still gives exactly the same problem, ie the data written into the
database is:
AAA
BBA
CBA

To (hopefully) reproduce the problem, you need a windows form with a button
that calls the above method,
plus the earlier method:

Private void UpdateDataTable(DataTable myDT)
{
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "AddComponentsForJobT";
comm.CommandType = CommandType.StoredProcedure;
OleDbConnection conn = new OleDbConnection(CONNECTION_STRING);
conn.Open();
comm.Connection = conn;
comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
= DataRowVersion.Current;
da.InsertCommand = comm;
da.Update(myDT);
}

plus an Access database containing a one table called 'PropJobParts' with a
single(text) field called 'PartNo'
And one update query called AddComponentsForJobT:

PARAMETERS pPartNo Text ( 255 );
INSERT INTO PropJobParts ( PartNo )
SELECT [pPartNo] AS Expr2;

If anyone has the time and the inclination to check this out, I would be
most grateful, I'm sure that I'm probably just missing somthing daft.

Thanks,

Chris.
Hi,

Hope someone can explain this...

OK, here is the simplest subset of my code that shows the problem:

I have a C# Method:

Private void UpdateDataTable(DataTable myDT)
{
OleDbCommand comm = new OleDbCommand();
comm.CommandText = "AddComponentsForJobT";
comm.CommandType = CommandType.StoredProcedure;
OleDbConnection conn = new OleDbConnection(CONNECTION_STRING);
conn.Open();
comm.Connection = conn;
Click to expand...
comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
= DataRowVersion.Current;
da.InsertCommand = comm;
da.Update(myDT);
}

An Access Query:

AddComponentsForJobT
==================
PARAMETERS pPartNo Text ( 255 );
INSERT INTO PropJobParts ( PartNo )
SELECT [pPartNo] AS Expr2;

If I pass into the above method a DataTable containing 3 rows with a
RowState of 'Added'

PartNo
=====
AAA
BB
C

The code runs successfully, but what finished up in my Access table is:

AAA
BBA
CBA

Anyone tell me what I'm doing wrong or missing out??? It seems like the
Automagic stuff inside OledbDataAdapter.Update that runs the Insert
command
is not clearing the parameters between each subsequent insert.

Please!

Thanks,

Chris.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top