How to solve ExecuteNonQuery: Connection property has not been initialized

Question

I am a newbie C# programmer. I try to make game with C#. At my register page it got some error. It said that ExecuteNonQuery: Connection property has not been initializedat this line "int result = command.ExecuteNonQuery();".Here is my C# code. Please help me solve this problem.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WiimoteTest
{
public partial class regispage : Form
{

    private SqlConnection connection;
    private string sql;
    private SqlCommand command = new SqlCommand();



    public regispage()
    {
        InitializeComponent();
    }

    private void back_Click(object sender, EventArgs e)
    {
        startpage back = new startpage();
        back.Show();
        this.Close();
    }

    private void regispage_Load(object sender, EventArgs e)
    {
        string conStr = @"Data Source=.\SQLEXPRESS;
                        AttachDbFilename=C:\Users\vaio\Downloads\Compressed\WiimotLib_1.7\WiimotLib_1.7\samples\WiimoteTestCS_5_AllpagePuppyGameTestBirdResizepicTestDB\Angee.mdf;
                        Intregrated Security=True;User Instance=True;";
        connection = new SqlConnection(conStr);
        if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
    }

    private bool IsCompleteData()
    {
        string errMsg = "";
        if (username.Text == "")
        {
            errMsg = "Please input username";
        }
        else if (password.Text == "")
        {
            errMsg = "Please input password";
        }
        else if (fname.Text == "")
        {
            errMsg = "Please input firstname";
        }
        else if (lname.Text == "")
        {
            errMsg = "Please input lastname";
        }
        else if (nickname.Text == "")
        {
            errMsg = "Please input nickname";
        }
        else if (gender.SelectedIndex == -1)
        {
            errMsg = "Please select gender";
        }
        else if (age.Text == "")
        {
            errMsg = "Please input age";
        }
        else if (symptom.Text == "")
        {
            errMsg = "Please input symptom";
        }

        if (errMsg == "")
        {
            return true;
        }
        else
        {
            MessageBox.Show(errMsg);
            return false;
        }
    }

    private void AddParamterValues()
    {
        command.Parameters.AddWithValue("username", username.Text);
        command.Parameters.AddWithValue("password", password.Text);
        command.Parameters.AddWithValue("fname", fname.Text);
        command.Parameters.AddWithValue("lname", lname.Text);
        command.Parameters.AddWithValue("nickname", nickname.Text);
        command.Parameters.AddWithValue("gender", gender.SelectedItem.ToString());
        command.Parameters.AddWithValue("age", age.Text);
        command.Parameters.AddWithValue("symptom", symptom.Text);
    }

    private void submit_Click(object sender, EventArgs e)
    {
        if (!IsCompleteData())
        {
            return;
        }

        InsertData();


    }

    private void InsertData()
    {
        using (var connection = new SqlConnection())
        using (var command = connection.CreateCommand())
        {
            sql = @"INSERT INTO User(username, password, fname, lname, nickname, gender, age, symptom) 
              VALUES(@username, @password, @fname, @lname, @nickname, @gender, @age, @symptom),this.connection";

            **connection.Open();**

            var affectedRows = command.ExecuteNonQuery();

            command.Parameters.Clear();
            command.CommandText = sql;

            AddParamterValues();

            if (affectedRows < 1)
            {
                MessageBox.Show("Error to insert data");
                return;
            }
            else
            {
                MessageBox.Show("Insert data complete");
                menupage submit = new menupage();
                submit.Show();
                this.Close();
            }
        }
    }

    private void regispage_FormClosing(object sender, FormClosingEventArgs e)
    {
        if (connection.State == ConnectionState.Open)
        {
            connection.Close();
        }
    }

}

}

Austin Salonen · Answer 1 · 2013-03-19T16:41:20.660

2

No class-level data client objects. Create and dispose of them as soon as possible.

private void InsertData()
{
    using (var connection = new SqlConnection("YOUR_CONNECTION_STRING_HERE"))
    using (var command = connection.CreateCommand())
    {
        // populate command details

        connection.Open();

        var affectedRows = command.ExecuteNonQuery();

        // do whatever
    }
}

When you use my recommendation, you can get rid of these lines:

private SqlConnection connection;
private string sql;
private SqlCommand command = new SqlCommand();

... and once you fix all the compiler errors (by deleting code), you'll be good to go.

edited Mar 19 '13 at 16:41

answered Mar 19 '13 at 16:06

Austin Salonen

49,173
15
109
139

I really don't know where to add this code.Can you explain more where to add this code please? Thank you. – user2187208 Mar 19 '13 at 16:13
He means that you should create your connection and command in the same function you are using them in. – Captain Kenpachi Mar 19 '13 at 16:15
I already modify my code according to your advice but it got this error" The ConnectionString property has not been initialized." at this line "connection.Open();". I think my code have miss something because at "connection.Open();" line the open word is written in black color. – user2187208 Mar 19 '13 at 16:26
I try to delete this code "private SqlConnection connection; private string sql; private SqlCommand command = new SqlCommand();" but it cause a lots of error because the above code doesn't know the "command" and "connection" parameters so I put it back it got zero error but when I run the code it got above error. – user2187208 Mar 19 '13 at 16:31
@user2187208: see updates. Also get rid of those line and fix the compiler errors (delete code & inline the methods that set properties) – Austin Salonen Mar 19 '13 at 16:41

score 1 · Answer 2 · answered Mar 19 '13 at 16:04

1

You are not telling Command which connection to use.

answered Mar 19 '13 at 16:04

Captain Kenpachi

6,960
7
47
68

Thx for helping. At that line, I already remove "//" comment but it still got same error. How should I do :( – user2187208 Mar 19 '13 at 16:06
I already modify my code to command.Connection = connection; but it still doesn't work. – user2187208 Mar 19 '13 at 16:14

score 0 · Answer 3 · answered Mar 19 '13 at 19:27

Note that in general, you should avoid opening a connection and leaving it open, especially in a web or other multi-user, multi-threaded application. By default, connections are pooled (which is to say they remain open and available for use within the app domain whenever a connection is made using the exact same connect string value). Just create the connection needed, build and execute the command and Close()/Dispose() of the connection as fast as possibly and let the infrastructure worry about what's happening under the hood.

The general scheme for executing SQL is something like this:

public DataTable executeMyStoredProcedure()
{
  DataTable dt = new DataTable() ;
  string connectString = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;" ;

  using ( SqlConnection connection = new SqlConnection(connectString) )
  using ( SqlCommand cmd = connection.CreateCommand() )
  using ( SqlDataAdapter sda = new SqlDataAdapter( cmd ) )
  {
    cmd.CommandText = "dbo.myStoredProcedure" ;
    cmd.CommandType = CommandType.StoredProcedure;

    // add your parameters here

    sda.Fill( dt ) ;

    connection.Close() ; // redundant, FWIW, since it will be closed via Dispose() when control leaves the using block.

  }

  return dt ;
}

The format of the connection string will vary depending on what relational database you're talking to. See http://connectionstrings.com for an introduction to the minimal values you'll need for different providers and database types. It should be noted though, that connection strings have many different keys that can be set to configure the connection as desired.

And the code will vary depending on the how of your execution. There are several different ways you can execute your query.

How to solve ExecuteNonQuery: Connection property has not been initialized

3 Answers3