asked on

MySQLDataAdapter doesn't set AutoIncrement property in Datatable

Hi Experts,

I have a MySql table with a column set to AutoIncrement=True and NotNull=True. Type is Int(10) Unsigned. My Application needs to add records to an in-memory datatable and them use the adapter to save the changes.

When I create a MySQLDataAdapter and fill my table it is supposed to set the DataColumn.AutoIncrement to true after reading the schema from the database. It is not. I tried many ways to fill the data and none of them are working.

Public Adapter As MySqlDataAdapter
Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
Adapter.Fill(MyDataSet, "MyTable")

Open in new window

I also tried

Public Adapter As MySqlDataAdapter
Adapter.FillSchema(MyDataSet, SchemaType.Source, "MyTable")
Adapter.Fill(MyDataSet, "MyTable")

Open in new window

as well as SchemaType.Mapped.

In every case my Datacolumn.Autoincrement=False.
Is there something I am doing wrong?
I am using MySQL 5.5.39-MariaDB and VB.NET 4.0

Now I could work around this by not using the autoincrement and using a GUID as a primary key instead, but I'd like to try the "normal" way of working with the table.

ASKER CERTIFIED SOLUTION

Lokesh B R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MasterWoodsman

ASKER

That Worked! I had to make sure that I loaded the schema without data before manually setting the column's autoincrement property, and then actually load the data.

My full code is

Public Adapter As MySqlDataAdapter
Adapter.FillSchema(MyDataSet, SchemaType.Source, "MyTable")
Adapter.MissingSchemaAction = MissingSchemaAction.Add
dim dc as DataColumn = ds.Tables("MyTable").Columns("ColumnWithAutoIncrement")
dc.AutoIncrement = True
Adapter.Fill(MyDataSet, "MyTable")

Open in new window

Thanks for the help!