[RESOLVED] SQL Quiery Try/Catch Question

[smilbuta]

Hello community, I have a block of code i wish to place a try statment around. I basicaly want to catch the SQL statment in case it returns an item not found. Im fairly new to VB and have only used try catch statments during some tutorial material. please no flaming on the noobish layout and syntax.

Here is the code, can some one show me where i would but the try/catch??

Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCriteriaSearch.Click
        Dim serialSelect As String = "equipment_serial_number"
        Dim cfSelect As String = "equipment_cf_tag"
        Dim sqlColumn As String
        Dim searchCriteria As String
 
        If cboCriteria.SelectedIndex = 0 Then
            sqlColumn = serialSelect
        Else
            sqlColumn = cfSelect
        End If
        searchCriteria = txtCriteriaSearch.Text.ToUpper
 
        Dim mySqlConnection As SqlConnection = New SqlConnection(getConnectionString)
        Dim mySqlCommand As SqlCommand = New SqlCommand()
        Dim mySqlReader As SqlDataReader
        mySqlConnection.Open()
        mySqlCommand.Connection = mySqlConnection
        mySqlCommand.CommandText = ( _
        "SELECT EQ_Equipment.equipment_serial_number, " & _
        "EQ_Equipment.equipment_cf_tag, " & _
        "EQ_Equipment.equipment_description, " & _
        "EQ_Equipment.equipment_warranty, " & _
        "EQ_Equipment.item_class_id, " & _
        "EQ_Equipment.location_id, " & _
        "EQ_Equipment.status_id, " & _
        "EQ_Equipment.user_id " & _
        "FROM EQ_Equipment " & _
        "WHERE " & sqlColumn & " = '" & searchCriteria & "'")
 
        mySqlReader = mySqlCommand.ExecuteReader()
        While mySqlReader.Read()
            txtSerialNumber.Text = mySqlReader.GetString(0)
            txtTag.Text = mySqlReader.GetString(1)
            txtNotes.Text = mySqlReader.GetString(2)
            dtpWarranty.Value = mySqlReader.GetDateTime(3)
            cboClassType.SelectedValue = mySqlReader.GetInt32(4)
            cboLocation.SelectedValue = mySqlReader.GetInt32(5)
            cboItemStatus.SelectedValue = mySqlReader.GetInt32(6)
            cboUserList.SelectedValue = mySqlReader.GetInt32(7)
        End While
 
        mySqlConnection.Close()
        variableUpdate()
    End Sub

[szlamany]

When the WHERE clause finds no rows it does not return an error.

It simply has no data rows.

All the metadata - column count - column definitions - column names come back from SQL.

Simply no rows.

If you step through that code in debug mode doesn't it simply not enter the loop when you specify a SERIAL NUMBER that does not match?

[smilbuta]

Yes your are correct. I guess i was concerned about actualy throwing up an exception to inform the user that nothing was returned, thus indicating an empty search due to the value not existing.

[GaryMazzone]

Maybe like this:

mySqlReader = mySqlCommand.ExecuteReader()
        If mySQLReader.HasRows() then
            While mySqlReader.Read()
                txtSerialNumber.Text = mySqlReader.GetString(0)
                txtTag.Text = mySqlReader.GetString(1)
                txtNotes.Text = mySqlReader.GetString(2)
                dtpWarranty.Value = mySqlReader.GetDateTime(3)
                cboClassType.SelectedValue = mySqlReader.GetInt32(4)
                cboLocation.SelectedValue = mySqlReader.GetInt32(5)
                cboItemStatus.SelectedValue = mySqlReader.GetInt32(6)
                cboUserList.SelectedValue = mySqlReader.GetInt32(7)
            End While
      else
          messagebox.show("No Data found that matched the search criteria.","No Data")
      end if

[szlamany]

@GM - good stuff - I was just going into HELP to find out what the syntax was!

[kleinma]

you should also wrap up most of that routine in try/catch blocks anyway. There is any number of reasons an error you aren't expecting could occur. A prime example is if the database is offline when someone tries to access it.

This will also allow you to clean up and dispose of your DB objects in a finally block of the try/catch

[GaryMazzone]

I've started using that in all calls for my datareader. I pass a SQL statement to a class that returns a type of datareader then see if there are any rows in the returned item.

[smilbuta]

Worked like a champ!! I did not know about the method "HasRows" Thanx! and i also added the Try catch for extra measure. I tested it and it works perfect.

Try
            mySqlReader = mySqlCommand.ExecuteReader()
            If mySqlReader.HasRows Then
                While mySqlReader.Read()
                    txtSerialNumber.Text = mySqlReader.GetString(0)
                    txtTag.Text = mySqlReader.GetString(1)
                    txtNotes.Text = mySqlReader.GetString(2)
                    dtpWarranty.Value = mySqlReader.GetDateTime(3)
                    cboClassType.SelectedValue = mySqlReader.GetInt32(4)
                    cboLocation.SelectedValue = mySqlReader.GetInt32(5)
                    cboItemStatus.SelectedValue = mySqlReader.GetInt32(6)
                    cboUserList.SelectedValue = mySqlReader.GetInt32(7)
                End While
            Else
                MessageBox.Show("No Item found. Check Serial number or CFTag")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try