CODEWITHSUNDEEP
c#sql-servervisual-studio-2013
CR9191
CR9191

Reputation: 51

How do I insert an item to my table properly? SQL/C#

I have troubles with inserting a review to my Reviews table due to my 2nd query error. I keep getting my message box (debugging) reads as (se7en, 57547, 4) or even 0 if I play around with the query instead of (24, 57547, 4) because when I typed in a movie, it would be converted to a movie ID number in order to be used as an int for different table. My goal is to read as (24, 57547, 4) in order to be inserted to my table.

    private void InsertReview_Click(object sender, EventArgs e)
    {
        string filename, connectionInfo;
        SqlConnection db;

        this.listBox1.Items.Clear();

        filename = "netflix.mdf";

        connectionInfo = String.Format(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\{0};Integrated Security=True;", filename);

        db = new SqlConnection(connectionInfo);
        db.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = db;

        string moviename = this.textBox1.Text;
        moviename = moviename.Replace("'", "''");

        cmd.CommandText = string.Format(
            @"SELECT MovieID FROM Movies                     
              where MovieName = '{0}';", moviename);

        object result = cmd.ExecuteScalar();

        int id = System.Convert.ToInt32(result);

        this.listBox1.Items.Add(id); //debugging to make sure it converted right

        SqlCommand cmd2 = new SqlCommand();
        cmd2.Connection = db;

        cmd2.CommandText = //the 2nd query, the issue im posting here
            string.Format(
            @"INSERT INTO Reviews (MovieID, UserID, Rating)
              VALUES({0});", id, this.textBox1.Text);

        MessageBox.Show(cmd2.CommandText); //debugging

        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        db.Close();

        //this.listBox1.Items.Add("Movie Review inserted successfully!");
    }

Upvotes: 0

Views: 59

Answers (1)

Ben Jaspers
Ben Jaspers

Reputation: 1546

In your string.Format:

cmd2.CommandText = //the 2nd query, the issue im posting here
            string.Format(
            @"INSERT INTO Reviews (MovieID, UserID, Rating)
              VALUES({0});", id, this.textBox1.Text);

you have only one format item {0}, but you are passing two arguments to be inserted into the string. And you are trying to insert 3 columns of data in the table. I'm not sure where/how the UserID and Rating are being stored, but your code should look more like:

cmd2.CommandText = //the 2nd query, the issue im posting here
            string.Format(
            @"INSERT INTO Reviews (MovieID, UserID, Rating)
              VALUES({0}, {1}, '{2}');", id, userId, rating);

This is a very bad way to do dynamic SQL though.

You should parameterize the query, like so:

cmd2.CommandText = //the 2nd query, the issue im posting here
            @"INSERT INTO Reviews (MovieID, UserID, Rating)
              VALUES(@MovieId, @UserId, @Rating);");

cmd2.Parameters.AddWithValue("@MovieId", id);
cmd2.Parameters.AddWithValue("@UserId", userId);
cmd2.Parameters.AddWithValue("@Rating", rating);

Upvotes: 5

Related Questions