[RESOLVED] [2005] Checking for NULL Values upoon SQL Statment Call

[smilbuta]

Hello community...

I have a VB.net app that makes a sql call filling in a form.

I want to handle null values but i keep getting errors.

here is the block of code.

    Private Sub searchItem()
        Dim serialSelect As String = "equipment_serial_number"
        Dim cfSelect As String = "equipment_cf_tag"
        Dim sqlRowSelect As String = ""
        Dim searchCriteria As String = ""

        Dim classValue As String = CStr(cboClass.SelectedValue)
        Dim num As Integer = 1

    

        If cboCriteria.SelectedIndex = 0 Then
            sqlRowSelect = serialSelect
        Else
            sqlRowSelect = cfSelect
        End If
        searchCriteria = txtCriteriaValue.Text.ToUpper

        Dim mySqlConnection As SqlConnection = New SqlConnection(getConnectionString)
        Dim mySqlCommand As SqlCommand = New SqlCommand
        Dim mySqlReader As SqlDataReader = Nothing

        mySqlConnection.Open()
        mySqlCommand.Connection = mySqlConnection
        mySqlCommand.CommandText = ( _
        "SELECT equipment_serial_number, equipment_cf_tag, equipment_description, equipment_warranty, item_class_id, location_room_id, status_id, user_id " & _
        "FROM EQ_Equipment " & _
        "WHERE " & sqlRowSelect & " = '" & searchCriteria & "'")
        '**Read and apply values from DB**
        Try
            mySqlReader = mySqlCommand.ExecuteReader
            If mySqlReader.HasRows Then
                While mySqlReader.Read
                    txtSerialNumber.Text = mySqlReader.GetString(0)


                    txtCfTag.Text = mySqlReader.GetString(1)

 --------------> If IsDBNull(mySqlReader.GetString(2)) Then
                        MessageBox.Show("yes im null")
                    Else
                        'txtNotes.Text = mySqlReader.GetString(2)
                    End If

                    dtpWarranty.Value = mySqlReader.GetDateTime(3)
                    cboClass.SelectedValue = mySqlReader.GetInt32(4)
                    cboLocation.SelectedValue = mySqlReader.GetInt32(5)
                    cboStatus.SelectedValue = mySqlReader.GetInt32(6)
                    cboUserName.SelectedValue = mySqlReader.GetInt32(7)
                    lblBoxDept.Text = getDepartment(mySqlReader.GetInt32(7))
                End While
                previousSerial = txtSerialNumber.Text

                If classValue = num.ToString Then
                    getBigBrother()
                End If

                searchfound()
                variableUpdate()

            Else

                If cboCriteria.SelectedIndex = 0 Then
                    MessageBox.Show("No item found with serial number: " & txtCriteriaValue.Text, "no record", MessageBoxButtons.OK)
                Else
                    MessageBox.Show("No item found with property decal " & txtCriteriaValue.Text, "no record", MessageBoxButtons.OK)
                End If
                clearNonEditContent()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        mySqlConnection.Close()
    End Sub

THE area in bold is what im trying to handle but i suppose if i get it working i can use it for any field. the error im getting is:

"Data is Null. This method or property cannot be called on null values."

So im stumped.. I tested the code with a check to see what it was pulling from the database using getsqlvalue and it says "Null".. so im stuck...

Any help would be appreciated.

[kleinma]

The SQL reader has it's own IsDBNull method. Use that instead

    If mySqlReader.IsDBNull(2) Then

    End If

[smilbuta]

DOH!.. thanx...

I actualy used that but i did not put my column refrence "(2)" at the end of the method.

Again thanx...

[kleinma]

you can also check a value to see if it equals system.dbnull.value, but the method I already posted is your best bet in this scenario when using a datareader.