Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / .NET Framework / New Users / August 2005

Tip: Looking for answers? Try searching our database.

Strange Behavoir With OleDbDataAdapter

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Chris Mayers - 29 Jul 2005 09:56 GMT
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.

Signature

Remove Elvis's shoes to reply.

W.G. Ryan MVP - 29 Jul 2005 18:48 GMT
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,
>
[quoted text clipped - 52 lines]
>
> Chris.
Chris Mayers - 01 Aug 2005 09:21 GMT
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.

> 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
[quoted text clipped - 21 lines]
> >  conn.Open();
> >  comm.Connection = conn;

comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
> > =  DataRowVersion.Current;
> >  da.InsertCommand = comm;
[quoted text clipped - 34 lines]
> >
> > Chris.
Chris Mayers - 01 Aug 2005 09:58 GMT
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,
> > >
[quoted text clipped - 52 lines]
> > >
> > > Chris.
Chris Mayers - 01 Aug 2005 11:24 GMT
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,
>
[quoted text clipped - 31 lines]
>   conn.Open();
>   comm.Connection = conn;

comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
> =  DataRowVersion.Current;
>   da.InsertCommand = comm;
[quoted text clipped - 32 lines]
> > > >  conn.Open();
> > > >  comm.Connection = conn;

comm.Parameters.Add("pPartNo",OleDbType.VarChar,10,"PartNo").SourceVersion
> > > > =  DataRowVersion.Current;
> > > >  da.InsertCommand = comm;
[quoted text clipped - 36 lines]
> > > >
> > > > Chris.

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.