Problems Deleting/Updating Rows, Using VB. NET and Access Database

[chris20]

Hi,

 

I just started learning Visual Basic.NET and I have some problems deleting and updating rows to a Access Database. I have no problems adding rows into the database. If if anyone has any advice its more than welcome. You can download the VB files from the following URL

 

http://www.powerhost4u.com/VB/WindowsApplication1.zip

 

Thanks

 

The error I get when I click the update and delete buttons is

 

'An unhandled exception of type'System.Data.OleDb.OleDbException' occurred in the system.data.dll

 

After I click the continue button the following code is highlighted in green

 

objCommand.ExecuteNonQuery()

 

Below is the Event when I click the Delete Button

 

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

Dim intPosition As Integer

Dim objCommand As OleDbCommand = New OleDbCommand

 

intPosition = Me.BindingContext(objDataView).Position - 1

 

If intPosition < 0 Then

intPosition = 0

End If

 

objConnection.Open()

 

objCommand.Connection = objConnection

objCommand.CommandText = "DELETE FROM Customer " & _

"WHERE CustomerID='" & txtID.Text & "'"

objCommand.CommandType = CommandType.Text

 

objCommand.Parameters.Add(New OleDbParameter)

objCommand.Parameters.Item(0).Direction = ParameterDirection.Input

objCommand.Parameters.Item(0).DbType = DbType.String

objCommand.Parameters.Item(0).Size = 25

objCommand.Parameters.Item(0).Value = txtID.Text

 

 

objCommand.ExecuteNonQuery()

 

objConnection.Close()

 

 

FillDataSetAndView()

BindFields()

 

Me.BindingContext(objDataView).Position = intPosition

 

ShowPosition()

 

 

objCommand = Nothing

 

 

 

 

End Sub

 

 

 

Below is the Event when I click the Update Button.

 

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

Dim intPosition As Integer

Dim objCommand As OleDbCommand = New OleDbCommand

 

intPosition = objCurrencyManager.Position

 

 

objConnection.Open()

 

objCommand.Connection = objConnection

objCommand.CommandText = "UPDATE Customer " & _

"SET CustomerName='" & txtname.Text & "',CustomerAddress='" & txtaddress.Text & "' WHERE CustomerID='" & txtID.Text & "'"

objCommand.CommandType = CommandType.Text

 

objCommand.Parameters.Add(New OleDbParameter)

objCommand.Parameters.Item(0).Direction = ParameterDirection.Input

objCommand.Parameters.Item(0).DbType = DbType.String

objCommand.Parameters.Item(0).Size = 25

objCommand.Parameters.Item(0).Value = txtname.Text

 

objCommand.Parameters.Add(New OleDbParameter)

objCommand.Parameters.Item(1).Direction = ParameterDirection.Input

objCommand.Parameters.Item(1).DbType = DbType.String

objCommand.Parameters.Item(1).Size = 25

objCommand.Parameters.Item(1).Value = txtaddress.Text

 

objCommand.Parameters.Add(New OleDbParameter)

objCommand.Parameters.Item(2).Direction = ParameterDirection.Input

objCommand.Parameters.Item(2).DbType = DbType.String

objCommand.Parameters.Item(2).Size = 10

objCommand.Parameters.Item(2).Value = txtID.Text

 

objCommand.ExecuteNonQuery()

 

objConnection.Close()

 

 

FillDataSetAndView()

BindFields()

 

objCurrencyManager.Position = intPosition

 

ShowPosition()

 

 

objCommand = Nothing

End Sub

[quwiltw]

Your query doesn't take any parameters but you're adding parameters to the command object. This might not be all of your problems but it's what I've noticed thus far.

[Eleventeen]

Secondly you need to specify what fields you are deleting from the delete query.

 

You need to have "DELETE fieldname FROM....." to delete the field data.

 

Or from what i can see you want to delete the whole record so it should be

"DELETE * FROM ....."

 

Hope that helps