Access Database Update and delete - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Access Database Update and delete

hendrikbezhendrikbez Posts: 6Member
I am using access 2003 for database, I can add, but it seems that I cannot update or delete.

[color=Red]Databse name Blokkies
Table name = tBlokkies
Colums leidraad (PK), A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12 [/color]

I have upload my files, and here is the code for update and delete.

[color=Blue] Public Function Edit() As String
Try

Dim OleDbConn As OleDbConnection = New OleDbConnection(ConnString)
OleDbConn.Open()

Dim MyOledbCommand As OleDbCommand = New OleDbCommand()

Dim String1, String2, String3, String4, String5, string6, string7, string8, string9, string10, string11, string12, string13 As String

String1 = TxtLuidraad.Text
String2 = txtA1.Text()
String3 = txtA2.Text()
String4 = txtA3.Text()
String5 = txtA4.Text()
string6 = txtA5.Text()
string7 = txtA6.Text()
string8 = txtA7.Text()
string9 = txtA8.Text()
string10 = txtA9.Text()
string11 = txtA10.Text()
string12 = txtA11.Text()
string13 = txtA12.Text()


MyOledbCommand.CommandText = "Update Tblokkies Set " _
& "leidraad='" & String1 & "', " _
& "A1='" & String2 & "', " _
& "A2='" & String3 & "', " _
& "A3='" & String4 & "', " _
& "A4='" & String5 & "' " _
& "A5='" & string6 & "' " _
& "A6='" & string7 & "' " _
& "A7='" & string8 & "' " _
& "A8='" & string9 & "' " _
& "A9='" & string10 & "' " _
& "A10='" & string11 & "' " _
& "A11='" & string12 & "' " _
& "A12='" & string13 & "' " _
& "WHERE leidraad = " & ListBox1.Items(ListBox1.SelectedIndex)

MyOledbCommand.Connection = OleDbConn

MyOledbCommand.ExecuteNonQuery()
OleDbConn.Close()

DisableTextboxes()
NeroBar1.Value = 0

FillDataGrid("Select * from Tblokkies")
FillListBox("Select * from Tblokkies")

btnaddNew.Enabled = True
btnDelete.Enabled = True
' btnupdate.Enabled = True

SaveOrEdit = "Cancel"

StatusLabel.Text = " Data Edited."
Catch err As System.Exception
StatusLabel.Text = err.Message
End Try

End Function

Public Function Delete() As String
Try

Dim OleDbConn As OleDbConnection = New OleDbConnection(ConnString)
OleDbConn.Open()

Dim MyOledbCommand As OleDbCommand = New OleDbCommand()

MyOledbCommand.CommandText = "Delete From Tblokkies " _
& "WHERE leidraad = " & ListBox1.Items(ListBox1.SelectedIndex)

MyOledbCommand.Connection = OleDbConn

MyOledbCommand.ExecuteNonQuery()
OleDbConn.Close()

DisableTextboxes()
NeroBar1.Value = 0

FillDataGrid("Select * from Tblokkies")
FillListBox("Select * from Tblokkies")
StatusLabel.Text = " Data Deleted."

Catch err As System.Exception
StatusLabel.Text = err.Message
End Try

End Function

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click

Try

Dim CatchError As Integer = ListBox1.Items(ListBox1.SelectedIndex)

Catch err As System.Exception

StatusLabel.Text = " Select Record to Edit"
Exit Sub
End Try

SaveOrEdit = "Edit"
StatusLabel.Text = " Edit Existing Data"

EnableTextboxes(False)

btnaddNew.Enabled = False
btnDelete.Enabled = False

End Sub


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

Try

Dim CatchError As Integer = ListBox1.Items(ListBox1.SelectedIndex)

Catch err As System.Exception

StatusLabel.Text = " Select Record to Delete"
Exit Sub
End Try

Delete()

End Sub[/color]

Comments

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    Do me a favor, check and be sure that you have created a primary key on your table. In order to Update or Delete a record, the OLEDataAdaptar needs a primary key record to index on to update a row. Without that, you could be it doesn't know "exactly" which record your trying to update or delete, just that you want to update or delete one.

    After you try that, let me know if that works or not and I will suggest other things. 9 times out of 10 it's the primary key (I do that a lot)

    happy coding,
    firesickle.com
  • hendrikbezhendrikbez Posts: 6Member
    I do have a primary key "leidraad"

    1. I click on delete button.
    2. Is says "choose recored to delete"
    3. I click on record in my listbox.
    4. It says "record ###3 seleted".
    5. Click on delete button again, must delete it now, but ask me again "Choose record to close".

    It only happening to delete adn update
  • MAHEYMAHEY Posts: 58Member
    Otherwise you can try by deletion without primary key also,
    but....listbox click() .....for deleting the records.
    Selected index is not good for deletion purpose.



Sign In or Register to comment.