1,105,395 Community Members

delete a record and update the dataset

Member Avatar
caramia
Newbie Poster
4 posts since Jun 2006
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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


Member Avatar
williamrojas78
Junior Poster
111 posts since Jun 2005
Reputation Points: 11 [?]
Q&As Helped to Solve: 10 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
caramia
Newbie Poster
4 posts since Jun 2006
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
williamrojas78
Junior Poster
111 posts since Jun 2005
Reputation Points: 11 [?]
Q&As Helped to Solve: 10 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
caramia
Newbie Poster
4 posts since Jun 2006
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
caramia
Newbie Poster
4 posts since Jun 2006
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
williamrojas78
Junior Poster
111 posts since Jun 2005
Reputation Points: 11 [?]
Q&As Helped to Solve: 10 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
oliverQ
Newbie Poster
1 post since Nov 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Netcode
Veteran Poster
1,049 posts since Jun 2009
Reputation Points: 33 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 8 [?]
 
0
 
DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)
Member Avatar
Netcode
Veteran Poster
1,049 posts since Jun 2009
Reputation Points: 33 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 8 [?]
 
0
 

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()
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article