[RESOLVED] Check if record exists in a table

[Longbow22]

I have a tenant details form that has a field called TenantID, I also have a Lease table that also has a TenantID field, what I want to do is click the lease button, check to see if the lease already exists, if it does open the lease form to that record. If not then open the lease form to a New record so the user can enter all the information for that lease.

I am trying to use the below code but am having a little trouble understanding how it all works. What needs to be declared, what doesnt

    Private Sub btnLease_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLease.Click
        Dim fm2 As New LeaseDetails
        Dim str As String
        Dim cmd As String
        Using conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Tenant Tracker 2011_be.accdb;Persist Security Info=False")
            Using cmd As SqlClient.SqlCommand = conn.CreateCommand()
                conn.Open()
                str = "SELECT * FROM Lease WHERE ContactID =" & ContactID.Text & ""
                cmd = New OleDbCommand(str, conn)
                dr = cmd.ExecuteReader()
                If Not dr.HasRows Then
                    AddNew = True
                Else
                    fm2.idToOpen = Me.ContactID.Text
                End If
            End Using
        End Using
        fm2.Show()
    End Sub

Thanks
LB

[Shaggy Hiker]

Your TenantID would be what is ContactID in that code. However, the code is not quite sufficient. There are four issues that I see:

1) You need only one field, so using *, which is never quick, is total overkill in this case.

2) There is no point in getting a datareader. Since you only want to know whether there is or is not a record, you can use ExecuteScalar, which is more efficient than the reader.

3) You shouldn't concatenate in the contents of a textbox, as it leaves you open to SQL injection attacks.

4) The database work needs to be wrapped in an exception handler. Exceptions would be rare, in this case, but they could happen, so it's the right place for exception handling.

An improved version is this:

Private Sub btnLease_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLease.Click
        Dim fm2 As New LeaseDetails
        Dim str As String
        Dim cmd As String
        Using conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Tenant Tracker 2011_be.accdb;Persist Security Info=False")
            Using cmd As SqlClient.SqlCommand = conn.CreateCommand()
               Try
                conn.Open()
                str = "SELECT ContactID FROM Lease WHERE ContactID = @p1"
                cmd.AddWithValue("@p1",ContactID.Text)
                
                If cmd.ExecuteScalar IsNot Nothing Then
                    AddNew = True
                Else
                    fm2.idToOpen = Me.ContactID.Text
                End If
              Catch ex As Exception
               'Do SOMETHING here, at least show the ex.Message, if nothing else. 
              End Try
            End Using
        End Using
        fm2.Show()
    End Sub

I guess I would also want to be setting the idToOpen to something whether or not there was something returned, even if it was set to -1 if nothing was returned.

[Longbow22]

Couple questions with that update.
2 Errors on the line:
Using cmd As SqlClient.SqlCommand = conn.CreateCommand()
Variable 'cmd' hides a variable in an enclosing block
Value of type 'System.Data.OleDb.OledbCommand' cannot be converted to 'System.Data.SQLClient.SQLCommand

Error on this line
cmd.AddWithValue("@p1", ContactID.Text)
AddwithValue is not a member of System.Data.SQLClient.SQLCommand

Thought that one would clear up if I fix the 2 above.

Also

Where does @p1 come from?

As I have mentioned before I am new to VB 2010, everything I have done was in Access 2007.

Thanks

LB

[Shaggy Hiker]

Ah, right. The first error is because I didn't fully clean up an error in the original example. You need to delete the line:

Dim cmd As String

The using block declares cmd, and that is all the declaration you need or want. In the initial code, I noticed that while the Using block set cmd to a command, the code did that a second time later one, which was a bad thing to do. I removed that line, but missed the initial declaration.

As for the second one: That was a typo, which is what I get when I write off-the-cuff rather than in an IDE. The line should be:

cmd.Parameters.AddWithValue

It is the Parameters that has .AddWithValue, and the command object has Parameters. I left out the parameters portion, and the command object itself does not have an AddWithValue member.


EDIT: Missed your final question. @p1 may not even work. In fact, it probably won't. The @ symbol is how you identify a parameter in a SQL query for SQL Server. The 'p1' portion is arbitrary. In my case, I have never seen a reason to make an elaborate name, so I just use p1, p2, p3, and so on for the parameters in a query. Access does not support named parameters in queries, though, so I believe all parameters in Access have to be simple ?. Therefore, the SQL statement would become:

str = "SELECT ContactID FROM Lease WHERE ContactID = ?"

Named parameters are decidedly better, since you can't tell which parameter is which when you only use a ?. Of course, that doesn't matter in this case since you only have a single parameter. I believe Access ignores the name supplied in the AddWithValue method, so I'm pretty sure that can be left as it is. The key is that the parameters to an Access query have to be supplied in the same order than they are used in the query. Again, that doesn't matter in this case, since there is only one, but it would matter if you use more than one parameter. With a system that uses named parameters, such as SQL Server (which is what I mostly use), each parameter has a name (p1, p2, p3, etc., in my case), so the order they are supplied doesn't matter.

[Longbow22]

Ok only problem left is I had to change from: and change my acdb file to .mdb
Provider=Microsoft.Jet.OLEDB.12.0;Data Source to Provider=Microsoft.Jet.OLEDB.4.0;Data Source
Kept saying something about driver not loaded, found thousands of posts with the same problem..but no real fixes.

I still get the error:
Value of type 'System.Data.OleDb.OledbCommand' cannot be converted to 'System.Data.SQLClient.SQLCommand
on Using cmd As SqlClient.SqlCommand = conn.CreateCommand()

Thanks
LB

[Shaggy Hiker]

The first error is unfortunate. There is an issue with mdb files because MS has not provided a 64-bit JET driver. The solution is simple enough, but unfortunate. What you have to do is to make sure that you are not using the AnyCPU setting, but are targeting x86. The reason is that if you use AnyCPU, and the program is run on a 64-bit system, it will try to find a 64-bit JET driver, which doesn't exist, and that'll be that. By targeting x86, you force it to look for a 32-bit JET driver using WOW, which does exist, so it'll work. The setting is on either the Application or Compile tab of the Project|Properties, though I believe it is on the latter under the Advanced Compile Options button. It's worth a look.

What's going on with the other part is a bit odd, though very easy to fix. When ADO.NET came out back around 2002, MS was making a big deal about creating special classes for different databases. The idea was that the SQL Server namespace would all be in SQL Server, Ole would all be in OleDB, ODBC would all be in ODBC, and so on. The suggestion was that each namespace would have classes optimized for that particular database. OLE and ODBC are quite generic, and MS was suggesting that new, specific, namespaces would be created for different databases over time. The specific namespaces would have classes that would be optimized for that particular database. Oddly, aside from that initial burst, I don't think there have been any others. Therefore, you have the specially focused SQLClient for SQL Server, and the more generic OleDB for anything else (you can even use it for SQL Server, theoretically).

In the initial post, you had a mix of SQLClient and OleDB. Since you are using Access, you need to use OleDB exclusively. The change is pretty simple. Wherever you see SQLClient, change it to OleDB. Aside from that, all the classes in SQLClient beging with SQL (SQLCommand, SQLConnection, etc.), as you might expect, all the classes in OleDB begin with something else (I think it is OleDB, but it may just be Ole). Therefore, you will have to change those names over, as well. The classes are all the same. So there are Command classes in each, and connection classes in each, they just have different prefixes on them.

[enrico1982]

Value of type 'System.Data.OleDb.OledbCommand' cannot be converted to 'System.Data.SQLClient.SQLCommand
on Using cmd As SqlClient.SqlCommand = conn.CreateCommand()

You are using an OLEDB connection then trying to create an SQL (for MSSQL and MySQL I believe) command from it.

Your line

Using cmd As SqlClient.SqlCommand = conn.CreateCommand()

Should be:

Using cmd As OleDb.OleDbCommand= conn.CreateCommand()

EDIT: Soz ShaggyHiker, didn't see you post.

[Longbow22]

Got that sorted out, getting closer for sure.
Did I have to dim p1 as a string?
When I step through the code its getting to:
If cmd.ExecuteScalar IsNot Nothing Then and falls through to the:
Catch ex As Exception
I dont quite understand this line:
cmd.Parameters.AddWithValue("@p1", ContactID.Text) Arent they essentially the same value? Since p1 is a variable that has the ContactID in it?

Also the line with str, whats that used for, dont see it referenced any where else?
I have this now.

    Private Sub btnLease_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLease.Click
        Dim fm2 As New LeaseDetails
        Dim str As String
        Dim p1 As String
        Using conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Rent Roll\Tenant Tracker 2011_be.mdb;Persist Security Info=False")
            Using cmd As OleDb.OleDbCommand = conn.CreateCommand
                Try
                    conn.Open()
                    p1 = Me.ContactID.Text
                    str = "SELECT ContactID FROM Lease WHERE ContactID = @p1"
                    cmd.Parameters.AddWithValue("@p1", ContactID.Text)

                    If cmd.ExecuteScalar IsNot Nothing Then
                        AddNew = True
                    Else
                        fm2.idToOpen = Me.ContactID.Text
                    End If
                Catch ex As Exception
                    'Do SOMETHING here, at least show the ex.Message, if nothing else. 
                End Try
            End Using
        End Using
        fm2.Show()
    End Sub

[Shaggy Hiker]

P1 doesn't belong at all, where did it come from? I think that's just a misunderstanding.

I tend to write excessively (hence the user title), so it's easy to overlook something, and I think you missed one point. I believe you need the SQL string to be this:

str = "SELECT ContactID FROM Lease WHERE ContactID = ?"

What I had in there with @p1 would work for SQL Server, but Access doesn't use named parameters, so I think you HAVE to use ?. That is probably the source of the error. You don't get the error until the SQL is executed, so it would look fine until the ExecuteScalar call....but more on that in a bit.

I believe that the AddWithValue line is ok, even though @p1 was removed from the SQL string. AddWithValue is a bit odd, and makes me kind of queasy, so I'm not surprised that you don't understand that. Essentially, the command object has a parameters collection. This is pretty much like the arguments to a function, except that there can be any number of them, and the types don't matter much. What AddWithValue is doing is effectively "add a parameter with this name (the first argument), and this value (the second argument)." You supply a name for the parameter in the first argument, and I supply @p1. That's a string, though, not a variable, so what happens is that when the command object parses the sql, it replaces @p1 with the second argument (ContactID.Text). What is nice about this is that it won't JUST replace @p1 with the second arguement, it will also format it correctly. This means that you can pass in whatever type is needed, and it will be formatted into the type needed in the query. For instance, in Access queries, dates have to be wrapped in # tags. When using AddWithValue, you can just supply a date variable, because the command will properly wrap it in # tags. You don't have to do it. Another, and even bigger, advantage is that the variable will be cleaned. If you concatenate in the contents of a textbox, a malicious person could write just the right thing into the textbox and wipe out your database. That's what's known as a SQL injection, and can occur if the user enters valid SQL into the textbox in such a way that when it gets concatenated in, it does something bad. I think that in your initial post, where you were concatenating in the contents of the textbox, all that would have to be in the textbox would be:

';DROP TABLE Lease

and your whole lease table would be gone. That's not good (though there is a funny cartoon by xkcd about that). AddWithValue will format the input to prevent such things from getting through.

So, you might be wondering how "@p1" can work once you change the SQL statement so that there is no @p1 in the statement. I think the way it works in Access is that the first parameter replaces the first ? in the SQL statement, and the name you supply for the parameter is ignored. In SQL Server, the name is not ignored, so you can supply the parameters in any order as long as you have the names correct.

I totally overlooked the str thing. That's what you get for tinkering with code. There are two ways you can fix it:

1) Assign str to the CommandText of the Command object:

str = "SELECT ContactID FROM Lease WHERE ContactID = ?"
cmd.Commandtext = str

or

2) Get rid of str entirely:

cmd.CommandText = "SELECT ContactID FROM Lease WHERE ContactID = ?"


Finally:
Don't leave the catch block empty. That will cause exceptions to simply vanish. At the very least, add something like:

Windows.Forms.Messagebox.Show(ex.Message,"Error")

because if an error happens, you need to know about it, and will want to see the error message at the very least.

[Longbow22]

Woot that cleared things up.

I did have the commands in the If cmd.ExecuteScalar IsNot Nothing Then
It was opening the form to a new record instead of to the selected Id.

It does seem odd that I have connected to my database and setup all my datasets, yet I still have to make a connection to the database?

Thank you very much for spending the time to answer my questions.

I have plenty more where they came from...

Thanks Again

LB

[Shaggy Hiker]

If you have connected to the database and setup all the datasets, you may not need to make that connection at all. If the data you need to check is already in the dataset, then you can check the dataset, instead (there is a SELECT method for the datatable, though it is a bit different from a SELECT query).

However, if the data you need to check is not already in the dataset, then you could bring it into the dataset, but that would be slower than an ExecuteScalar call, which is the fastest database interaction you can get. So, that's a trade to consider. If you don't have the data and need it just this once, then ExecuteScalar is probably better. If you don't have the data, but will need it repeatedly, then bringing it into the dataset and working with it there would make sense.

[Longbow22]

Nah its working like a champ the way it is.
I've moved on to an update query, I figure if I get an example of each SELECT, UPDATE, DELETE and understand them, I'll be cooking with gas.

I have the below now, no errors, but it updated ALL my records to status 3...oops.
I think its in the WHERE clause

        Using conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Rent ROll\Tenant Tracker 2011_be.mdb;Persist Security Info=False")
            Using cmd As OleDb.OleDbCommand = conn.CreateCommand
                Try
                    conn.Open()
                    cmd.CommandText = "UPDATE Apartments Set Status = ? WHERE AptID = AptID"
                    cmd.Parameters.AddWithValue("Status", 3)
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    Windows.Forms.MessageBox.Show(ex.Message, "Error")
                End Try
            End Using
        End Using
    End Sub

LB

[Longbow22]

Thought I had it Figured it out.
Its setting the Status to Me.AptID.SelectedValue which in this case is 25 instead of the hard coded 3

            Using cmd As OleDb.OleDbCommand = conn.CreateCommand
                Try
                    conn.Open()
                    cmd.CommandText = "UPDATE Apartments Set Status = ? " & "WHERE AptID = @AptID"
                    cmd.Parameters.AddWithValue("@AptID", Me.AptID.SelectedValue)
                    cmd.Parameters.AddWithValue("Status", 3)
                    Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
                    If rowsAffected > 0 Then
                        MsgBox(Me.AptID.Text & " Status Updated", MsgBoxStyle.OkOnly, "Status Updated")
                    End If
                Catch ex As Exception
                    Windows.Forms.MessageBox.Show(ex.Message, "Error")
                End Try
            End Using
        End Using

[Shaggy Hiker]

In Access, the parameters will be used in the order they are supplied. Since you supplied Me.AptID.SelectedValue as the first parameter, that is what was used. The second parameter (3) was ignored. It's just a drawback of Access, and you will have to live with it. For every ? in the query, supply just one value, and supply them in the order that the ? are found in the query.

By the way, it seems like you will soon get into doing updates or inserts in a loop. One thing that tripped me up pretty well is that the parameters collection is just like any other collection (which is no surprise), so if you add one parameter the first time through a loop, then loop a second time adding another parameter...well, the first one was still there, so it gets used, and the second one is ignored.

The lesson to learn is that it is a good habit to call

cmd.Parameters.Clear

after using a command object with parameters. This isn't necessary if you are just going to dispose of the command object, which is what you have done so far, but if you are going to re-use the command object with different SQL, or with the same SQL in a loop, you want to clear the parameters after each use (after the cmd.ExecuteWhatever statement).