Reputation: 191

Simple database Connection error in C#

I'm receiving the following error message:

invalidOperationException was unhandled

In the following code:

private void btnInsert_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection("Data Source=DASTGIRKHAN\\SQLEXPRESS;Initial Catalog=DBProject;Integrated    Security=True;Pooling=False");
    conn.Open();
    SqlCommand cmd = new SqlCommand("Insert INTO EmployeeRecord Values(" + tfCode.Text   + ",'" + tfName.Text + "','" + tfCell.Text + "','" + tfAdrs + "',)");
    cmd.BeginExecuteNonQuery();
    cmd.ExecuteNonQuery();
    conn.Close();
    MessageBox.Show("Inserted Successfully");
}

Upvotes: 0

Answers (5)

Steve

Reputation: 216363

Sorry but your code has many errors. Let me show a different approach

private void btnInsert_Click(object sender, EventArgs e)
{
    string cnString = @"Data Source=DASTGIRKHAN\\SQLEXPRESS;
                        Initial Catalog=DBProject;
                        Integrated Security=True;";
    string cmdText = @"Insert INTO EmployeeRecord 
                       Values(@code,@fname,@cell,@adr)";
    using(SqlConnection conn = new SqlConnection(cnString))
    using(SqlCommand cmd = new SqlCommand(cmdText, conn))
    {
         conn.Open();
         cmd.Parameters.AddWithValue("@code", Convert.ToInt32(tfCode.Text));
         cmd.Parameters.AddWithValue("@fname", tfName.Text );
         cmd.Parameters.AddWithValue("@cell", tfCell.Text  );
         cmd.Parameters.AddWithValue("@adr", tfAdrs.Text);
         int rowsInserted = cmd.ExecuteNonQuery();
         if(rowInserted > 0) 
              MessageBox.Show("Inserted Successfully");
         else
              MessageBox.Show("Insert failes");
    }
}

The primary cause of your error is stated by the answer of kmatyaszek, but this is just the tip of the iceberg.

You should always use the using statement around your disposable objects like the connection. This will ensure that the connection is closed and disposed also in case of exceptions.

You should use a parameterized query to create your command to avoid Sql Injection and parsing problems. For example, a single quote in the tfName textbox could lead to a Syntax Error.

The call to BeginExecuteNonQuery, excludes the call to ExecuteNonQuery and requires a call to EndExecuteNonQuery.

Finally, the result of ExecuteNonQuery tells you if the insertion is successful.

As a last note, I have remove the Pooling=False from the connection string.
You haven't said anything why do you want avoid his very useful optimization.

Upvotes: 1

Dayan

Reputation: 8031

Why complicate yourself, use Parameterized Insert instead of concatenation, which its prone to SQL Injection.

SqlCommand command1 = new SqlCommand("INSERT INTO EmployeeRecord VALUES(@tfCode, @tfName, @tfCell, @tfAdrs)", conn);

command1.Parameters.AddWithValue("@tfCode", trCode);
command1.Parameters.AddWithValue("@tfName", tfName);
command1.Parameters.AddWithValue("@tfCell", tfCell);
command1.Parameters.AddWithValue("@tfAdrs", tfAdrs);

Upvotes: 0

kmatyaszek

Reputation: 19296

InvalidOperationException exception is thrown when you invoke BeginExecuteNonQuery method (msdn) and you not specified "Asynchronous Processing=true" in the connection string.

You should also set connection to your command:

SqlCommand cmd = new SqlCommand("Insert INTO EmployeeRecord Values(" + tfCode.Text   + ",'" + tfName.Text + "','" + tfCell.Text + "','" + tfAdrs + "')", conn);

InvalidOperationException

The name/value pair "Asynchronous Processing=true" was not included within the connection string defining the connection for this SqlCommand. The SqlConnection closed or dropped during a streaming operation.

Upvotes: 1