Ad:

VB.NET Discussion Thread
Unsolved
Views: 37524

Jun 26th, 2006

delete a record and update the dataset

Expand Post »

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

Similar Threads

How to delete record from Datagrid and not from Database in ASP.NET
SQL Delete/Update Error in ColdFusion
datagrid delete function not auto update in ASP.NET
need to update each records after record deleted in PHP

caramia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Jun 2006

Permalink

Jun 27th, 2006

Re: delete a record and update the dataset

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:

VB.NET Syntax (Toggle Plain Text)
 
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
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

williamrojas78

Reputation Points: 23

Solved Threads: 10

Junior Poster

Offline

111 posts
since Jun 2005

Permalink

Jun 27th, 2006

Re: delete a record and update the dataset

Quote originally posted by 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:
VB.NET Syntax (Toggle Plain Text)
 
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
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

caramia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Jun 2006

Permalink

Jun 27th, 2006

Re: delete a record and update the dataset

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.

VB.NET Syntax (Toggle Plain Text)
 
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
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.

williamrojas78

Reputation Points: 23

Solved Threads: 10

Junior Poster

Offline

111 posts
since Jun 2005

Permalink

Jun 27th, 2006

Re: delete a record and update the dataset

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Jun 2006

Permalink

Jun 28th, 2006

Re: delete a record and update the dataset

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

(Toggle Plain Text)

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

Last edited by caramia; Jun 28th, 2006 at 8:12 pm.

caramia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Jun 2006

Permalink

Jun 30th, 2006

Re: delete a record and update the dataset

Hi

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

VB.NET Syntax (Toggle Plain Text)
 
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 = ?)) )
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

williamrojas78

Reputation Points: 23

Solved Threads: 10

Junior Poster

Offline

111 posts
since Jun 2005

Permalink

Nov 19th, 2010

Re: delete a record and update the dataset

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

oliverQ

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Nov 2010

Permalink

Nov 19th, 2010

Re: delete a record and update the dataset

VB.NET Syntax (Toggle Plain Text)
 DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)
 DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)

Netcode

Reputation Points: 43

Solved Threads: 67

Veteran Poster

Offline

1,016 posts
since Jun 2009

Permalink

Nov 19th, 2010

Re: delete a record and update the dataset

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

VB.NET Syntax (Toggle Plain Text)
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()
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()

Netcode

Reputation Points: 43

Solved Threads: 67

Veteran Poster

Offline

1,016 posts
since Jun 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Playing wav files

Next Thread in VB.NET Forum Timeline: SelectedIndexChanged event code

DaniWeb Message

Cancel Changes

User Name
Password