locked
Data Delete access RRS feed

  • Question

  •  

    I Can Not Found wrong Please Help Me Where wrong 

    Dim connetionString As String
            Dim connection As OleDbConnection
            Dim oledbAdapter As New OleDbDataAdapter
            Dim sql As String
            connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Benzir\Documents\Test.accdb;"
            connection = New OleDbConnection(connetionString)
            sql = "Delete from Gomostapur_Tax where Holdin = '" & TextBox10.Text & "'"
            Try
                connection.Open()
                oledbAdapter.DeleteCommand = connection.CreateCommand
                oledbAdapter.DeleteCommand.CommandText = sql
                oledbAdapter.DeleteCommand.ExecuteNonQuery()
                MsgBox("Row(s) Deleted !! ")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

    Thursday, December 3, 2015 4:52 AM

Answers

  • You did not tell what is wrong with your code, is it returning error or its executing but not deleting from your DB?

    Please see below Function to use for a better resources management with the use of "Using" keyword that will be responsible to release the connection resource automatically once its out of scope:

    Private Function DeleteData(ByVal connectionString As String, ByVal Holdin As String) As Boolean
            Dim queryString As String = "Delete from Gomostapur_Tax where Holdin = '" & Holdin & "'"
            Try
                Using connection As New OleDbConnection(connectionString)
                    Dim command As New OleDbCommand(queryString, connection)
    
                    connection.Open()
    
                    command.ExecuteNonQuery()
                End Using
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

    You need to pass the ConnectionString and the Holdin value(and you can add more params like the delete query if you want):

     If DeleteData(connetionString, TextBox10.Text) Then
                MsgBox("Row(s) Deleted !! ")
            Else
                MsgBox("Error deleting records! ")
            End If

    If the Holdin column in your DB is of Type int, your Delete query should be like the below (omit the single quotes):

            Dim queryString As String = "Delete from Gomostapur_Tax where Holdin = " & Holdin & ""
    


    Fouad Roumieh

    • Marked as answer by benjir01 Thursday, December 3, 2015 11:02 AM
    Thursday, December 3, 2015 5:57 AM

All replies

  • You did not tell what is wrong with your code, is it returning error or its executing but not deleting from your DB?

    Please see below Function to use for a better resources management with the use of "Using" keyword that will be responsible to release the connection resource automatically once its out of scope:

    Private Function DeleteData(ByVal connectionString As String, ByVal Holdin As String) As Boolean
            Dim queryString As String = "Delete from Gomostapur_Tax where Holdin = '" & Holdin & "'"
            Try
                Using connection As New OleDbConnection(connectionString)
                    Dim command As New OleDbCommand(queryString, connection)
    
                    connection.Open()
    
                    command.ExecuteNonQuery()
                End Using
                Return True
            Catch ex As Exception
                Return False
            End Try
        End Function

    You need to pass the ConnectionString and the Holdin value(and you can add more params like the delete query if you want):

     If DeleteData(connetionString, TextBox10.Text) Then
                MsgBox("Row(s) Deleted !! ")
            Else
                MsgBox("Error deleting records! ")
            End If

    If the Holdin column in your DB is of Type int, your Delete query should be like the below (omit the single quotes):

            Dim queryString As String = "Delete from Gomostapur_Tax where Holdin = " & Holdin & ""
    


    Fouad Roumieh

    • Marked as answer by benjir01 Thursday, December 3, 2015 11:02 AM
    Thursday, December 3, 2015 5:57 AM
  • My Wrong That When I Run It Then Message Show Data Type Mismatch 
    Thursday, December 3, 2015 11:06 AM
  • Hi I am Beginer  .So Please Tell Me How Can I Use Your Code .  
    Thursday, December 3, 2015 11:23 AM
  • What is the data type of Holdin column in your table?

    I didn't write complex code, its just a Function that you need to add to your code. So after adding the Function DeleteData, if you want to call it from behind a button click for example:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Benzir\Documents\Test.accdb;"
            If DeleteData(connectionString, TextBox10.Text) Then
                MsgBox("Row(s) Deleted !! ")
            Else
                MsgBox("Deleteion failed.")
            End If
        End Sub
    I saw above that connectionString looks like a class-level variable that's why I didn't define again.


    Fouad Roumieh

    Thursday, December 3, 2015 11:31 AM
  • Holdin data Type "Number"
    Friday, December 4, 2015 11:04 AM
  • Holdin data Type "Number"
    What is the situation now? where is the latest version of the code you are using? are you getting any errors? Did you put a break-point to verify if the code executing?

    Fouad Roumieh

    Friday, December 4, 2015 11:14 AM