DataRow Access Problem

[nlindley7]

I am currently getting a "NullReferenceException: object reference not set to an instance of an object" when I try to check a DataRow("columnname") to see if it is a DBNull.Value. The search works fine when you put a value the is in the DB, but when you try to search for an invalid record, it gives me the NullReferenceError. I tried to do a New DataRow, as most NullRefs i've came across stem from missing the New keyword, but it looks like thats not the right way to go. Any suggestions would be appreciated. The line of code that gives the error is "If drCustomersSearch("MortgageNumber") Is DBNull.Value Then" SqlConnection1.Open() SqlDataAdapter1.Fill(DsNameandAddress, "NameandAddress") SqlConnection1.Close() If txtSearch.Text = "" Then MessageBox.Show("You must type in a Mortgage Number" & Chr(13) & " to Search For. Try search again.", "Missing Morgage Number", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) Else Dim dtCustomersUpdate As New DataTable Dim drCustomersSearch As DataRow Dim drCustomersSearchNoRow As DataRow dtCustomersUpdate = DsNameandAddress.Tables.Item("NameandAddress") drCustomersSearch = dtCustomersUpdate.Rows.Find(txtSearch.Text) If drCustomersSearch("MortgageNumber") Is DBNull.Value Then MessageBox.Show("Your entry is not a valid Mortgage number." & Chr(13) & Chr(13) & "Try search again.", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) txtSearch.Focus() txtMortgageNumber.Text = "" Else : txtMortgageNumber.Text = drCustomersSearch("MortgageNumber") End If Nathan Lindley, .NET Aficionado

[Kschuler]

Perhaps it is not the field "MortgageNumber" that is in error, but the drCustomersSearch that is. You should do a check after this statement: drCustomersSearch = dtCustomersUpdate.Rows.Find(txtSearch.Text) To make sure that drCustomersSearch actually found something. Perhaps that was the problem. Hope this helps.

[nlindley7]

Thanks for the quick response kschuler:-D This line of code: If drCustomersSearch("MortgageNumber") Is DBNull.Value Then is where the error occurs. That line is testing to see if the PK (MortgageNumer) of the table (NamesandAddress) is available. The goal is to have it come back null when there is no record, but instead I get the NullReferenceExc. The search works fine when you use a valid MortgageNumber, but for some reason it can't return a row with nulls. Am I missing something to be able to handle a row with nulls? Again thanks for the response. Nathan Lindleyy, .NET Aficionado

[Kschuler]

I didn't think anything else was required to be able to check if something was DBNull.Value. I would try debugging. Put a break point on that statement and see what value is says drCustomersSearch("MortgageNumber") really is...then check what just drCustomersSearch's value is...If that doesn't shed some light on it, you might want to put the whole thing in a try block and in the catch portion put a messagebox to display the exception's message. Sometimes they have better error message there. Sorry I can't be more helpful.

[nlindley7]

Thanks again Kschuler. I did the debugging and the values for the drCustomersSearch comes back as Nothing. I guess when it cant find the row it returns Nothing as the value. I did a check for Nothing, but still got the NullReferenceException. Not sure how to work around this. Might have to rethink the whole approach. Nathan Lindley, .NET Aficionado

[Kschuler]

When you say that you are checking for nothing, are you checking just the DataRow object? I think your code should look something like this:

    dtCustomersUpdate = DsNameandAddress.Tables.Item("NameandAddress")

    drCustomersSearch = dtCustomersUpdate.Rows.Find(txtSearch.Text)

    If drCustomersSearch Is Nothing Then
        MessageBox.Show("Customer could not be found.")
    Else

        If drCustomersSearch("MortgageNumber") Is DBNull.Value Then
            MessageBox.Show("Your entry is not a valid Mortgage number." & Chr(13) & Chr(13) & "Try search again.", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtSearch.Focus()
            txtMortgageNumber.Text = ""

        Else : txtMortgageNumber.Text = drCustomersSearch("MortgageNumber")
        End If
    End If

[Dave Kreskowiak]

The problem is you're trying to get the value of a row that doesn't exist. Since your call to dtCustomersUpdate.Rows.Find() returned Nothing, you have to check if drCustomersSearch is Nothing before you try and do anything with it.

drCustomersSearch = dtCustomersUpdate.Rows.Find(txtSearch.Text)
**If Not drCustomersSearch Is Nothing Then**
    If drCustomersSearch("MortgageNumber") Is DBNull.Value Then
        MessageBox.Show(_blah, blah, blah_)
        txtSearch.Focus()
        txtMortgageNumber.Text = ""
    Else
        txtMortgageNumber.Text = drCustomersSearch("MortgageNumber")
    End If
**Else
    ' The search didn't find anything, handle this case...
End If**

It's standard practice to check to see if any object actually exists before you try and use it.

Dave Kreskowiak Microsoft MVP - Visual Basic

[nlindley7]

Thanks Kschuler, Dave! I had checked it for being Nothing before and was still getting the NullReference, but when i checked if Not Nothing, it seemed to do the trick. Thanks for the advice. Nathan Lindley, .NET Aficionado