Saving to database...

[liquidspaces]

This is what I'm 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.

 

Let's 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 it's trying to duplicate a primary key in the database. This of course is bad, but I honestly can't get it to work the way I need it to.

 

Since I'm 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?

 

I'd 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 can't 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? I'm trying to validate within the save button click event, but I'm not sure how to do it. This is what I'm trying to do, in plain english:

 

If primary key already exists then

edit that row

 

else

create row

end if

 

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

 

Thanks,

Kevin

Edited January 8, 2003 by liquidspaces

[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 it's ok to continue.

[Nerseus]

If you're using ...Rows.Add(...Table[..].NewRow()) then you can check the status of the row to see if it's 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 doesn't already exist?

 

If at all possible, it's 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 didn't 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 wouldn't work if there were multiple users, but since only one person will be using this at a time it should work fine.

 

We're just making the transition from doing things on paper to doing them on the computer. I'm 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 didn't 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.