CODEWITHSUNDEEP
sqldatabasevb.net
user4357691
user4357691

Reputation:

VB.net Help needed on checking record exists in access

I am working on a program, and I am making a section where when you enter a customer id it will delete the record. The thing is, if you enter id 30 and there is no 30 it will still go ahead and delete it even though no record exists with that id. I want to make it display a message saying no record exists, but my below code won't work. I am so confused!

I found code on here, and I'm trying to implement it to my design, but it doesn't work. As you can see I tagged out my original code, but I tried to use this new code. Help please!

My error i receive when running is: Keyword not supported : 'provider'

Code:

Private Sub Client(ByVal ans As String)

    Dim con1 As SqlConnection = New SqlConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Database.accdb")
    If con1.State = ConnectionState.Closed Then con1.Open()
    Dim theQuery As String = "SELECT * FROM tbl WHERE ID = ?"
    Dim cmd1 As SqlCommand = New SqlCommand(theQuery, con1)
    cmd1.Parameters.AddWithValue("?", OleDbType.VarChar).Value = ans

    Using reader As SqlDataReader = cmd1.ExecuteReader()
        If reader.HasRows Then
            ' User exists delete
            Dim cmd As SqlCommand = New SqlCommand("DELETE FROM tbl where ID = ?", con1)
            cmd.ExecuteNonQuery()
        Else
            ' User does not exist throw message

            MsgBox("Records dont exist", MsgBoxStyle.Information, "Add New Customer!")

        End If
    End Using

    con1.Close()
End Sub

Upvotes: 3

Views: 775

Answers (1)

user4357691
user4357691

Reputation:

Since i was using SQL statements to connect to a ACCESS database it was not working correctly so i needed to go back to my roots and use the correct OleDb statements. The workable code in my case would be the following:

Private Sub DeleteClient(ByVal ans As String)
    If con.State = ConnectionState.Closed Then con.Open()

    cmd = New OleDbCommand
    cmd.Connection = con
    cmd.CommandText = "DELETE FROM tbl WHERE ID = ?"
    cmd.Parameters.Add("?", OleDbType.VarChar).Value = ans
    If cmd.ExecuteNonQuery() = 0 Then
        MsgBox("ID Does not exist!", MsgBoxStyle.Critical, "Enter new customer ID!")
    Else
        MessageBox.Show("The entered ID has now been deleted.", "Deletion Complete!", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If
    cmd.ExecuteNonQuery()
    con.Close()
End Sub

Upvotes: 1

Related Questions