0

Im getting fatal error during command execution when I add the WHERE clause which is the ID

MySqlCommand MyCommand2 = new MySqlCommand("UPDATE student SET name =@Name, gender = @Gender, course =@Course, section = @Section, violation = @Violation, action = @Action, manual =@Manual, report = @Report, date = @Date ,img = @Img WHERE ID = @id ", MyConn2);
//
MyCommand2.Parameters.AddWithValue("@Name", name_tf.Text);
MyCommand2.Parameters.AddWithValue("@Gender", gender_tf.Text);
MyCommand2.Parameters.AddWithValue("@Course", course_tf.Text);
MyCommand2.Parameters.AddWithValue("@Section", yr_tf.Text);
MyCommand2.Parameters.AddWithValue("@Violation", vio_tf.Text);
MyCommand2.Parameters.AddWithValue("@Action", taken_cb.Text);
MyCommand2.Parameters.AddWithValue("@Manual", manual_tf.Text);
MyCommand2.Parameters.AddWithValue("@Report", report_tf.Text);
MyCommand2.Parameters.AddWithValue("@Date", date_tf.Text);
MyCommand2.Parameters.AddWithValue("@Img", arr);

My questions are;

  1. How can I implement it in where clause correctly since my ID is PK which is not user input?
  2. How to avoid updating all the data in database even you selected specific data only?

SOLVE!! how did i do it? so here;

int i; // global variable

I put this code on the property of DataGridView which is CellClick

i = Convert.ToInt32(stud_tbl.CurrentRow.Cells[0].Value);

I add this in mysqlcommand

MyCommand2.Parameters.AddWithValue("@id", i);

that's all thanks everyone who commentend!

Janbres Gagaracruz
  • 88
  • 8
  • 2
    you have not added the parameter value for `@id`. – Akhilesh Mishra Dec 07 '20 at 06:22
  • 1
    The where clause (`WHERE ID = @id`) requires you supply the ID. – kmoser Dec 07 '20 at 06:22
  • will i still do that? MyCommand2.Parameters.AddWithValue("@id", What should I put here?"); – Janbres Gagaracruz Dec 07 '20 at 06:23
  • im having trouble because @id is not user input, it is auto incremented in my database. what should i put i the parameter? – Janbres Gagaracruz Dec 07 '20 at 06:24
  • Not related to your problem but [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already) – Bill Tür stands with Ukraine Dec 07 '20 at 07:01
  • 1
    Are you adding a new record to the database or are you modifying a record that you first read from the database? If you are adding a record you should use the `INSERT` command. If you are reading a record from the database in order to later update it, you need to store the `ID` that came out from the database and use if in the update. – user1429080 Dec 07 '20 at 07:18
  • @BillTür This is MySQL, so go ahead and keep using `AddWithValue`: https://mysqlconnector.net/overview/using-addwithvalue/ – Bradley Grainger Dec 07 '20 at 14:48

1 Answers1

0

You need to supply the id.

You say that you don't have the id and it's not part of the user input - that's good: the user should not decide this id.

Normally the id is passed into the code that displays the form but it's a hidden field which is passed back into the update function.

The flow would look something like this:

  1. Read student's current values including id.
  2. Show a form with current student values but but keep id hidden.
  3. Call the update function and along the other values pass in the id field.
tymtam
  • 31,798
  • 8
  • 86
  • 126