Member Avatar for Sevyt

Never had this problem before, and i am not sure how this happened.

I got a Mysql sql database for my project.
The id field is set as auto increment, which seems to function correctly.
But on the form in my text field and datagrid i see a negative number, while in the database its a postitive number.
I do not really plan to use the id field right now, but when i do it might cause some problems.

Anybody know why it would show as a negative number on the form(textbox/gridview) and as a positive in the actual database?

  • 2 Contributors
  • 1 Reply
  • 278 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by peter_budo

Dani AI

Generated

Most likely cause (and what happened here): ADO.NET / the typed DataSet assigns temporary negative keys to newly added rows on the client so they don’t collide with positive, server-generated AUTO_INCREMENT values. This is normal behavior — the designer/client side often sets an AutoIncrementSeed/AutoIncrementStep that produces -1, -2, -3… for unsaved rows. ’s form showing a negative id while the DB shows a positive value fits that pattern. (stackoverflow.com)

How to make the client show the real DB id after insert (practical fix): keep the negative temporary ids while editing, but ensure the DataAdapter/InsertCommand returns the generated id and updates the DataRow after Update(). With Connector/NET the common approach is to clone the generated InsertCommand, append “; SELECT LAST_INSERT_ID() AS id”, set UpdatedRowSource = FirstReturnedRecord, then call Update — the DataRow’s id will be replaced with the DB value. Example (VB.NET, sketch):

Dim cb = New MySqlCommandBuilder(adapter)
Dim insertCmd As MySqlCommand = cb.GetInsertCommand().Clone()
insertCmd.CommandText &= "; SELECT LAST_INSERT_ID() AS id"
insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
adapter.InsertCommand = insertCmd
cb.DataAdapter = Nothing
adapter.Update(myTable)

This pattern (or enabling the TableAdapter “Refresh the DataSet” option in VS) makes the negative placeholder disappear and the positive DB id appear. (dev.mysql.com)

Another, less common cause: a type / range mismatch. If the MySQL column is UNSIGNED and the value exceeds a signed Int32 range, reading it into a signed Integer can look “negative” on the client. In that case change the client DataColumn type to Long/ULong (or match the DB type) before filling. Check MySQL INT/BIGINT ranges and VB.NET numeric ranges when deciding which .NET type to use. (dev.mysql.com)

Quick checklist to resolve this thread:

  • Confirm whether the negative id is on an unsaved (Added) DataRow.
  • Inspect the DataColumn.AutoIncrementSeed / AutoIncrementStep in the typed dataset.
  • Use the InsertCommand + LAST_INSERT_ID() trick or enable TableAdapter refresh so DB ids replace placeholders.
  • If large ids are suspected, change the column DataType to Long/ULong on the client side.
Member Avatar for peter_budo

Even if it was some mistake on your part, you should mentioned what was wrong. You never know who will repeat same mistake as you and will search for help

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.