delete a record and update the dataset

[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
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 :-|

[williamrojas78]

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:

If reponse_del = DialogResult.Yes Then
 OleDAPass.SelectCommand.Parameters ("UserName").Value = txtSearch.Text
 'delete row
 DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)

End If

hope it helps

[caramia]

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:

If reponse_del = DialogResult.Yes Then
 OleDAPass.SelectCommand.Parameters ("UserName").Value = txtSearch.Text
 'delete row
 DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)

End If

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

[williamrojas78]

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.

Try
    OleDAPass.update(DS_Pass1, "PwordStore")
Catch x As Exception
    ' Error during Update, add code to locate error, reconcile 
    ' and try to update again.
End Try

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

[caramia]

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.

[caramia]

Hi again - I'm still stuck
i was wondering if my update statement could be wrong. It was generated by the access wizard :

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)

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

[williamrojas78]

Hi

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

DELETE FROM  Table_name WHERE ( ((? = 1 AND Rep IS NULL) OR (Rep = ?)) AND (ID = ?) AND ((? = 1 AND SN IS NULL) OR (SN = ?)) AND ((? = 1 AND Veh IS NULL) OR (Veh = ?)) AND ((? = 1 AND Hm IS NULL) OR (Hm = ?)) AND ((? = 1 AND Dt IS NULL) OR (Dt = ?)) )

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

[oliverQ]

how do I add data into a dataset because currently my software just stores it on the ram until the computer is shutdown

[Netcode]

DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)

[Netcode]

OliverQ, here's the SQL syntax to insert record:

Dim objcommand As SqlCommand = New SqlCommand

            With objcommand
                .Connection = objConnection
                .CommandText = "INSERT INTO TableName" & _
               "(Columns separated with commas)" + _
                "VALUES (enter values separated with commas)"
              
            End With

            objConnection.Open()
            objcommand.ExecuteNonQuery()
            objConnection.Close()