Problem with delete and update in VB.Net 2003

[caramia]

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

'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.

This is my update statement:UPDATE PwordStore SET UserName = ?, Pword = ? WHERE (PwordNo = ?) AND (Pword = ? OR ? IS NULL AND Pword IS NULL) AND (UserName = ? OR ? IS NULL AND UserName IS NULL)

Does anyone know what I'm doing wrong? Any guidance will be very much appreciated

 

[vis781]

If your are trying to delete a row from a databse then you need a different kind of SQL statement. A 'DELETE FROM' statement specifically.

Would Go something like....

DELETE FROM 'TableName' WHERE 'ColumnName' = Somevalue

 

[caramia]

Hi vis781
Thank you for replying
I do have a delete statement. It was created by the wizard when I configured the data adapter. It is as follows:
DELETE FROM PwordStore WHERE (PwordNo = ?) AND (Pword = ? OR ? IS NULL AND Pword IS NULL) AND (UserName = ? OR ? IS NULL AND UserName IS NULL)
Having not created this myself I'm a little dubious that it is correct.
Thanks for your help.

 

[vis781]

Have you set a breakpoint and stepped through to see if the code is actually running? My guess would be that if the sql has benn generated then it should work. My guess is that it isn't running your Dataset.update line.

Ahh, hang on a minute, your response_del isn't getting a return value from the messagebox because you havent assigned it.

Response_Del = MessageBox.Show("Are you sure you wish to delete this user?", _
"Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)

 

[caramia]

Thank you soooo much! I've spent the best part of a month looking at this and you finally got it working.
Not only did i have to assign the messagebox.show but i had to swap it for the msgBox function and it worked just fine.
I'll be back soon with my next problem. Watch this space