Error in setting up the connection between SQL Server and C#

Question

What is wrong with my code? When Im setting up the connection between sql server management studio and c#, it gives me this error " ExecuteNonQuery: Connection property has not been initialized."

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString= "Database= hotel; server= Sherissa\SQLEXPRESS";
    con.Open();
    SqlCommand cmd = new SqlCommand("insert into CheckIn (@TransactionId,@GuestName,@RoomType,@RoomNo,@ReservationDate,@CheckInDate,@CheckOutDate,@NoOfDays,@NoOfAdults,@NoOfChildren)");
    cmd.Parameters.AddWithValue("@TransactionId",textBox1.Text);
    cmd.Parameters.AddWithValue("@GuestName", textBox2.Text);
    cmd.Parameters.AddWithValue("@RoomType", textBox3.Text);
    cmd.Parameters.AddWithValue("@RoomNo", textBox4.Text);
    cmd.Parameters.AddWithValue("@ReservationDate", textBox5.Text);
    cmd.Parameters.AddWithValue("@CheckInDate", textBox6.Text);
    cmd.Parameters.AddWithValue("@CheckOutDate", textBox7.Text);
    cmd.Parameters.AddWithValue("@NoOfDays", textBox8.Text);
    cmd.Parameters.AddWithValue("@NoOfAdults", textBox9.Text);
    cmd.Parameters.AddWithValue("@NoOfChildren", textBox10.Text);

    cmd.ExecuteNonQuery();
    con.Close();
    MessageBox.Show("DATA ADDED SUCCESSFULLY!!");
}

Your connection string is hardcoded. use the section in the app.config. You have datalayer code in your presentation layer..if this is a quick demo, that's ok, if this is really how you're planning on doing it...bad. .Text is always a string...you need to Convert.ToInt32 (etc, etc) the values. — granadaCoder, May 28 '13 at 20:29
Possible duplicate of [ExecuteNonQuery: Connection property has not been initialized.](http://stackoverflow.com/a/10263125/2091410) — Ed Gibbs, May 28 '13 at 20:29
You have tagged this question with MySql and sql-server. To which database are you trying to connect? — Steve, May 28 '13 at 20:32

Joel Coehoorn · Answer 1 · 2013-05-28T21:05:58.123

I see several errors here.

The first error is the substance of your question. To solve that issue, you need to associate your command with a particular connection. Remember, you might be connected to several different databases at the same time. A command needs to know which connection to use.

The next error is that your Sql syntax is wrong. You are missing the required VALUES keyword.

Another problem is that you don't close the connection correctly. If an exception is thrown, the code will never make it to the con.Close(); line. If this happens often enough you will lock yourself out of your database.

Finally, a nitpick. I'm not a fan of the .AddWithValue() method, because it forces .Net to try to guess the sql datatype you're using. Sometimes it guesses wrong, and when it does the performance implications can be severe, because it can break index use on the database.

Here is code that solves all four issues:

private void button1_Click(object sender, EventArgs e)
{
    using (var con = new SqlConnection("Database= hotel; server= roger\SQLEXPRESS"))
    using (var cmd = new SqlCommand("insert into CheckIn VALUES (@TransactionId,@GuestName,@RoomType,@RoomNo,@ReservationDate,@CheckInDate,@CheckOutDate,@NoOfDays,@NoOfAdults,@NoOfChildren)", con))
    {

        cmd.Parameters.Add("@TransactionId", SqlDbType.Int).Value = int.Parse(textBox1.Text);
        cmd.Parameters.Add("@GuestName", SqlDbType.NVarChar, 50).Value = textBox2.Text;
        cmd.Parameters.Add("@RoomType", SqlDbType.NVarChar, 10).Value = textBox3.Text;
        cmd.Parameters.Add("@RoomNo", SqlDbType.NChar, 4).Value = textBox4.Text;
        cmd.Parameters.Add("@ReservationDate", SqlDbType.DateTime).Value = datetime.Parse(textBox5.Text);
        cmd.Parameters.Add("@CheckInDate", SqlDbType.DateTime).Value = datetime.Parse(textBox6.Text);
        cmd.Parameters.Add("@CheckOutDate", SqlDbType.DateTime).Value = datetime.Parse(textBox7.Text);
        cmd.Parameters.Add("@NoOfDays", SqlDbType.Int).Value = int.Parse(textBox8.Text);
        cmd.Parameters.Add("@NoOfAdults", SqlDbType.Int).Value = int.Parse(textBox9.Text);
        cmd.Parameters.Add("@NoOfChildren", SqlDbType.Int).Value = int.Parse(textBox10.Text);

        con.Open();
        cmd.ExecuteNonQuery();
    }
    MessageBox.Show("DATA ADDED SUCCESSFULLY!!");
}

Of course I had to guess at the datatypes to use, and in my own code I would also devote a little more effort to validating the inputs before trying to parse them.

Make sure you have the `,con ` part at the end of the line that creates the command. — Joel Coehoorn, May 28 '13 at 21:06
m now getting this error " A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) " — user2430070, May 29 '13 at 20:02
http://stackoverflow.com/questions/8075784/error-message-provider-shared-memory-provider-error-0-no-process-is-on-th — Joel Coehoorn, May 29 '13 at 20:18

score 1 · Answer 2 · edited May 28 '13 at 20:38

SqlCommand cmd = new SqlCommand("insert into CheckIn (@TransactionId, @GuestName, @RoomType, @RoomNo, @ReservationDate, @CheckInDate, @CheckOutDate, @NoOfDays, @NoOfAdults, @NoOfChildren)");`

This line should be typed as,

SqlCommand cmd = new SqlCommand("insert into CheckIn (@TransactionId, @GuestName, @RoomType, @RoomNo, @ReservationDate, @CheckInDate, @CheckOutDate, @NoOfDays, @NoOfAdults, @NoOfChildren)", con );

score 0 · Answer 3 · answered May 28 '13 at 20:29

0

You need to assign the connection to your command:

SqlCommand cmd = new SqlCommand("insert into CheckIn (@TransactionId,@GuestName,@RoomType,@RoomNo,@ReservationDate,@CheckInDate,@CheckOutDate,@NoOfDays,@NoOfAdults,@NoOfChildren)", con);

answered May 28 '13 at 20:29

Nasir

10,935
8
31
39

Error in setting up the connection between SQL Server and C#

3 Answers3