Create new overwriting existing records

[JackBurton07]

Hi

When I create a new record, my app is overwriting existing records.

Its supposed to create a new id_no ( they are autonumbers in the access database) and update.

However its selecting an existing record and overwriting existing records in the database and at the same time creating a new blank record in the databse.

please help

Public Function CreateNewRecord()
Dim myRS As New ADODB.Recordset()
'open the recordset and have it contain all records in the database table called client'
myRS.Open("client", myDb, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)
'add an empty record'
myRS.AddNew()
'update the recordset to include the new entry'
myRS.Update()
myRS.Requery()
'go to the record we just created'
myRS.MoveLast()
'Grab the records ID value so that we can edit it, return this from the function
CreateNewRecord = myRS.Fields("id_no").Value

'Close the record set'
myRS.Close()


End Function


Private Sub cmdNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNew.Click
'clear the exisitng fields'
ClearFields()
currentID = CreateNewRecord()
enableall()

cmdUpdate.Enabled = False
txtdaysrem.Enabled = False
txtrate.Enabled = False
txtmat1.Enabled = False
txtterm1.Enabled = False
'get the id_no from creating the new record'


cboclient.Enabled = False





MsgBox("Once you have entered the data into the form click on the update button to save the data to the database", MsgBoxStyle.Information, "Add new record")

End Sub

 

[macleod1021]

It's very dangerous to use the last entry of a recordset assuming that it's the most recent inserted record. The optimal solution is to insert it all at once. If you need the identity before inserting the actual data, then you can reliably get the correct ID by using "select @@identity".

I don't have any VB code with me at the moment to show, but there's a ton of examples on the net. Basically, the @@identity will return the primary key of the last inserted record on the calling connection. The last part is what makes it safe. You don't have to worry about other connections adding data before you getting your key and messing it up.

I have some code tucked away on my server that used @@Identity with Access 2000. I'll look for it and post it when I can.

 

[JackBurton07]

Thanks that would be great!

 

[jebenson]

As this is the VB .NET forum, I feel I should point out that you really, really, really need to migrate away from ADODB and start using ADO .NET to do your database programming.

Here's a link to a MSDN article that shows how to get @@Identity in ADO .NET:

http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.71).aspx

I you must, for some reason, continue using ADODB, here's a link that shows how to get @@Identity that way:

http://support.microsoft.com/default.aspx/kb/232144

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!