473,390 Members | 1,042 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 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 1572
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Markus Franz | last post by:
Hi. Today I created a script called load.py for using at the command line written in Python 2.3. This script should load as many websites as given on the comand line and print them with a...
1
by: jtsree | last post by:
I am Using (Windows XP) Visual Studio.net 2003 professional edition working on VB.net language. I am bulding a very very simple project in VB.net where i connect to Access Database by dragging...
2
by: Eyvind W Axelsen | last post by:
Hi. I am creating an application (primarily in C#), with MDI child windows. What I would like to do, is to override the default behavoir that makes a newly createad child form maximized if the...
24
by: LineVoltageHalogen | last post by:
Greetings All, I was hoping that someone out there has run into this issue before and can shed some light on it for me. I have a stored procedure that essentially does a mini ETL from a source...
2
by: Son Ha | last post by:
I want to copy some record from a Access database to another Access DB. My code as follow but not working. The destAdapter.Update() return 0 record affected. Tell me what's wrong in my code? ...
1
by: Bennett Haselton | last post by:
Suppose I add a new row to a table in a dataset, and then I use an OleDbDataAdapter to add that new row to a SQL Server database using OleDbDataAdapter.Update(), as in the following code: ...
4
by: shachar | last post by:
hi all(i tried yesterday but it didnt work). i wrote this code and i get an error msg: Private Pr_MyCon As System.Data.OleDb.OleDbConnection Private Pr_DataSet As DataSet Private Pr_DataAdapter...
1
by: RML | last post by:
Hi everyone, I am using VB.NET 2003 and an OleDBDataAdapter to update an Access table's DateTime field. The field's format is set to "General Date" (ie: 11/24/2004 8:00:00 AM). The problem is...
4
by: Chris Mayers | last post by:
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) {
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.