0

enter image description herehere my query if anyone can spot error

    str = "update student set course='" & ComboBox1.Text & "',name='" & 
TextBox2.Text & "',f_name='" & TextBox3.Text & "',address='" & TextBox4.Text 
& "' ,tel_no='" & TextBox5.Text & "',qualification='" & TextBox6.Text & 
"',remarks='" & TextBox7.Text & "',school/college='" & TextBox8.Text & 
"',fee='" & TextBox10.Text & "' where reg_no=" & TextBox9.Text & " "
8
  • where am i suppose to add a from clause? i have never used from clause with update Commented Apr 3, 2017 at 5:02
  • where is the error message Commented Apr 3, 2017 at 5:06
  • and is this a name of a column? school/college Commented Apr 3, 2017 at 5:07
  • 1
    This code is crazy vulnerable to sql injection attacks. It's practically begging to get hacked. You should look up how to use parameterized queries. Commented Apr 3, 2017 at 13:51
  • 1
    SQL parameters, SQL parameters, SQL parameters. I've repeated because quite often a comment stating SQL parameters, is overlooked. Joel's mentioned it, I've mentioned it and it is important. Commented Apr 3, 2017 at 14:01

1 Answer 1

1

Here is a better way to build this query:

str = "update student " &
        " set course= @course, name= @name, f_name= @fname, address= @address," & 
        " tel_no= @tel, qualification = @qualification, remarks= @remarks," & 
        " `school/college`=@school, fee= @fee" &
      " where reg_no= @regno"

Using cn  As New MySqlConnection("connection string here"), _
      cmd As New MySqlCommand(str, cn)

    'Use actual column types/lengths from your DB here
    cmd.Parameters.Add("@course", MySqlDbType.VarChar, 15).Value = ComboBox1.Text
    cmd.Parameters.Add("@name", MySqlDbType.VarChar, 25).Value = TextBox2.Text
    cmd.Parameters.Add("@fname", MySqlDbtype.VarChar, 25).Value = TextBox3.Text
    cmd.Parameters.Add("@address", MySqlDbType.VarChar, 120).Value = TextBox4.Text
    cmd.Parameters.Add("@tel", MySqlDbType.VarChar, 25).Value = TextBox5.Text
    cmd.Parameters.Add("@qualification", MySqlDbType.VarChar, 40).Value = TextBox6.Text
    cmd.Parameters.Add("@remarks", MySqlDbType.VarString).Value = TextBox7.Text
    cmd.Parameters.Add("@school", MySqlDbType.VarChar, 40).Value = TextBox8.Text
    cmd.Parameters.Add("@fee", MySqlDbType.Decimal, 6, 2).Value = Convert.ToDecimal(TextBox10.Text)
    cmd.Parameters.Add("@regno", MySqlDbType.Int32).Value = Integer.Parse(TextBox9.Text)

    cn.Open()
    cmd.ExecuteNonQuery()
End Using

This does a number of things for you:

  1. It prevents sql injection attacks
  2. It allows you to accept data that includes things like single quotes ('). The code you have will fail if someone puts in a single quote.
  3. It handles things like date formatting for the sql automatically.
  4. It's faster, because the database server can cache the execution plan after it compiles the query, and use statistics over time to get better execution plans.
  5. It closes the db connection more reliably. The current code leaves the database connection hanging open if an exception is thrown.
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.