How do I place data from the SQL Server database into a TextBox?

Question

I am missing something very simple. I have a form with 5 textBox and want to fill them with the data from the SQL Server database. Here is what I have so far:

As I debug this code line by line it returns to the calling block at the connection.open() line.

public void Get_Contact_Info()
{
    using (DataAccessClass.sql_Connection)
    {
        string SQL = "SELECT * FROM Customer_Contacts WHERE Contact_ID = @contact_Id";
        SqlCommand sqlCommand = new SqlCommand(SQL, DataAccessClass.sql_Connection);

        sqlCommand.Parameters.AddWithValue("@Contact_Id", contact_Id);
        sqlCommand.Parameters.AddWithValue("@Contact_Direct_Number", contact_Direct_NumberTextBox);
        sqlCommand.Parameters.AddWithValue("@Contact_Cell_Number", contact_Cell_NumberTextBox);
        sqlCommand.Parameters.AddWithValue("@Contact_Email", contact_EmailTextBox);
        sqlCommand.Parameters.AddWithValue("@Contact_Department", contact_DepartmentTextBox);

        DataAccessClass.sql_Connection.Open();

        using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
        {
            while (sqlDataReader.Read())
            {
                contact_Id = sqlDataReader["Contact_ID"].ToString();
            }
        DataAccessClass.sql_Connection.Close();
        }
    }
}

I have been doing a pretty good job of getting info from the user and saving it to the SQL Server DataTable but know I want to get some out so that I can edit the info.

Any help will be gratefully appreciated.

Here is my DataAccessClass

public static class DataAccessClass
    {
        static SqlConnection sqlConnection = new SqlConnection();

        public static SqlConnection sql_Connection
        {
            get { return sqlConnection; }
        }

        public static void OpenConnection()
        {
            string sqlString = Properties.Settings.Default.ConnectionString;
            sqlConnection.ConnectionString = sqlString;
            sqlConnection.Open();
        }

        public static void CloseConnection()
        {
            sqlConnection.Close();
        }
    }

Here is how I am calling Get_Contact_Info.

    private void Customer_Add_Contact_Form_Load(object sender, EventArgs e)
    {
        this.customer_ContactsBindingSource.AddNew();
        if (contact_Id == null)
        {
            contact_NameTextBox.Select();
        }
        else
        {
            Get_Contact_Info();
        }
    }

From a DataGridView I am selecting a row and passing customer_ID to the Customer_Add_Contact_Form so that I can edit the contact information. Here is the code for this step:

DataGridViewRow row = customer_ContactsDataGridView.CurrentCell.OwningRow;
string contact_ID = row.Cells[0].Value.ToString();
string customer_Ship_ID = null;

using (Form Customer_Add_Contact_Form = new Customer_Add_Contact_Form(customer_Ship_ID, contact_ID))

Can you show how `DataAccessClass.sql_Connection` is declared and created? — JleruOHeP, Nov 14 '14 at 06:01
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Nov 14 '14 at 06:10
@GrantWinney: My code never gets into the `while` loop. When it hits the `connection.open` line it returns to the `Customer_Add_Contact_Load` block and gives me a form with 4 blank controls. — Dave Hampel, Nov 14 '14 at 06:25
@GrantWinney : Its called from the `form_Load` and I will add it to the original post. — Dave Hampel, Nov 14 '14 at 06:41
@GrantWinney I added the code on how I get the row information from a `DataGridView` and pass it onto the Contact_Form. I really appreciate your help with this. — Dave Hampel, Nov 14 '14 at 07:24

score 1 · Accepted Answer · edited May 23 '17 at 11:49

As discussed here it is better to let connection pooling manage the connections, so you can simplify your code to:

public void Get_Contact_Info()
{
    using (var connection = new SqlConnection(roperties.Settings.Default.ConnectionString))
    {
        connection.Open();

        var SQL = "SELECT * FROM Customer_Contacts WHERE Contact_ID = @contact_Id";

        var sqlCommand = new SqlCommand(SQL, connection);
        sqlCommand.Parameters.AddWithValue("@Contact_Id", contact_Id);

        using (var sqlDataReader = sqlCommand.ExecuteReader())
        {
            while (sqlDataReader.Read())
            {
                contact_Id = sqlDataReader["Contact_ID"].ToString();

                TextBoxName.Text = sqlDataReader["Contact_Name"].ToString();
                //etc ...
            }
        }
    }
}

I have removed unused parameters and created a new connection. Possibly you tried to open a connection using .Open() without initializing it with a connections string (is it roperties?)

I was running my connections like you suggest but read a post that convinced my the other way was better. I can switch back easy enough. My connection string really is `Properties`. I will try this code and let you know where I end up. — Dave Hampel, Nov 14 '14 at 06:35
As soon as I hit the connection.open line, it goes straight to the `form` and its blank because the `dataReader` is skipped and I can't figure out why. — Dave Hampel, Nov 14 '14 at 06:55

How do I place data from the SQL Server database into a TextBox?

1 Answers1