Check if Record Exists? Easier way then this???

Ok I am new to VB.Net.  (I do fairly well in VB 6)

There has to be an easier way to check to see if a record exists...  The following code "Works" but seems like there should be something simpler.

==== start of code ====
        Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim ds As New DataSet
        Dim strSQL As String
        strSQL = "Select * from invoice_header where vendor_number = " & cbVendorNumber.SelectedValue & _
        " and invoice_number = '" & txtInvoiceNumber.Text & "'"
        conn.ConnectionString = myConnString
        Try
            conn.Open()
            Try
                ' Data Adapter for Main Datagrid
                Dim myda As MySqlDataAdapter
                myda = New MySqlDataAdapter(strSQL, conn)
                ds = New DataSet
                myda.Fill(ds, "Invoice_Header")
                Select Case ds.Tables("Invoice_Header").Rows.Count
                    Case 0
                        MsgBox("The table is empty")
                    Case 1
                        MsgBox("You have only 1 record")
                    Case Else
                        'The execution will be here if you have 2 or more records
                End Select
            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
==== end of code ====
LVL 3
ScottParkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
       Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim strSQL As String

        conn.connectionstring = myConnString
        myCommand.commandtext = "Select * from invoice_header where vendor_number = " & cbVendorNumber.SelectedValue & _
        " and invoice_number = '" & txtInvoiceNumber.Text & "'"
        myCommand.connection = conn
        Try
            myCommand.connection.Open()
            dim i as integer = cmd.executenonquery
                Select Case i
                    Case 0
                        MsgBox("The table is empty")
                    Case 1
                        MsgBox("You have only 1 record")
                    Case Else
                        'The execution will be here if you have 2 or more records
                End Select
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If mycommand.connection.State <> ConnectionState.Closed Then myCommand.connection.Close()
        End Try
ScottParkerAuthor Commented:
BriCrowe,

After changeing you line of code from "dim i as integer = cmd.executenonquery"
to "dim i as integer = myCommand.executenonquery"

I tested.  It failed.  Even if the record existed "i" was always comming back as Zero.
DabasCommented:
I would have supplied a similar answer to BriCrowe's
I suggest you set a breakpoint on the excuteNonQuery line and QuickWatch myCommand.CommandText to see if something else is not right.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

kishore3576Commented:
       Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim dr As New DataReader
        Dim strSQL As String
        strSQL = "Select * from invoice_header where vendor_number = " & cbVendorNumber.SelectedValue & _
        " and invoice_number = '" & txtInvoiceNumber.Text & "'"
        conn.ConnectionString = myConnString
        Try
            conn.Open()
            Try
                myCommand.Connection = conn
                myCommand.CommandText = strSQL
                dr = myCommand.ExecuteReader
                if dr.Read then
                          MsgBox ("Table is not empty")
                else
                          MsgBox ("Table is empty")
                endif
            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
==== end of code ====
amyhxuCommented:
I don't think SqlCommand.ExecuteNonQuery will work in this case. Comment from MSDN: "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1." Here the command is "SELECT" and will return -1.

Actually, I think the way you used is the best one because you mentioned "Data Adapter for Main Datagrid", so you will need the dataset to fill the datagrid anyway. I don't see why you would check if the records exist first and fill the dataset if there are records. I would have used the same approach you used.

In some other cases, if you only need to check if the command returns records, you can use the sqlDataReader which has a HasRows property:
       Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim dr As New DataReader
        Dim strSQL As String
        strSQL = "Select * from invoice_header where vendor_number = " & cbVendorNumber.SelectedValue & _
        " and invoice_number = '" & txtInvoiceNumber.Text & "'"
        conn.ConnectionString = myConnString
        Try
            conn.Open()        
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
            Exit Sub
        End Try

            Try
                myCommand.Connection = conn
                myCommand.CommandText = strSQL
                dr = myCommand.ExecuteReader
                If dr.HasRows then
                          MsgBox ("The table has record(s)")
                          'If you want to check whether the command returns 1 row or more rows, take out the apostrophe for the following lines:
                          'dr.Read()  
                          'If dr.Read() Then
                               'The execution will be here if you have 2 or more records
                          'Else
                               'MsgBox("You have only 1 record")
                          'EndIf
                Else
                          MsgBox ("The table is empty")
                EndIf
                dr.Close()
            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try

            conn.Close()
amyhxuCommented:
A little modification to the code above:
       Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim dr As New DataReader
        Dim strSQL As String
        strSQL = "Select * from invoice_header where vendor_number = " & cbVendorNumber.SelectedValue & _
        " and invoice_number = '" & txtInvoiceNumber.Text & "'"
        conn.ConnectionString = myConnString
           
            Try
                conn.Open()
                myCommand.Connection = conn
                myCommand.CommandText = strSQL
                dr = myCommand.ExecuteReader
                If dr.HasRows then
                          MsgBox ("The table has record(s)")
                          'If you want to check whether the command returns 1 row or more rows, take out the apostrophe for the following lines:
                          'dr.Read()  
                          'If dr.Read() Then
                               'The execution will be here if you have 2 or more records
                          'Else
                               'MsgBox("You have only 1 record")
                          'EndIf
                Else
                          MsgBox ("The table is empty")
                EndIf
                dr.Close()
                conn.Close()

            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try

           

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DabasCommented:
amyhxu,
Good point!

Dabas
ScottParkerAuthor Commented:
amyhxu,

The comment line about the datagrid was an accident.  Left over from a copy paste..
I like your datareader approach better then what I had.  It just looks "Cleaner". (your 2nd example)

I guess I will just have to get used to haveing to write more lines of code to do the same things in VB.Net that I used to do in VB 6.  
ScottParkerAuthor Commented:
I gave everyone some points because you all had good points.
kishore3576Commented:
amyhxu,

It was a great answer. I never knew that even if dr.Read() is false there is a row in the datareader. Is this the row containing the column headers without any data or does it have the first and the only row as well.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.