CODEWITHSUNDEEP
sqlc#-4.0
alternatefaraz
alternatefaraz

Reputation: 374

Update Sql query where data is comming from 2 tables

I need to know that i have 3 tables one with sessional marks with primary key sessional id and another table student with student id as primary key and third table Data with student id and sessional id as foreign keys. Now i need to update marks of each student in grid view so that marks get stored in sessional marks table. i am using this query 

        string hourly1;
        string hourly2;
        string student_id;

        for (int i = 0; i < this.dataGridView1.Rows.Count - 1; i++)
        {
            hourly1 = dataGridView1[1,i].Value.ToString();
            hourly2 = dataGridView1[2,i].Value.ToString();
            student_id = Convert.ToString(dataGridView1[3, i].Value);

            SqlCommand cmd = new SqlCommand("UPDATE SessionalMarks  SET " +
            "SessionalMarks.Hourly1Marks = '" + hourly1 + "'," + "SessionalMarks.Hourly2Marks = '" + hourly2 + "'from Student,DATA  where Student.StudentId=DATA.StudentId AND Student.StudentId='" + student_id + "'", conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

but this query is adding same marks in each row in need to update marks which are placed in the grid view i think there is a problem in where clause can some one help me please.

Upvotes: 0

Views: 154

Answers (1)

Stephen Jennings
Stephen Jennings

Reputation: 13234

It's very unclear what your SQL schema looks like, but in your update statement, everything after FROM isn't being used for any purpose. Your WHERE clause doesn't mention SessionalMarks, so each time you loop through your for loop you're updating all rows in that table.

If you post your SQL schema of these three tables, it might help us understand the shape of your data and help you write a better query. The way you've described it, it sounds like the DATA table ought to contain the marks for each student, but you've apparently put that data in the SessionalMarks table instead.

However, your code has some problems besides the one you've mentioned:

  • You're composing a SQL query by joining strings together, and it looks like those strings might have come from the user. This exposes you to a SQL injection attack, or at least if the user types a ' your program will behave incorrectly.

  • You're opening and closing the database connection repeatedly, instead of opening it once and closing it when you're finished.

To fix these problems, you probably should (a) use a parameterized SQL query, and (b) open and close the database connection outside of the loop.

This isn't perfect, but it's a start:

// this query needs to be fixed
string query = @"UPDATE SessionalMarks 
                SET SessionalMarks.Hourly1Marks = @hourly1
                   ,SessionalMarks.Hourly2Marks = @hourly2
                FROM Student,DATA
                WHERE Student.StudentId = DATA.StudentId
                AND Student.StudentId = @studentid";

// Make sure we dispose of the SqlCommand when we're finished with it
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    // Create my three parameters, don't need a value yet
    // Need to make sure we have the right SqlDbType specified
    cmd.Parameters.Add( new SqlParameter("@hourly1", SqlDbType.Decimal) );
    cmd.Parameters.Add( new SqlParameter("@hourly2", SqlDbType.Decimal) );
    cmd.Parameters.Add( new SqlParameter("@studentid", SqlDbType.Int) );

    conn.Open();

    try
    {
        // For each row in our DataGridView
        // We could also use foreach(DataGridViewRow row in dataGridView1.Rows)
        for (int i = 0; i < this.dataGridView1.Rows.Count - 1; i++)
        {
            // Fill in our parameters with our values
            cmd.Parameters[0].Value = dataGridView1[1,i].Value.ToString();
            cmd.Parameters[1].Value = dataGridView1[2,i].Value.ToString();
            cmd.Parameters[2].Value = Convert.ToString(dataGridView1[3, i].Value);

            cmd.ExecuteNonQuery();
        }
    }
    finally
    {
        // Close our database connection even if cmd.ExecuteNonQuery() throws
        conn.Close();
    }
}

Upvotes: 1

Related Questions