Strange Behavoir With OleDbDataAdapte r

[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.CommandTex t = "AddComponentsF orJobT";
comm.CommandTyp e = CommandType.Sto redProcedure;
OleDbConnection conn = new OleDbConnection (CONNECTION_STR ING);
conn.Open();
comm.Connection = conn;
comm.Parameters .Add("pPartNo", OleDbType.VarCh ar,10,"PartNo") .SourceVersion
= DataRowVersion. Current;
da.InsertComman d = comm;
da.Update(myDT) ;
}

An Access Query:

AddComponentsFo rJobT
=============== ===
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 OledbDataAdapte r.Update that runs the Insert command
is not clearing the parameters between each subsequent insert.

Please!

Thanks,

Chris.
--
Remove Elvis's shoes to reply.

[W.G. Ryan MVP]

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" <ch************ **@SUEDEYahoo.C om> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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.CommandTex t = "AddComponentsF orJobT";
comm.CommandTyp e = CommandType.Sto redProcedure;
OleDbConnection conn = new OleDbConnection (CONNECTION_STR ING);
conn.Open();
comm.Connection = conn;

comm.Parameters .Add("pPartNo", OleDbType.VarCh ar,10,"PartNo") .SourceVersion
= DataRowVersion. Current;
da.InsertComman d = comm;
da.Update(myDT) ;
}

An Access Query:

AddComponentsFo rJobT
=============== ===
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 OledbDataAdapte r.Update that runs the Insert
command
is not clearing the parameters between each subsequent insert.

Please!

Thanks,

Chris.
--
Remove Elvis's shoes to reply.

[Chris Mayers]

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.Upd ate 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" <Wi*********@no spam.gmail.com> wrote in message
news:um******** ******@tk2msftn gp13.phx.gbl...

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" <ch************ **@SUEDEYahoo.C om> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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.CommandTex t = "AddComponentsF orJobT";
comm.CommandTyp e = CommandType.Sto redProcedure;
OleDbConnection conn = new OleDbConnection (CONNECTION_STR ING);
conn.Open();
comm.Connection = conn;

comm.Parameters .Add("pPartNo", OleDbType.VarCh ar,10,"PartNo") .SourceVersion = DataRowVersion. Current;
da.InsertComman d = comm;
da.Update(myDT) ;
}

An Access Query:

AddComponentsFo rJobT
=============== ===
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 OledbDataAdapte r.Update that runs the Insert
command
is not clearing the parameters between each subsequent insert.

Please!

Thanks,

Chris.
--
Remove Elvis's shoes to reply.

[Chris Mayers]

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.G etType("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.CommandTex t = "AddComponentsF orJobT";
comm.CommandTyp e = CommandType.Sto redProcedure;
OleDbConnection conn = new OleDbConnection (CONNECTION_STR ING);
conn.Open();
comm.Connection = conn;
comm.Parameters .Add("pPartNo", OleDbType.VarCh ar,10,"PartNo") .SourceVersion
= DataRowVersion. Current;
da.InsertComman d = 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 AddComponentsFo rJobT:

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" <ch************ **@SUEDEYahoo.C om> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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.CommandTex t = "AddComponentsF orJobT";
comm.CommandTyp e = CommandType.Sto redProcedure;
OleDbConnection conn = new OleDbConnection (CONNECTION_STR ING);
conn.Open();
comm.Connection = conn;

comm.Parameters .Add("pPartNo", OleDbType.VarCh ar,10,"PartNo") .SourceVersion = DataRowVersion. Current;
da.InsertComman d = comm;
da.Update(myDT) ;
}

An Access Query:

AddComponentsFo rJobT
=============== ===
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 OledbDataAdapte r.Update that runs the Insert
command
is not clearing the parameters between each subsequent insert.

Please!

Thanks,

Chris.
--
Remove Elvis's shoes to reply.

[Chris Mayers]

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.CommandTex t = "AddComponentsF orJobT";
comm.CommandTyp e = CommandType.Sto redProcedure;

in the code with:

comm.CommandTex t = "insert into propjobparts (partno) values(?)";
comm.CommandTyp e = CommandType.Tex t;

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.G etType("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.CommandTex t = "AddComponentsF orJobT";
comm.CommandTyp e = CommandType.Sto redProcedure;
OleDbConnection conn = new OleDbConnection (CONNECTION_STR ING);
conn.Open();
comm.Connection = conn;
comm.Parameters .Add("pPartNo", OleDbType.VarCh ar,10,"PartNo") .SourceVersion = DataRowVersion. Current;
da.InsertComman d = 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 AddComponentsFo rJobT:

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" <ch************ **@SUEDEYahoo.C om> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..
> 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.CommandTex t = "AddComponentsF orJobT";
> comm.CommandTyp e = CommandType.Sto redProcedure;
> OleDbConnection conn = new OleDbConnection (CONNECTION_STR ING);
> conn.Open();
> comm.Connection = conn;
>
>

comm.Parameters .Add("pPartNo", OleDbType.VarCh ar,10,"PartNo") .SourceVersion

> = DataRowVersion. Current;
> da.InsertComman d = comm;
> da.Update(myDT) ;
> }
>
> An Access Query:
>
> AddComponentsFo rJobT
> =============== ===
> 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 OledbDataAdapte r.Update that runs the Insert
> command
> is not clearing the parameters between each subsequent insert.
>
> Please!
>
> Thanks,
>
> Chris.
>
>
> --
> Remove Elvis's shoes to reply.
>
>