DataAdapter.Fill problem

[Sébastien]

Here is my code :

oldOleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " &
strTable, strConnection)

dttDataTable = New DataTable(strTable)

oldOleDbDataAdapter.FillSchema(dttDataTable, SchemaType.Mapped)
oldOleDbDataAdapter.Fill(dttDataTable)

dtsDataSet.Tables.Add(dttDataTable)

Here is my problem :

If I have 0 rows in one of my table before I start my software, then add a
new Row, then I modify that same row, I get a concurrency exception. But if
I had 1 or more rows before I start my application, there is no problem. I
got a way to bypass it. After the first add in my Table, I do this :

oldOleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM " &
strTable, strConnection)

dttDataTable = New DataTable(strTable)

oldOleDbDataAdapter.FillSchema(dttDataTable, SchemaType.Mapped)
oldOleDbDataAdapter.Fill(dttDataTable)

dtsDataSet.Tables.Remove(strTable)
dtsDataSet.Tables.Add(dttDataTable)

I don't need to do it after the second, third, etc... row is inserted. That
is very ugly code. I would like to know why it work only when I do
oldOleDbDataAdapter.Fill and there is data in the DataBase. What does
oldOleDbDataAdapter.Fill do so different when there is no data in the
DataBase ?

Thank you for your help.

 

[Scott M.]

Don't know if this is your problem or not, but you don't need .FillSchema if
you are calling .Fill as well.

 

[Sébastien]

It seems I have to when I fill a new DataTable with no columns. Elseway I
have 0 columns and it throw an exception.

 

[Scott M.]

Instead of filling the DataTable and then adding it to the DataSet, why not
just fill the DataSet directly:

oldOleDbDataAdapter.Fill(dtsDataSet, dttDataTable)

This works for me even when the original table has no data in it.

 

[Sébastien]

How can you get your primary keys in your DataSet then ? I am not sure, but
I think FillShema is very important to get all the columns informations.

 

[Scott M.]

FillSchema is used when you want to create the identical structure of an
existing table, but without any data. Fill is used to get the structure and
the data (if any). You do not need both. You wil have access to whatever
columns your SELECT statement brings back. If you ask for the primary key
field from the DB, then you will have it as a column in your DataTable.

From MSDN:

Remarks:
The Fill method retrieves the data from the data source using a SELECT
statement. The IDbConnection object associated with the select command must
be valid, but it does not need to be open. If the IDbConnection is closed
before Fill is called, it is opened to retrieve data, then closed. If the
connection is open before Fill is called, it remains open.

If a command does not return any rows, no tables are added to the DataSet,
and no exception is raised.

 

[=?iso-8859-1?Q?S=E9bastien?=]

Sorry to not beleive you but here is an example from Microsoft. I tryed without the FillSchema in my application and it give me error messages. So I need the FillSchema function (I use VB.NET 2003).

Here is the sample :

Public Shared Function GetCustomerData(dataSetName As String) As DataSet

Dim ds As DataSet = New DataSet(dataSetName)

Dim conn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind")

Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName, ContactName FROM Customers", conn)

conn.Open()

da.FillSchema(ds, SchemaType.Source, "Customers")

da.Fill(ds)

conn.Close()

Return ds
End Function


If you have MSDN 2003, here is the link :
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1036/cpref/html/frlrfSystemDataCommonDbDataAdapterClassFillSchemaTopic.htm

 

[Scott M.]

Well Sebastian, I don't know what else to tell you. I have been doing what
I've been suggesting to you (not using both Fill and FillSchema) for 3 years
now without problems. The fact that you get an exception isn't a bad thing.
You just have to know what kind of exception you got and handle it.

Good Luck!

 

[David Sceppa]

Sébastien,

Does the database generate any values (AutoIncrement,
timestamp, defaults, etc) when you successfully insert or modify
a row? If so, you should fetch that data after a successful
update if you may want to submit further changes to the DataRow.
If this sounds like the cause of your problem, someone should be
able to suggest a solution if you provide more information on
your database and the types of server-generated values involved.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Cor]

Hi Sebastian,

I do not know if you already have corrected this but in your first message
you write this and I did see nowhere in the thread a correction (it can be I
missed it)

There are possibilities with the datatable also but than you had connected
it to the dataset and that I do not see. And this is standard see for it
your sample.
This has to be (see your sample)
dttDataset = new Dataset
"tablename")
Normaly you do not need this an alternative for this is but if you do key
operations you can try it.
I would start with skipping it.
I never use it except if I want to fill an empty dataset.
for what you say I use
da.MissingSchemaAction = MissingSchemaAction.AddWithKey

My idea is, try this first,

Cor

 

[Cor]

Hi Sebastian,

Seeing it over I see at the end you connect it to the dataset, but still I
would change it the way I suggest which is conform the sample.

To repeat it because i see my message became trash reading the thread over
and over again.

dim ds as new dataset
dim da as new dataadapter("sqlString", connection)
da.fill(ds)

That is simple what you need,

Will you use an empty dataset while the database is already filled

da.fillschema(ds, schemaparamaters,tablename)

But you cannot start filling things in the datatable, when you have not done
one of the things above.

I hope this helps?

Cor

 

[Sébastien]

My database generate AutoIncrement value in each table that cause me the
concurrency exception. So I guess you're right. The problem happen when I
start with an empty table, I start my application, I add something a new row
(the first one) in my DataTable inside my DataSet, then I try to update that
row. It wont happen if my table was having one or more rows before I start
my application.

So as you propose, I would like to know how to fetch that data. I use SQL
Server with AutoIncrement numeric fields as primary key for each table that
cause me that trouble (the one with Numeric field instead AutoIncrement work
fine).

Thank you for your help. I wish someone can help me.

 

[Ravichandran J.V.]

DataAdapter.Fill(DataSet Object) is absolutely okay. And moreover, if
your are terribly concerned about the schema of the table mappings, the
dataset object is supposed to retain the relationship, constraints and
the schema so, you may not have to worry after all.

with regards,


J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- http://www.411asp.net/func/search?
qry=Ravichandran+J.V.&cob=aspnetpro
- http://www.southasianoutlook.com
- http://www.MSDNAA.Net
- http://www.csharphelp.com
- http://www.poetry.com/Publications/
display.asp?ID=P3966388&BN=999&PN=2
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com