Object reference not set to an instance of object

Question

I have a function like this

public void GetTablesWithUpperCaseName()
{
   SqlConnectionStringBuilder objConnectionString = new SqlConnectionStringBuilder();
   objConnectionString.DataSource = txtHost.Text;
   objConnectionString.UserID = txtUsername.Text;
   objConnectionString.Password = txtPassword.Text;
   objConnectionString.InitialCatalog = Convert.ToString(cmbDatabases.SelectedValue);

   SqlConnection sConnection = new SqlConnection(objConnectionString.ConnectionString);

   //To Open the connection.
   sConnection.Open();

   //Query to select table_names that have their names in uppercase letters.
   string selectTablesWithUppercaseName = @"SELECT 
                                              NAME
                                            FROM 
                                              sysobjects 
                                            WHERE 
                                              UPPER(name) COLLATE Latin1_General_BIN = name COLLATE Latin1_General_BIN 
                                              AND 
                                                 OBJECTPROPERTY(ID,N'IsTable')=1
                                              AND 
                                                 OBJECTPROPERTY(ID,N'IsMSShipped')=0 ";
   //Create the command object
   SqlCommand sCommand = new SqlCommand(selectTablesWithUppercaseName, sConnection);

   try
   {
       //Create the dataset
       DataSet dsListOfTablesWithUppercaseName = new DataSet("sysobjects");

       //Create the dataadapter object
       SqlDataAdapter sDataAdapter = new SqlDataAdapter(selectTablesWithUppercaseName, sConnection);

       //Provides the master mapping between the sourcr table and system.data.datatable
       sDataAdapter.TableMappings.Add("Table", "sysobjects");

       //Fill the dataset
       sDataAdapter.Fill(dsListOfTablesWithUppercaseName);

       //Bind the result combobox with foreign key table names
       DataViewManager dvmListOfForeignKeys = dsListOfTablesWithUppercaseName.DefaultViewManager;
       dgResultView.DataSource = dsListOfTablesWithUppercaseName.Tables["sysobjects"];
    }
    catch(Exception ex)
    {
        //All the exceptions are handled and written in the EventLog.
        EventLog log = new EventLog("Application");
        log.Source = "MFDBAnalyser";
        log.WriteEntry(ex.Message);
    }
    finally
    {
       //If connection is not closed then close the connection
       if(sConnection.State != ConnectionState.Closed)
       {
          sConnection.Close();
       }
    }
 }

And another function for counting the rows generated from the previous functions. But this function

Null Reference Exception or Object reference not set to an instance of object..

Can anyone help me in this... why it is catching error only for the functions above and working fine for all other similar functions.

private void UpdateLabelText()
{
    SqlConnectionStringBuilder objConnectionString = new SqlConnectionStringBuilder();
    objConnectionString.DataSource = txtHost.Text;
    objConnectionString.UserID = txtUsername.Text;
    objConnectionString.Password = txtPassword.Text;
    objConnectionString.InitialCatalog = Convert.ToString(cmbDatabases.SelectedValue);

    SqlConnection sConnection = new SqlConnection(objConnectionString.ConnectionString);

    //To Open the connection.
    sConnection.Open();

    try
    {
        int SelectedCellTotal = 0;
        int counter;

        // Iterate through the SelectedCells collection and sum up the values.
        for(counter = 0;counter < (dgResultView.SelectedCells.Count);counter++)
        {
            if(dgResultView.SelectedCells[counter].FormattedValueType == Type.GetType("System.String"))
            {
                 string value = null;

                 // If the cell contains a value that has not been commited,
                 if(dgResultView.IsCurrentCellDirty == true)
                 {
                    value = dgResultView.SelectedCells[counter].EditedFormattedValue.ToString();
                 }
                 else
                 {
                    value = dgResultView.SelectedCells[counter].FormattedValue.ToString();
                 }
                 if(value != null)
                 {
                    // Ignore cells in the Description column.
                    if(dgResultView.SelectedCells[counter].ColumnIndex != dgResultView.Columns["TABLE_NAME"].Index)
                    {
                       if(value.Length != 0)
                       {
                          SelectedCellTotal += int.Parse(value);
                       }
                    }
                 }
              }
            }

            // Set the labels to reflect the current state of the DataGridView.
            lblDisplay.Text = "There are Total " + dgResultView.RowCount + cmbOperations.SelectedItem.ToString();
        }
        catch(Exception ex)
        {
            //All the exceptions are handled and written in the EventLog.
            EventLog log = new EventLog("Application");
            log.Source = "MFDBAnalyser";
            log.WriteEntry(ex.Message);
        }
        finally
        {
            //If connection is not closed then close the connection
            if(sConnection.State != ConnectionState.Closed)
            {
                sConnection.Close();
            }
        }
    }

Also the lblDisplay.Text is not taking proper spaces.

Waiting for reply

You've provided a lot of code... which line is throwing the exception? — Jon Skeet
– Jon Skeet, Commented Dec 2, 2010 at 13:09
And why do you even create and open a SqlConnection in your second method?? You're not using it anywhere...... — marc_s
– marc_s, Commented Dec 2, 2010 at 13:27
Regarding the spaces in lblDisplay: you have to add space after the count: lblDisplay.Text = "There are Total " + dgResultView.RowCount + " " + cmbOperations.SelectedItem.ToString(); — user447356
– user447356, Commented Dec 2, 2010 at 13:41

marc_s · Accepted Answer · 2010-12-02 13:33:03Z

OK, I don't really have an answer why you're getting a "null reference exception" - but a few points to throw in, nonetheless:

I would use sys.tables instead of sysobjects and having to specify what type of object to query for
ALWAYS put your disposable SqlConnection and SqlCommand into using(.....) { ...... } blocks. That way, you won't need any finally {..} blocks, and .NET will take care of properly disposing of those objects when they're no longer needed
why do you use a DataSet when you only have a single table inside?? That's just unnecessary overhead - use a DataTable instead!
don't open the SqlConnection that early - wait 'til the very last moment, open it, execute query, close it again right away
actually, when using the SqlDataAdapter, you don't need to open the SqlConnection yourself at all - the SqlDataAdapter will do that for you (and close it again after it is done reading the data)
do not mix the retrieval of the data from the database with the binding to the UI element - this is a very bad practice. From the GetTablesWithUpperCaseName method, you should return something (like a DataTable) to the caller (the UI) and let the UI handle the binding process
along the same lines: that method should not be grabbing stuff from UI elements (like text boxes) itself - pass in those values as method parameters, to get a cleaner code - one that you might actually be able to reuse in another project some day

This is how I think your first method ought to look like

    public DataTable GetTablesWithUpperCaseName(string server, string database, 
                                                string username, string password)
    {
        // Create the datatable
        DataTable dtListOfTablesWithUppercaseName = new DataTable("tableNames");

        SqlConnectionStringBuilder objConnectionString = new SqlConnectionStringBuilder();
        objConnectionString.DataSource = server;;
        objConnectionString.UserID = username;
        objConnectionString.Password = password;
        objConnectionString.InitialCatalog = database;

        // Define the Query against sys.tables - much easier and cleaner!
        string selectTablesWithUppercaseName =
            "SELECT NAME FROM sys.tables WHERE UPPER(name) COLLATE Latin1_General_BIN = name COLLATE Latin1_General_BIN AND is_msshipped = 0";

        // put your SqlConnection and SqlCommand into using blocks!
        using (SqlConnection sConnection = new SqlConnection(objConnectionString.ConnectionString))
        using (SqlCommand sCommand = new SqlCommand(selectTablesWithUppercaseName, sConnection))
        {
            try
            {
                // Create the dataadapter object
                SqlDataAdapter sDataAdapter = new SqlDataAdapter(selectTablesWithUppercaseName, sConnection);

                // Fill the datatable - no need to open the connection, the SqlDataAdapter will do that all by itself 
                // (and also close it again after it is done)
                sDataAdapter.Fill(dtListOfTablesWithUppercaseName);
            }
            catch (Exception ex)
            {
                //All the exceptions are handled and written in the EventLog.
                EventLog log = new EventLog("Application");
                log.Source = "MFDBAnalyser";
                log.WriteEntry(ex.Message);
            }
        }

        // return the data table to the caller
        return dtListOfTablesWithUppercaseName;
    }

Collectives™ on Stack Overflow

Object reference not set to an instance of object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related