Q: Primary Key

[Geoff Jones]

Hi

I have a problem with the values of a primary key in a table.

I am deleting all the rows of an existing data table in a database by using
code:

For Each x As DataRow In myDataSet.Tables(0)
x.Delete()
Next

myDataAdaptor.Update(myDataSet.Tables(0))

This works fine, HOWEVER, when I add more rows later on to this table, the
index ID does not start from 1, rather it appears that it is working from
the last value of the set of rows deleted earlier.

I guess there must be some command I'm missing to re-set the ID counter.

Can anybody help?

Thanks in advance

Geoff

[Cor Ligthert]

Geoff,

Never attach a question to a question, the Original Poster would think that
you are giving and answer.

A dataset is a disconnected so it just start adding autokeys from the latest
it had when you not are using the autoincrement seed.

The best is to set the seed to -1 and the increment as well.

http://msdn.microsoft.com/library/de...ementtopic.asp

I hope this helps?

Cor

[Geoff Jones]

Apologies Cor for posting in the above. Only after sending did I realise
that the first responder had not stayed in the same thread.

I'll have a look at your link.

Thanks

Geoff

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:eq****************@TK2MSFTNGP10.phx.gbl...

Geoff,

Never attach a question to a question, the Original Poster would think that you are giving and answer.

A dataset is a disconnected so it just start adding autokeys from the latest it had when you not are using the autoincrement seed.

The best is to set the seed to -1 and the increment as well.

http://msdn.microsoft.com/library/de...ementtopic.asp
I hope this helps?

Cor

[Geoff Jones]

Hi Cor

Err, I'm afraid I don't follow you:

"A dataset is a disconnected so it just starts adding autokeys from the
latest
it had when you not are using the autoincrement seed.

The best is to set the seed to -1 and the increment as well."

I understand that a dataset is disconnected. I'm deleting all the records in
the database itself by deleting the records in the dataset and then using
Update to transfer the changes to the database itself. I'm also, as a matter
of interest, deleting all the records in the DataSet afterwards using
RemoveAt.

However, as I said in the first post, when I add a record to the DataSet
(which in a DataGrid has the ID of 1 - which is what I expect) and then
Update it to the database, the ID is not 1! Instead, it is one more than the
number of records deleted initially. So, IMHO, the databases ID does not
know that the earlier records have been deleted.

Does this make sense?

Geoff

[Cor Ligthert]

Hi Geoff,

What you write is what it does to me as well. However, I did not investigage
it.

Do you have this simple code, with that you can see what is in your dataset
at any moment. (You need to have somewhere a textbox on your form otherwise
it is so difficult to read)

Dim sw As New System.IO.StringWriter
ds.WriteXml(sw, XmlWriteMode.WriteSchema)

Me.txtTekst.Text = sw.ToString

Cor

[Geoff Jones]

Hi Cor

Do you mean that you get the same thing?

Maybe the question I should be asking is how to delete all the entries in a
table of a database. Maybe it is the methods I am currently using that are
causing the problems?

Geoff

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:O$**************@TK2MSFTNGP09.phx.gbl...

Hi Geoff,

What you write is what it does to me as well. However, I did not investigage it.

Do you have this simple code, with that you can see what is in your dataset at any moment. (You need to have somewhere a textbox on your form otherwise it is so difficult to read)

Dim sw As New System.IO.StringWriter
ds.WriteXml(sw, XmlWriteMode.WriteSchema)

Me.txtTekst.Text = sw.ToString

Cor

[Cor Ligthert]

Hi Geoff,

I never had this as a problem, however did I not show you how to create a
table?

When I had this problem I would drop the table and create the table new.

http://msdn.microsoft.com/library/de...de-dz_7uud.asp

I hope we get something further?

Cor

[Lucas Tam]

"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in
news:41***********************@news.dial.pipex.com :

However, as I said in the first post, when I add a record to the
DataSet (which in a DataGrid has the ID of 1 - which is what I expect)
and then Update it to the database, the ID is not 1! Instead, it is
one more than the number of records deleted initially. So, IMHO, the
databases ID does not know that the earlier records have been deleted.

What database are you using?

If you're using SQL Server look here:
http://msdn.microsoft.com/library/de...l=/library/en-
us/tsqlref/ts_dbcc_5lv8.asp
Anyhow, why reseed? The PK number should not matter...

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[James R]

Geoff

If you are using SQL Server:

Try using Truncate Table. This clears all the records, and resets the
identity column to its seed value, which is usually 1. Truncate is
quicker than dropping a table and creating a new one, though the result
is the same.

To use Truncate Table use SQLCommand.ExecuteNonQuery with the
SQLCommand.CommandText = "Truncate Table <mytable>".

James

Cor wrote :

Hi Geoff,

I never had this as a problem, however did I not show you >how to create atable?

When I had this problem I would drop the table and create >the table new.
http://msdn.microsoft.com/library/default.asp?>url=/library/en-us/tsqlr ef/ts_de-dz_7uud.asp
I hope we get something further?

Cor

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Geoff Jones]

Hi James

Unfortunately, I'm using Access. Is there an equivalent?

Geoff

"James R" <an*******@devdex.com> wrote in message
news:ed**************@tk2msftngp13.phx.gbl...

Geoff

If you are using SQL Server:

Try using Truncate Table. This clears all the records, and resets the
identity column to its seed value, which is usually 1. Truncate is
quicker than dropping a table and creating a new one, though the result
is the same.

To use Truncate Table use SQLCommand.ExecuteNonQuery with the
SQLCommand.CommandText = "Truncate Table <mytable>".

James

Cor wrote :

Hi Geoff,

I never had this as a problem, however did I not show you >how to

create a

table?

When I had this problem I would drop the table and create >the table

new.

http://msdn.microsoft.com/library/default.asp?>url=/library/en-us/tsqlr

ef/ts_de-dz_7uud.asp

I hope we get something further?

Cor

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Geoff Jones]

Hi Lucas

I'm using Access.

Geoff

"Lucas Tam" <RE********@rogers.com> wrote in message
news:Xn***************************@140.99.99.130.. .

"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in
news:41***********************@news.dial.pipex.com :

However, as I said in the first post, when I add a record to the
DataSet (which in a DataGrid has the ID of 1 - which is what I expect)
and then Update it to the database, the ID is not 1! Instead, it is
one more than the number of records deleted initially. So, IMHO, the
databases ID does not know that the earlier records have been deleted.

What database are you using?

If you're using SQL Server look here:
http://msdn.microsoft.com/library/de...l=/library/en-
us/tsqlref/ts_dbcc_5lv8.asp
Anyhow, why reseed? The PK number should not matter...

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/