acces Database program: delete/add record does not work

i am making a program that uses an acces database to store data. But the delete/add function does not work.. i am forgetting some kind of "execute querry" line or so (execute non query does not work, program crashes)

this is a part of my code (deleting part) :

(msgboxes and stuff like that are in dutch)

on form level:

[green][code]
Dim BOConnection As Boolean
Dim BOData As Boolean
Dim afsprid as int16


Private Sub btndelrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelrecord.Click
If MsgBox("Afspraak verwijderen?", MsgBoxStyle.YesNo, "Afspraak verwijderen?") = MsgBoxResult.Yes Then remrec_sure()
End Sub

Private Sub remrec_sure()
MsgBox("Afspraak met " & lblnaam.Text & " op " & lbldatum.Text & " is verwijderd")
DeleteRecord(afsprid)
If opendatabase() = False Then
BOConnection = False
Else
opendatabase()
BOConnection = True
If getdataset() = False Then
BOData = False
Else
BOData = True
getdataset()
End If
End If
End Sub

[/code]
[/green]

In the module:

[green][code]
Public dsdata As New DataSet()

Public Sub DeleteRecord(ByVal indexin As String)
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = cnndata
cmd.CommandText = "Delete * From afspraken WHERE ID='" & indexin & "'"
cmd.Connection = cnndata
cmd = Nothing
dsdata.Clear()
closedatabase()
opendatabase()
getdataset()

End Sub

[/code]
[/green]

I only posted the code needed to delete a record (else it would be to much). I am connected to the database, and i can read out of it.

Can someone tell me what i am doing wrong?

thanx!

Jeroen.

Comments

  • : i am making a program that uses an acces database to store data. But the delete/add function does not work.. i am forgetting some kind of "execute querry" line or so (execute non query does not work, program crashes)
    :
    : this is a part of my code (deleting part) :
    :
    : (msgboxes and stuff like that are in dutch)
    :
    : on form level:
    :
    : [green][code]
    : Dim BOConnection As Boolean
    : Dim BOData As Boolean
    : Dim afsprid as int16
    :
    :
    : Private Sub btndelrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelrecord.Click
    : If MsgBox("Afspraak verwijderen?", MsgBoxStyle.YesNo, "Afspraak verwijderen?") = MsgBoxResult.Yes Then remrec_sure()
    : End Sub
    :
    : Private Sub remrec_sure()
    : MsgBox("Afspraak met " & lblnaam.Text & " op " & lbldatum.Text & " is verwijderd")
    : DeleteRecord(afsprid)
    : If opendatabase() = False Then
    : BOConnection = False
    : Else
    : opendatabase()
    : BOConnection = True
    : If getdataset() = False Then
    : BOData = False
    : Else
    : BOData = True
    : getdataset()
    : End If
    : End If
    : End Sub
    :
    : [/code]
    : [/green]
    :
    : In the module:
    :
    : [green][code]
    : Public dsdata As New DataSet()
    :
    : Public Sub DeleteRecord(ByVal indexin As String)
    : Dim cmd As New OleDb.OleDbCommand()
    : cmd.Connection = cnndata
    : cmd.CommandText = "Delete * From afspraken WHERE ID='" & indexin & "'"
    : cmd.Connection = cnndata
    : cmd = Nothing
    : dsdata.Clear()
    : closedatabase()
    : opendatabase()
    : getdataset()
    :
    : End Sub
    :
    : [/code]
    : [/green]
    :
    : I only posted the code needed to delete a record (else it would be to much). I am connected to the database, and i can read out of it.
    :
    : Can someone tell me what i am doing wrong?
    :
    : thanx!
    :
    : Jeroen.
    :
    Since it's an Access database, try enclosing the data in double quotes.
    [code]
    cmd.CommandText = "Delete * From afspraken WHERE ID=""" & indexin & """"
    [/code]
    I know Access likes that.

    James
  • tried it, does not work :(




    : : i am making a program that uses an acces database to store data. But the delete/add function does not work.. i am forgetting some kind of "execute querry" line or so (execute non query does not work, program crashes)
    : :
    : : this is a part of my code (deleting part) :
    : :
    : : (msgboxes and stuff like that are in dutch)
    : :

    : : on form level:
    : :
    : : [green][code]
    : : Dim BOConnection As Boolean
    : : Dim BOData As Boolean
    : : Dim afsprid as int16
    : :
    : :
    : : Private Sub btndelrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelrecord.Click
    : : If MsgBox("Afspraak verwijderen?", MsgBoxStyle.YesNo, "Afspraak verwijderen?") = MsgBoxResult.Yes Then remrec_sure()
    : : End Sub
    : :
    : : Private Sub remrec_sure()
    : : MsgBox("Afspraak met " & lblnaam.Text & " op " & lbldatum.Text & " is verwijderd")
    : : DeleteRecord(afsprid)
    : : If opendatabase() = False Then
    : : BOConnection = False
    : : Else
    : : opendatabase()
    : : BOConnection = True
    : : If getdataset() = False Then
    : : BOData = False
    : : Else
    : : BOData = True
    : : getdataset()
    : : End If
    : : End If
    : : End Sub
    : :
    : : [/code]
    : : [/green]
    : :
    : : In the module:
    : :
    : : [green][code]
    : : Public dsdata As New DataSet()
    : :
    : : Public Sub DeleteRecord(ByVal indexin As String)
    : : Dim cmd As New OleDb.OleDbCommand()
    : : cmd.Connection = cnndata
    : : cmd.CommandText = "Delete * From afspraken WHERE ID='" & indexin & "'"
    : : cmd.Connection = cnndata
    : : cmd = Nothing
    : : dsdata.Clear()
    : : closedatabase()
    : : opendatabase()
    : : getdataset()
    : :
    : : End Sub
    : :
    : : [/code]
    : : [/green]
    : :
    : : I only posted the code needed to delete a record (else it would be to much). I am connected to the database, and i can read out of it.
    : :
    : : Can someone tell me what i am doing wrong?
    : :
    : : thanx!
    : :
    : : Jeroen.
    : :
    : Since it's an Access database, try enclosing the data in double quotes.
    : [code]
    : cmd.CommandText = "Delete * From afspraken WHERE ID=""" & indexin & """"
    : [/code]
    : I know Access likes that.
    :
    : James
    :

  • : tried it, does not work :(
    :
    :
    :
    :
    : : : i am making a program that uses an acces database to store data. But the delete/add function does not work.. i am forgetting some kind of "execute querry" line or so (execute non query does not work, program crashes)
    : : :
    : : : this is a part of my code (deleting part) :
    : : :
    : : : (msgboxes and stuff like that are in dutch)
    : : :
    :
    : : : on form level:
    : : :
    : : : [green][code]
    : : : Dim BOConnection As Boolean
    : : : Dim BOData As Boolean
    : : : Dim afsprid as int16
    : : :
    : : :
    : : : Private Sub btndelrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelrecord.Click
    : : : If MsgBox("Afspraak verwijderen?", MsgBoxStyle.YesNo, "Afspraak verwijderen?") = MsgBoxResult.Yes Then remrec_sure()
    : : : End Sub
    : : :
    : : : Private Sub remrec_sure()
    : : : MsgBox("Afspraak met " & lblnaam.Text & " op " & lbldatum.Text & " is verwijderd")
    : : : DeleteRecord(afsprid)
    : : : If opendatabase() = False Then
    : : : BOConnection = False
    : : : Else
    : : : opendatabase()
    : : : BOConnection = True
    : : : If getdataset() = False Then
    : : : BOData = False
    : : : Else
    : : : BOData = True
    : : : getdataset()
    : : : End If
    : : : End If
    : : : End Sub
    : : :
    : : : [/code]
    : : : [/green]
    : : :
    : : : In the module:
    : : :
    : : : [green][code]
    : : : Public dsdata As New DataSet()
    : : :
    : : : Public Sub DeleteRecord(ByVal indexin As String)
    : : : Dim cmd As New OleDb.OleDbCommand()
    : : : cmd.Connection = cnndata
    : : : cmd.CommandText = "Delete * From afspraken WHERE ID='" & indexin & "'"
    : : : cmd.Connection = cnndata
    : : : cmd = Nothing
    : : : dsdata.Clear()
    : : : closedatabase()
    : : : opendatabase()
    : : : getdataset()
    : : :
    : : : End Sub
    : : :
    : : : [/code]
    : : : [/green]
    : : :
    : : : I only posted the code needed to delete a record (else it would be to much). I am connected to the database, and i can read out of it.
    : : :
    : : : Can someone tell me what i am doing wrong?
    : : :
    : : : thanx!
    : : :
    : : : Jeroen.
    : : :
    : : Since it's an Access database, try enclosing the data in double quotes.
    : : [code]
    : : cmd.CommandText = "Delete * From afspraken WHERE ID=""" & indexin & """"
    : : [/code]
    : : I know Access likes that.
    : :
    : : James
    : :
    :
    :


    * kicks O-)
  • : i am making a program that uses an acces database to store data. But the delete/add function does not work.. i am forgetting some kind of "execute querry" line or so (execute non query does not work, program crashes)

    ...

    : [green][code]
    : Public dsdata As New DataSet()
    :
    : Public Sub DeleteRecord(ByVal indexin As String)
    : Dim cmd As New OleDb.OleDbCommand()
    : cmd.Connection = cnndata
    : cmd.CommandText = "Delete * From afspraken WHERE ID='" & indexin & "'"
    : cmd.Connection = cnndata
    : cmd = Nothing
    : dsdata.Clear()
    : closedatabase()
    : opendatabase()
    : getdataset()
    :
    : End Sub
    :
    [/code]
    [/green]This code never actually attempts to update the database. You have a connection, but you don't open it, which is probably why you got an exception when you called ExecuteNonQuery, but I can't say for certain because you didn't post the exception's text.


    [code]Dim cmd As New OleDb.OleDbCommand()
    cmd.Connection = cnndata
    cmd.CommandText = "Delete * From afspraken WHERE ID='" & indexin & "'"
    cmd.Connection = cnndata

    ' Add this part. Note that you should use a Try/Catch block
    ' when opening db connections and executing commands
    cnndata.Open()
    cmd.ExecuteNonQuery()
    cnndata.Close()
    [/code]

    Also, I would use parameters in your OleDbCommand rather than concatenating your command. That way, you won't have to worry about escaping quotes and injection and all that jazz.


  • : : i am making a program that uses an acces database to store data. But the delete/add function does not work.. i am forgetting some kind of "execute querry" line or so (execute non query does not work, program crashes)
    :
    : ...
    :
    : : [green][code]
    : : Public dsdata As New DataSet()
    : :
    : : Public Sub DeleteRecord(ByVal indexin As String)
    : : Dim cmd As New OleDb.OleDbCommand()
    : : cmd.Connection = cnndata
    : : cmd.CommandText = "Delete * From afspraken WHERE ID='" & indexin & "'"
    : : cmd.Connection = cnndata
    : : cmd = Nothing
    : : dsdata.Clear()
    : : closedatabase()
    : : opendatabase()
    : : getdataset()
    : :
    : : End Sub
    : :
    : [/code]
    : [/green]This code never actually attempts to update the database. You have a connection, but you don't open it, which is probably why you got an exception when you called ExecuteNonQuery, but I can't say for certain because you didn't post the exception's text.
    :
    :
    : [code]Dim cmd As New OleDb.OleDbCommand()
    : cmd.Connection = cnndata
    : cmd.CommandText = "Delete * From afspraken WHERE ID='" & indexin & "'"
    : cmd.Connection = cnndata
    :
    : ' Add this part. Note that you should use a Try/Catch block
    : ' when opening db connections and executing commands
    : cnndata.Open()
    : cmd.ExecuteNonQuery()
    : cnndata.Close()
    : [/code]
    :
    : Also, I would use parameters in your OleDbCommand rather than concatenating your command. That way, you won't have to worry about escaping quotes and injection and all that jazz.
    :
    :
    :

    thnx for the help, but i cant get it working at the moment.
    You said i have a connection, but i dont open it. But when i put .open between it, like in the example, i get a error saying "connection already open"

    When i replace cnndata.open with Opendatabase (= with try and catch) or just remove this lines, i get one of these errors at the "execute non query" line:

    An unhandled exception of type 'System.NullReferenceException' occurred in agenda.exe

    Additional information: Object reference not set to an instance of an object.


    or:

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



Sign In or Register to comment.

Howdy, Stranger!

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

Categories