Proper Code help

[callydata]

Here's my code for a "find" customer button. but the only way it works is to add error handling code. I need some help with the proper way to code this.
What I need to happen is when a "wrong" ID number is entered, it shows a Message box. And if the right number is entered, then display the customers info. So far only the latter works without the error handling.
The ID number is a key column in SQL, and when a wrong number is entered it returns an error message saying the "wrong" number is a NULL value.

Private Sub btnQVSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
        If txtNmbNumber.Text = "" Or txtNmbNumber.Text = "NMB" Then
            MessageBox.Show("Please enter in a NMB Number", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtNmbNumber.Focus()
            txtNmbNumber.SelectAll()
            txtNmbNumber.ReadOnly = False
            btnFind.Enabled = True
            Exit Sub
        End If
        'popluate all fields that relate to id number entered
        SqlDataAdapter1.Fill(DsCustomers1, "Customer")
        SqlDataAdapter2.Fill(DsDrafts1, "Draft")
        SqlDataAdapter3.Fill(DsPayables1, "Payables")
        SqlDataAdapter4.Fill(DsAchNewbie1, "Ach")
        SqlDataAdapter5.Fill(DsPayableTrans1, "Payables_Transaction")
 
        Dim dtCustomersUpdate As DataTable = DsCustomers1.Tables.Item("Customer")
        Dim dtDraftsUpdate As DataTable = DsDrafts1.Tables.Item("Draft")
        Dim dtPayablesUpdate As DataTable = DsPayables1.Tables.Item("Payables")
        Dim dtAchUpdate As DataTable = DsAchNewbie1.Tables.Item("Ach")
        Dim dtPayablesTransUpdate As DataTable = DsPayableTrans1.Tables.Item("Payables_Transaction")
 
        drCustomersUpdate = dtCustomersUpdate.Rows.Find(txtNmbNumber.Text)
        drDraftsUpdate = dtDraftsUpdate.Rows.Find(txtNmbNumber.Text)
        drPayablesUpdate = dtPayablesUpdate.Rows.Find(txtNmbNumber.Text)
        drPayablesTransUpdate = dtPayablesTransUpdate.Rows.Find(txtNmbNumber.Text)
        drAchUpdate = dtAchUpdate.Rows.Find(txtNmbNumber.Text)
        Dim Msg As String
        'If an error occurs, construct an error message.
        On Error Resume Next
        ' MessageBox.Show("Please enter in a valid NMB Number", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) ' Defer error handling.
        Err.Clear()
        'If the number is correct
        'Customers Info Tab
        If txtNmbNumber.Text = drCustomersUpdate("IDNumber") Then
            ' txtNmbNumber.ReadOnly = True
            btnFind.Enabled = False
            ' Else
            '  MessageBox.Show("Please enter in a valid NMB Number", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            ' txtNmbNumber.Focus()
            ' txtNmbNumber.SelectAll()
            '  txtNmbNumber.ReadOnly = False
            '  btnFind.Enabled = True
        End If
        If drCustomersUpdate("Agent") Is DBNull.Value Then
            txtAgent.Text = ""
        Else : txtAgent.Text = drCustomersUpdate("Agent")
        End If
        txtLastName.Text = drCustomersUpdate("LastName")
        txtFirstName.Text = drCustomersUpdate("FirstName")
        txtSsn1.Text = drCustomersUpdate("SSN1")
        'If field is null... NOTE: fields that are required should not be null so they won't need this code
        If drCustomersUpdate("LastName2") Is DBNull.Value Then
            txtLastNameSpouse.Text = ""
        Else : txtLastNameSpouse.Text = drCustomersUpdate("LastName2")
        End If
        If drCustomersUpdate("FirstName2") Is DBNull.Value Then
            txtFirstNameSpouse.Text = ""
        Else : txtFirstNameSpouse.Text = drCustomersUpdate("FirstName2")
        End If
        If drCustomersUpdate("SSN2") Is DBNull.Value Then
            txt2SSN.Text = ""
        Else : txt2SSN.Text = drCustomersUpdate("SSN2")
        End If
        If drCustomersUpdate("Address") Is DBNull.Value Then
            txtAddress.Text = ""
        Else : txtAddress.Text = drCustomersUpdate("Address")
        End If
        If drCustomersUpdate("City") Is DBNull.Value Then
            txtCity.Text = ""
        Else : txtCity.Text = drCustomersUpdate("City")
        End If
        If drCustomersUpdate("State") Is DBNull.Value Then
            txtState.Text = ""
        Else : txtState.Text = drCustomersUpdate("State")
        End If
        If drCustomersUpdate("ZipCode") Is DBNull.Value Then
            txtZipCode.Text = ""
        Else : txtZipCode.Text = drCustomersUpdate("ZipCode")
        End If
        If drCustomersUpdate("Daytime") Is DBNull.Value Then
            txtDaytime.Text = ""
        Else : txtDaytime.Text = drCustomersUpdate("Daytime")
        End If
        If drCustomersUpdate("Evening") Is DBNull.Value Then
            txtEvening.Text = ""
        Else : txtEvening.Text = drCustomersUpdate("Evening")
        End If
        If drCustomersUpdate("Notes") Is DBNull.Value Then
            txtNotes.Text = ""
        Else : txtNotes.Text = drCustomersUpdate("Notes")
        End If
        'Drafts Info Tab
        txtNmbNumberDraft.Text = txtNmbNumber.Text
        drDraftsUpdate("IDNumber") = txtNmbNumberDraft.Text
        txtFinancialInt.Text = drDraftsUpdate("Bank")
        If drDraftsUpdate("BankPhone") Is DBNull.Value Then
            txtDraftPhone.Text = ""
        Else : txtDraftPhone.Text = drDraftsUpdate("BankPhone")
        End If
        If drDraftsUpdate("RoutingNumber") Is DBNull.Value Then
            txtDraftRoute.Text = ""
        Else : txtDraftRoute.Text = drDraftsUpdate("RoutingNumber")
        End If
        txtDraftSaves.Text = drDraftsUpdate("SavingsORChecking")
        'Payables Tab
        '1
        txtNmbNumberPayables.Text = txtNmbNumber.Text
        drPayablesUpdate("IDNumber") = txtNmbNumberPayables.Text
        drPayablesTransUpdate("IDNumber") = txtNmbNumberPayables.Text
        txtPayable.Text = drPayablesUpdate("PayableCompany")
        txtNoPayables.Text = drPayablesUpdate("NumberPayables")
        txtName.Text = drPayablesUpdate("NameOnAccount")
        If drPayablesUpdate("PayablesPhone") Is DBNull.Value Then
            txtPayablePhone.Text = ""
        Else : txtPayablePhone.Text = drPayablesUpdate("PayablesPhone")
        End If
        If drPayablesUpdate("PayableAddress") Is DBNull.Value Then
            txtPayableAddress.Text = ""
        Else : txtPayableAddress.Text = drPayablesUpdate("PayableAddress")
        End If
        If drPayablesTransUpdate("Starting2") Is DBNull.Value Then
            txtStartDateB.Text = ""
        Else : txtStartDateB.Text = drPayablesTransUpdate("Starting2")
        End If
        If drPayablesTransUpdate("Ending2") Is DBNull.Value Then
            txtEndDateB.Text = ""
        Else : txtEndDateB.Text = drPayablesTransUpdate("Ending2")
        End If
        If drPayablesTransUpdate("Amount3") Is DBNull.Value Then
            txtPaymentC.Text = ""
        Else : txtPaymentC.Text = drPayablesTransUpdate("Amount3")
        End If
        If drPayablesTransUpdate("Period3") Is DBNull.Value Then
            txtPeriodC.Text = ""
        Else : txtPeriodC.Text = drPayablesTransUpdate("Period3")
        End If
        If drPayablesTransUpdate("Starting3") Is DBNull.Value Then
            txtStartDateC.Text = ""
        Else : txtStartDateC.Text = drPayablesTransUpdate("Starting3")
        End If
        If drPayablesTransUpdate("Ending3") Is DBNull.Value Then
            txtEndDateC.Text = ""
        Else : txtEndDateC.Text = drPayablesTransUpdate("Ending3")
        End If
        'Ach Tab
        txtNmbNumberAch.Text = txtNmbNumber.Text
        drAchUpdate("IDNumber") = txtNmbNumberAch.Text
        If drAchUpdate("Amount") Is DBNull.Value Then
            txtAchAmount.Text = ""
        Else : txtAchAmount.Text = drAchUpdate("Amount")
        End If
        If drAchUpdate("FeeCode") Is DBNull.Value Then
            txtFeeCode.Text = ""
        Else : txtFeeCode.Text = drAchUpdate("FeeCode")
        End If
        If drAchUpdate("Starting") Is DBNull.Value Then
            txtStarting.Text = ""
        Else : txtStarting.Text = drAchUpdate("Starting")
        End If
        If drAchUpdate("Ending") Is DBNull.Value Then
            txtEnding.Text = ""
        Else : txtEnding.Text = drAchUpdate("Ending")
        End If
        If drAchUpdate("Amount1") Is DBNull.Value Then
            txtAchAmount2.Text = ""
        Else : txtAchAmount2.Text = drAchUpdate("Amount1")
        End If
        If drAchUpdate("FeeCode1") Is DBNull.Value Then
            txtFeeCode2.Text = ""
        Else : txtFeeCode2.Text = drAchUpdate("FeeCode1")
        End If
        If drAchUpdate("Starting1") Is DBNull.Value Then
            txtStarting2.Text = ""
        Else : txtStarting2.Text = drAchUpdate("Starting1")
        End If
        If drAchUpdate("Ending1") Is DBNull.Value Then
            txtEnding2.Text = ""
        Else : txtEnding2.Text = drAchUpdate("Ending1")
        End If
        If drAchUpdate("Amount2") Is DBNull.Value Then
            txtAchAmount3.Text = ""
        Else : txtAchAmount3.Text = drAchUpdate("Amount2")
        End If
        If drAchUpdate("FeeCode2") Is DBNull.Value Then
            txtFeeCode3.Text = ""
        Else : txtFeeCode3.Text = drAchUpdate("FeeCode2")
        End If
        If drAchUpdate("Starting2") Is DBNull.Value Then
            txtStarting3.Text = ""
        Else : txtStarting3.Text = drAchUpdate("Starting2")
        End If
        If drAchUpdate("Ending2") Is DBNull.Value Then
            txtEnding3.Text = ""
        Else : txtEnding3.Text = drAchUpdate("Ending2")
        End If
    End Sub

[/QUOTE]

[Aspnot]

A few things:

1 - What is the error that you are getting?
2 - What line is the error happening?
3 - What are your SQLCommand.CommandText properties?

[callydata]

1. An unhandled exception of type 'System.NullReferenceException' occurred in BiWeeklyMock.exe

Additional information: Object reference not set to an instance of an object.
I keep getting this error on other things throughout my project. What am I doing wrong?

2.

If txtNmbNumber.Text = drCustomersUpdate("IDNumber") Then
            ' txtNmbNumber.ReadOnly = True
            btnFind.Enabled = False
            ' Else
            '  MessageBox.Show("Please enter in a valid NMB Number", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            ' txtNmbNumber.Focus()
            ' txtNmbNumber.SelectAll()
            '  txtNmbNumber.ReadOnly = False
            '  btnFind.Enabled = True
        End If

3. Huh?

[conipto]

Also, the Try.. Catch.. Finally block is better to use than the On Error and labels. Also, are you using different tables and SQL Queries for each column? Why not build a join query and use one big statement?

Bill

[callydata]

I have had very bad luck with join statements, when using the data adapter wizard. All of my tables have an idenity key of an ID number and it hasn't liked that at all. How do I program the SQLDataAdapter without the wizard, and using a join statement?

[conipto]

Do the identity keys match up throughout the tables?

Bill

[callydata]

Yes, all the same IDs.

[jmcilhinney]

Whenever you get a null reference exception it means that you have declared a variable but not created an object. This is the basis of OOP. When dealing with classes, the variable IS NOT the object. The variable contains the memory address of the object. If you declare a variable it is at first a null reference, i.e. it contains memory address zero and referes to no object. You have to create an object, usually using the "New" keyword, and assign it to the variable, which will then contain a valid memory address and refere to the object you created.

It also sounds like you need to read up a bit on SQL. There is no "bad luck" with joins. If you write the SQL code correctly they will work, whatever method you use to create your data access objects, including the Data Adapter Configuration Wizard. Just note that the wizard cannot create the other statements for you when the query is a join because it has no idea which table to update, so you need to write the Insert, Update and Delete statements yourself in that instance.

[conipto]

With unique ID numbers across tables, a join statement is likely to work well. AS jmcilhinney say - you should read up on SQL a bit.

http://www.w3schools.com/sql/sql_intro.asp

This is what I learned it with, pretty good from the ground up.

Bill

[callydata]

Just note that the wizard cannot create the other statements for you when the query is a join because it has no idea which table to update, so you need to write the Insert, Update and Delete statements yourself in that instance.

Stupid question....
where do I put the INSERT, UPDATE, and DELETE statements within the VB code?
After I call the SQL adapter fill?
cally