CODEWITHSUNDEEP
c#sql-server
user2430070
user2430070

Reputation: 1

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

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

Upvotes: 0

Views: 182

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

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.

Upvotes: 3

Josh
Josh

Reputation: 363

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

Upvotes: 1

Nasir
Nasir

Reputation: 11401

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

Upvotes: 0

Related Questions