Saving to database...

[liquidspaces]

This is what Im trying to do:

On my form, I have fields where the user can enter in an assortment of different information. Also on the form is a unique identifying number which when saved to the database is the primary key in a table.

Lets say that the user enters in half of the information and then clicks save. The user then realizes that they forgot to fill in the rest of it so he fills it in and then clicks save again. As it stands right now, the program will crash because its trying to duplicate a primary key in the database. This of course is bad, but I honestly cant get it to work the way I need it to.

Since Im saving to a database and the primary key of this table is the unique identifying number, how can I:

1)Check to see if there is already a primary key field with the same value and:

2)Edit that row if the primary key already exists?

Id prefer to do this all through the click event for the "save" button.

Thanks,
Kevin

 

[Robby]

I would do all the validation in the save button....

Is the form or textbox bound to the database, or is the user just entering data on a blank form?

Is the primary key generated automaticaly?

And if the user forgot to fill in the rest of it, you should remind them as soon as they click save.

 

[liquidspaces]

Each textbox is bound to the database.

The primary key is generated automatically by my program, not by the database.

I cant remind the user to fill in the rest of it because not all fields are required fields. Quite a few are optional, and some are not even enabled unless certain options are chosen.

Does that help at all? Im trying to validate within the save button click event, but Im not sure how to do it. This is what Im trying to do, in plain english:

If primary key already exists then
edit that row

else
create row
end if

Im not sure of the syntax to find out if the primary key already exists. Any help would be appreciated.

Thanks,
Kevin

 

[Robby]

When you generate a new Primary key, do something like this...

Select idField from myTable Where idField = " & intNewId

If the rs returns a record the PK exists else its ok to continue.

 

[Nerseus]

If youre using ...Rows.Add(...Table[..].NewRow()) then you can check the status of the row to see if its an insert or an update (this is built into the DataSet object).

Also, how can you be sure that the first insert even works? Meaning, how do you calculate your primary key value to make sure it doesnt already exist?

If at all possible, its much easier to use an auto-incrementing column (Identity in SQL Server, AutoIncrement in Access). If you define your DataSet and binding correctly the ID on an insert will show "1" until it commits and then it should automatically show the new value (say 42356) once the Update() method has been called.

-Nerseus

 

[liquidspaces]

There were a few different reasons why I didnt want to use the auto incrementing column, but the main reason was because it is impertive for this project that the primary key, which in this case is the Bill of Lading Number, be displayed as soon as the user enters in data...before they click save.

Since the numbers have to be sequential, I just get the value of the last field and then add 1 to get the next number. Of course this wouldnt work if there were multiple users, but since only one person will be using this at a time it should work fine.

Were just making the transition from doing things on paper to doing them on the computer. Im new to all of this, but have done the best I can to learn how to do this in the past month or so. I didnt use the autonumber, because the numbers need to start at 5065. Maybe you can set the autonumber to start there, but it was much easier for me just to make the first entry at 5064 and then add 1 to it to get the first record.