Simple database Connection error in C#

Question

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");
}

There should be only a single gap like in `Integrated Security`. — Amit Joki, Feb 22 '14 at 13:21
`SqlCommand` is not associated with a connection. You need to set `conn` to the command. — Reactgular, Feb 22 '14 at 13:22
There is also an extra comma at the end of the Insert just before the last parentheses. — John Bartels, Feb 22 '14 at 13:30

kmatyaszek · Answer 1 · 2014-02-22T13:31:52.297

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.

score 1 · Answer 2 · edited May 23 '17 at 10:31

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.

score 0 · Answer 3 · answered Feb 22 '14 at 13:22

0

I'd print that SQL text. Looks like there's an unbalanced apostrophe to me.

Better yet, use a .NET class that binds parameters for you. Easier and better SQL injection projection, too.

answered Feb 22 '14 at 13:22

duffymo

305,152
44
369
561

score 0 · Answer 4 · answered Feb 22 '14 at 13:28

0

What are tfCode, tfName,tfCell,tfAdrs? I assume they are textbox control? if so you are using tfAdrs instead of tfAdrs.Text

also assign connection string to the command and remove additional space in
"Integrated security"

answered Feb 22 '14 at 13:28

Alaa Jabre

1,843
5
26
52

score 0 · Answer 5 · answered Feb 22 '14 at 13:43

0

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);

answered Feb 22 '14 at 13:43

Dayan

7,634
11
49
76

@DastgirKhan Your welcome, please make sure to accept an answer. – Dayan Feb 22 '14 at 18:44

Simple database Connection error in C#

5 Answers5