Hi everyone,
I hope you can help me.

I am using vb.net and trying to delete a row in an access database. Firstly, I find the row and confirm there's record. If there's a record I ask for deletion. I am using the values submitted via a text box.

Here is my code:
Private Sub btnDeleteUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteUser.Click
Dim reponse_del As Integer
If txtSearch.Text = "" Then
MessageBox.Show("Please type a user name into the text box")
End If

'clear and refill Dataset
OleDAPass.SelectCommand.Parameters("UserName").Value = txtSearch.Text
DS_Pass1.Clear()
OleDAPass.Fill(DS_Pass1)
'no records of the search name
If DS_Pass1.Tables("PwordStore").Rows.Count = 0 Then
MessageBox.Show("Record not found")
ElseIf DS_Pass1.Tables("PwordStore").Rows.Count = 1 Then 'record exists delete it
MessageBox.Show("Are you sure you wish to delete this user?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
If reponse_del = DialogResult.Yes Then
OleDAPass.SelectCommand.Parameters ("UserName").Value = txtSearch.Text
'delete row
DS_Pass1.Tables("PwordStore").Rows(0).Delete()
OleDAPass.Update(DS_Pass1, "PwordStore")
End If
DS_Pass1.PwordStore.AcceptChanges()
DS_Pass1.Clear()
txtSearch.Text = ""
End If
End Sub

The record is found ok but the database does not update. I can update newly added records.
Does anyone know what I'm doing wrong? Any guidance will be very much appreciated :-|

Hi

There must be something wrong with the command that you are using to update the database.

Why don't you try creating a commandbuilder, it will create the commands automaticallly for you.

something like this:

hope it helps

Thanks for your advice William. I tried adding the commandbuilder as you suggested but it made no difference whatsoever. I have only one simple table in access and i used the wizard to create the Sql commands. the delete command i am using is DELETE FROM PwordStore WHERE (UserName = ? ). Anymore advice please?
Thanks in advance

Hi

Try to enclose the database update in a try - catch statement and see exacly what the problem is.

Also try adding to the update statement the name of the table.


other than that, I can not think of anything else, everything seems to be fine.

hi William
I tried your 'try catch' suggestion but it doesn't throw up any exceptions. The problem is the code runs and seems to be fine, it just doesn't update the database.
Thanks for your help, I guess I'm going to have to keep at it - unless anyone else can suggest something.

Hi again - I'm still stuck
i was wondering if my update statement could be wrong. It was generated by the access wizard :
It is also used to update the database when a new row is added and works fine for that. Does it need changing for the delete statement?
Please help me my deadline is getting very near.
Thanks

Hi

The delete command and the update commad have to be different.
The delete command will be:


This is what my command builder gives me to delete records from my table. My table has the fields Rep, ID, SN, etc.

Another thing. When you use the command builder, did you declare the variables for the oleDBdataadapter, oleDBconnection and oleDbcommand outside the procedure?


regards