Q: Primary Key

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

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
 
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
 
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
 
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
 
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
 
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.
Click to expand...

What database are you using?

If you're using SQL Server look here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_dbcc_5lv8.asp


Anyhow, why reseed? The PK number should not matter...
 
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 :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Table DataSet 7
Why doesn't changing the position in a table change the position of the DatGridView that's bound to 2
Search DataRow Array 5
Highlight a column in a datagrid 1
OleDbtransaction? 1
Loading Large Lists 3
Q: primary key 9
Primary key Usage 1

Back
Top