By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,041 Members | 958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,041 IT Pros & Developers. It's quick & easy.

Q: Primary Key

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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
Nov 20 '05 #2

P: n/a
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

Nov 20 '05 #3

P: n/a
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
Nov 20 '05 #4

P: n/a
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


Nov 20 '05 #5

P: n/a
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

Nov 20 '05 #6

P: n/a
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
Nov 20 '05 #7

P: n/a
"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/
Nov 20 '05 #8

P: n/a
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!
Nov 20 '05 #9

P: n/a
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!

Nov 20 '05 #10

P: n/a
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/

Nov 20 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.