471,596 Members | 1,218 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,596 software developers and data experts.

Strange Behavoir With OleDbDataAdapter

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.
--
Remove Elvis's shoes to reply.
Jul 29 '05 #1
4 1504
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.Com> wrote in message
news:%2****************@TK2MSFTNGP09.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.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.
--
Remove Elvis's shoes to reply.

Jul 29 '05 #2
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" <Wi*********@nospam.gmail.com> wrote in message
news:um**************@tk2msftngp13.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.Com> wrote in message
news:%2****************@TK2MSFTNGP09.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.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.
--
Remove Elvis's shoes to reply.


Aug 1 '05 #3
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.Strin g"));
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" <ch**************@SUEDEYahoo.Com> wrote in message
news:%2****************@TK2MSFTNGP09.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.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.
--
Remove Elvis's shoes to reply.



Aug 1 '05 #4
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.Strin g"));
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" <ch**************@SUEDEYahoo.Com> wrote in message
news:%2****************@TK2MSFTNGP09.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.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.
>
>
> --
> Remove Elvis's shoes to reply.
>
>



Aug 1 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Markus Franz | last post: by
2 posts views Thread by Eyvind W Axelsen | last post: by
24 posts views Thread by LineVoltageHalogen | last post: by
2 posts views Thread by Son Ha | last post: by
4 posts views Thread by shachar | last post: by
4 posts views Thread by Chris Mayers | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.