It says command.ExecuteNonQuery() is not initialized

Question

My code:

// Get Connection String
string conn = WebConfigurationManager.ConnectionStrings["GraduatesConnectionString"].ToString();
// Create connection object
SqlConnection connection = new SqlConnection(conn);
SqlCommand command = connection.CreateCommand();
try
{
    // Open the connection.
    connection.Open();
    // Execute the insert command.
    command.CommandText = ("INSERT INTO PersonalInfo(Id,Name,LastName,ContactNumber, Address,Gender, Date_Of_Birth) VALUES(\'"
                + (this.txtID.Text + ("\',\'"
                + (this.txtName.Text + ("\',\'"
                + (this.txtLastName.Text + ("\',\'"
                + (this.txtContactNumber.Text + ("\',\'"
                + (this.txtAddress.Text + ("\',\'"
                + (this.gender + ("\',\'"
                + (this.txtDateofBirth.Text + ("\',\'"
             )))));
    command.ExecuteNonQuery();
}
finally
{
    // Close the connection.
    connection.Close();
}

**STOP RIGHT NOW** and go and read about SQL Injection and how to parameterise your queries — podiluska, Sep 18 '12 at 08:38
Wrap your `SqlConnection`/`SqlCommand` up in a [using statement](http://msdn.microsoft.com/en-us/library/yh598w02.aspx). — James, Sep 18 '12 at 08:43
http://stackoverflow.com/questions/601300/what-is-sql-injection — Paddy, Sep 18 '12 at 08:47
At times like this, I bring you the obligatory XKCD comic: http://xkcd.com/327/ — Arran, Sep 18 '12 at 08:49
Please try to explain the problem in more detail, not just in code. What have you tried, and what is it that you want to do? — Olle Sjögren, Sep 18 '12 at 09:11

score 4 · Answer 1 · answered Sep 18 '12 at 08:47

4

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = "INSERT INTO PersonalInfo (Id, Name, LastName, ContactNumber, Address, Gender, Date_Of_Birth) VALUES (@Id, @Name, @LastName, @LastName, @Address, @Gender, @DateOfBirth)";

    command.Parameters.AddWithValue("@Id", txtID.Text);
    ...

    connection.Open();
    command.ExecuteNonQuery();
}

answered Sep 18 '12 at 08:47

abatishchev

98,240
88
296
433

You didn't actually answer the OP's question you simply re-wrote their code - albeit in a better more reliable way. However, this could have simply been a comment (like mines & @podiluska's). @RonaldWildenberg's answer is more appropriate as he has at least said *why* the exception is being raised. – James Sep 18 '12 at 09:11

score 3 · Answer 2 · answered Sep 18 '12 at 08:48

You are missing a closing ) after txtDateofBirth so your statement is incomplete.

BUT please take note of the comment of @podiluska. This code is really easy to abuse. Suppose I enter something like the following text in txtDateofBirth:

;DROP TABLE PersonalInfo;

You then get a query like:

INSERT INTO PersonalInfo(...)
VALUES (...);DROP TABLE PersonalInfo;

So please use parameterized queries as described by @abatishchev.

score 1 · Answer 3 · answered Sep 18 '12 at 08:53

I'd be tempted to change your code to:

string conn = WebConfigurationManager.ConnectionStrings["GraduatesConnectionString"].ToString();
// Create connection object
using(SqlConnection connection = new SqlConnection(conn))
{
    string queryText = "INSERT INTO PersonalInfo(Id,Name,LastName,ContactNumber, Address,Gender, Date_Of_Birth) VALUES(@id,@name,@lastName,@contactNumber, @address,@gender, @date_Of_Birth)";

    using(SqlCommand command = new SqlCommand(queryText, connection))
    {
        try
        {
            // Open the connection.
            connection.Open();

            command.Parameters.AddWithValue("@id", this.txtID.Text);
            command.Parameters.AddWithValue("@name", this.txtName.Text);
            command.Parameters.AddWithValue("@lastName", this.txtLastName.Text);
            command.Parameters.AddWithValue("@contactNumber", this.txtContactNumber.Text);
            command.Parameters.AddWithValue("@address", this.txtAddress.Text);
            command.Parameters.AddWithValue("@gender",this.gender );
            command.Parameters.AddWithValue("@date_Of_Birth", this.txtDateofBirth.Text);
            command.ExecuteReader();
        }
        finally
        {   
            // Close the connection.
            if(connection.State != ConnectionState.Closed)
                connection.Close();
        }
    }
}

It says command.ExecuteNonQuery() is not initialized

3 Answers3