Hi,

I have a datagrid where users can put in their data directly.
I also have a "save" button so that the user can save his or her changes.

But i have a problem with my primary key!
I made a column for the primary key.
I dont want the user to be able to change the primary key cells. So i want the application to fill the primary key cells automaticly.
But when i made the key to autoincrement = true, then i get an error becouse most of the time the number that the application give to the cell (by means of the autoincrement) is already used in another primary key cell!

I could however use another column as primary key. But when i do this, the user won't be able to change the cells in that column becouse the application won't let you change a primary key if it has already a number in it.

You see my problem? I really have no idea what the sollution may be.
I would be VERY greatfull to the one who can help me

Thanks,

VB Newbie

Not totally sure I get what you are describing.... But if you have a field set as an Primary Key in your DB and it is a 'Identity' or 'AutoIncrementing' field, you do not put that field in the 'INSERT' statement the DB will do that itself.... likewise it should be excluded for any 'UPDATE' statements. Are you creating these SQL statements yourself, or is there an object doing that work? I don't work much with the UI objects that do create the statements, but I would imagine there either an option to exclude 'identity' fields, or you could manually edit the statement it created to remvoe them before running it.

Second, if necessary you can tell SQL Server, if that is what you are using, to turn the constraint for Identity fields off if you are importing data into it in a block... ALthough, obviously you would have to verify the values are not duplicated yourself.

~rlc

: Not totally sure I get what you are describing.... But if you have a field set as an Primary Key in your DB and it is a 'Identity' or 'AutoIncrementing' field, you do not put that field in the 'INSERT' statement the DB will do that itself.... likewise it should be excluded for any 'UPDATE' statements. Are you creating these SQL statements yourself, or is there an object doing that work? I don't work much with the UI objects that do create the statements, but I would imagine there either an option to exclude 'identity' fields, or you could manually edit the statement it created to remvoe them before running it.
:
: Second, if necessary you can tell SQL Server, if that is what you are using, to turn the constraint for Identity fields off if you are importing data into it in a block... ALthough, obviously you would have to verify the values are not duplicated yourself.
:
: ~rlc
:
:


Hi,
I use the Microsoft.Jet.OLEDB.4.0 dataprovider.
An example to give you an idea:

Private oConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & apppath & "db1.mdb;" & "Jet OLEDB:Database Password=db1")
    Private oDataSet As New DataSet()
    Private oDataAdapter As New OleDb.OleDbDataAdapter()

 Private oSelectCommand As New OleDb.OleDbCommand("Select Name, Adres, ID From CustomerTable", oConnection)
    Private oInsertCommand As New OleDb.OleDbCommand("Insert into CustomerTable(Name, Adres, ID) Values (@Name, @Adres, @ID)", oConnection)
    Private oUpdateCommand As New OleDb.OleDbCommand("Update CustomerTable Set Name = @Name, Adres = @Adres, Where ID = @ID", oConnection)
    Private oDeleteCommand As New OleDb.OleDbCommand("Delete From CustomerTable Where ID = @ID", oConnection)

'Create Update statement
        oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@Name", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Naam", System.Data.DataRowVersion.Current, Nothing))
 oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@Adres", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Adres", System.Data.DataRowVersion.Current, Nothing))
oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", System.Data.SqlDbType.Int, 5, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))

VBnewbie

Right, so if ID is a autoincrementing PK remove it from the insert statement. The DB will create it itself.

~rlc

This message was edited by vbnewbiejoep at 2005-12-20 12:50:13

---

: Right, so if ID is a autoincrementing PK remove it from the insert statement. The DB will create it itself.
:
: ~rlc
:
:
Hi,
I didnt know exactly what to remove.
Must i remove the "ID" from:

Private oInsertCommand As New OleDb.OleDbCommand("Insert into ProductenTable(ProductOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier, ID) Values (@ProductOmschrijving, @Artikelnummer, @PrijsPerEenheid, @Leverancier, @ID)", oConnection)

And/or from:

 oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))

And the same also with the update command?

So i tried all the options, but i get always an error.
The application put an autoincrement number in the ID cell, but i get an error saying that it can't be null or some other error i dont understand.

I do not know exactly what you mean and why removing the ID from the statements would help me, but the statements (with the ID) is working. By "working" i mean that the application automaticly put a number in the ID cell, beginning with 0. And i can also save it.
But the problem is that the autonumber always begins with 0.
So i get duplicate values in 2 or more ID cells. And that will result in an error becouse the ID cells must be unique.

So every time a user views (loads/opens) the datagrid, the next row of data he add to it, will have an ID cell that begins with 0, 1, 2,...
But that ID cells, most of the time, already excist

Hope you can help me. It seems i'm the only one who have a problem with it

VB Newbie

:

: Private oInsertCommand As New OleDb.OleDbCommand("Insert into ProductenTable(ProductOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier) Values (@ProductOmschrijving, @Artikelnummer, @PrijsPerEenheid, @Leverancier)", oConnection)
: 

: :

: : Private oInsertCommand As New OleDb.OleDbCommand("Insert into ProductenTable(ProductOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier) Values (@ProductOmschrijving, @Artikelnummer, @PrijsPerEenheid, @Leverancier)", oConnection)
: : 

:

Thanks for all your help, but with the code you said, i get an error saying that the ID cell can't be a Null value.
Its very weird though, becouse the autoincrement works and the application is putting a number in the ID cell.

Anyway,
I don't think there is a "clean" way to overcome my problem.
I think i just do the following:
I put a timer and in the timer i put the code:

oDataSet.Tables("ProductenTable").Columns("ID").DefaultValue = System.DateTime.Now

In this way i am sure that every primary key cell has it's own unique value. (unless in the unthinkable event when the systemclock is not displaying the right time and i am adding data at the same time and second as a previous one).

Well first off I think if you are getting an error, there must be something defining the ID somewhere. That should work fine... Here is a link From MS wit ha example for you.... The OLE is the lower example

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp

Second, instead of making a timer I would run a query and get the last ID entered and increase it by one. If you are multi threaded but a mutex on that so it can't run at same time.... However, this is a very basic use of the DB and you should be able to get working without all that extra work.

~rlc