VB.net Help needed on checking record exists in access

Question

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

I can appreciate your thinking: dont execute a delete query unless that row exists. But, the result is that you execute *another* query, so it is even up on query count when it doesnt exist, but you have fired *2* when it does exist. A) `cmd.ExecuteNonQuery()` is a function returning the number of rows affected, so you can know if there was one. b) if you were working from a DataTable you could know from that if it exists (but still do not need to). — Ňɏssa Pøngjǣrdenlarp, Mar 19 '16 at 19:28
So you are trying to update an MS-Access database using the classes built for SQL Server. No, that cannot work at all. Use the classes from the OleDb namespace. (OleDbConnection, OleDbCommand etc...) Of course, as @Plutonix says, there is no need to check if the record exists or not if you plan to delete it. Just delete and get the result back from ExecuteNonQuery — Steve, Mar 19 '16 at 19:31
Ok i deleted all that, but even if i change all that back to OleDb like before but how do i make it so it checks to see if the row exists? — , Mar 19 '16 at 19:34
`how do i make it so it checks to see if the row exists` read the first comment: you dont. there is no need to run a preliminary query to see if you should run the real query. It doesnt save anything — Ňɏssa Pøngjǣrdenlarp, Mar 19 '16 at 19:35
Yeah i understand that but i want it to check and stop if it doesnt exist because when i press the delete customer button it brings up a input box and within the input box you need to enter the customer id, if they enter 30 and there is no 30 it will display a message saying: Customer is now removed from database. But it wont be right as there is no customer there and i want to tell people - there is no customer here enter another? If you get me:) Clad to see you helping me out again Plutonix haha appreciate it! — , Mar 19 '16 at 19:39
`If cmd.ExecuteNonQuery() = 0 Then THERE WAS NO CUST Else CUstomer was deleted ` Are the records shown in a DGV or something else? *My* users almost never see the Id/PK unless it has some business meaning (natural, non AI type PK) — Ňɏssa Pøngjǣrdenlarp, Mar 19 '16 at 19:40
If you are abstractly asking _how do I check if a record exists in Access_ then you are limited by the functionality available in Access. There is no IF EXISTS statement so you have to execute a _SELECT FIELD FROM TABLE WHERE CONDITION_ and run the query with ExecuteScalar that is the method with the lower resource requirements — Steve, Mar 19 '16 at 19:40
It should be mentioned that for single tables, all this could be eliminated using a DataAdapter and a DataTable. `da.Upate(myDT)` will delete what needs to be deleted, update what needs to be updated etc. And, not only could the user see the entire row and Delete "Ziggy's Hair Emporium" (which has meaning to them, unlike `30`), but your code could poke around and see the data — Ňɏssa Pøngjǣrdenlarp, Mar 19 '16 at 19:47
Hmm i see what you's mean. See this is for a project and it states i need a remove client feature so once a client is added i can press the remove menu and there i must enter the clients CustomerID which is numbered with the field autonumber so if i want to delete customer with id 20 i enter 20 and it will delete the row and customer is deleted perfectly but if someone enters 30 and there IS no 30 then i was hoping to display some sort of message saying sorry no customer exists enter another number? — , Mar 19 '16 at 20:30
What Plutonix means: `cmd.CommandText = "DELETE FROM tblcustomer WHERE CustomerID = ?" : cmd.Parameters.Add("?", OleDbType.VarChar).Value = ans : If cmd.ExecuteNonQuery() = 0 Then MsgBox("Records dont exist", MsgBoxStyle.Information, "Add New Customer!") End If` — Olivier Jacot-Descombes, Mar 19 '16 at 21:26
@OlivierJacot-Descombes That is exactly the code i was looking for! Thank you all very much and appreciate the constant help! Thanks plutonix for another helpful response and olivier for putting together the correct code needed to work. — , Mar 19 '16 at 22:04

score 1 · Answer 1 · 2016-04-13T22:06:54.180

1

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

edited Apr 13 '16 at 22:06

answered Mar 19 '16 at 22:07

2

You have an extra`cmd.ExecuteNonQuery()` at the bottom and the cmd object should be disposed of see [OleDbException was unhandled...](http://stackoverflow.com/a/29187199/1070452). Upvote it if it helps – Ňɏssa Pøngjǣrdenlarp Mar 19 '16 at 23:16
1

You can also safely create a new connection each time you need one (enclose it in a Using statement). The connections will be pooled automatically for you. Therefore this does not create a physical connection each time. – Olivier Jacot-Descombes Mar 20 '16 at 19:17

VB.net Help needed on checking record exists in access

1 Answers1