update command problems

Dim myconn AsNew
MySql.Data.MySqlClient.MySqlConnection("server=localhost;user
id=root;password=****;database=srasv2;pooling=false;")

Dim sql AsString = "update teacher_subject set
teacher_subject_class_id=?teacher_subject_class_id where
teacher_subject_teacher_id='%" & Label7.Text & "%' and
teacher_subject_subject_id ='%" & Label5.Text & "%' and
teacher_subject_class_id ='%" & Label6.Text & "%' " 
<--PROBLEM..it will execute but never made it into the database (mysql)

' If i change the code to "update teacher_subject set
teacher_subject_class_id=?teacher_subject_class_id where
teacher_subject_teacher_id='ad001' and teacher_subject_subject_id ='BM1'and
teacher_subject_class_id ='1D' " ..it can execute succefully....and the new
value of teacher_subject_class_id will be stored in the database..unlike the
above(PROBLEM) codes.. can anyone point which i've done wrong in the
code(PROBLEM) above...Dim mycommand AsNew
MySql.Data.MySqlClient.MySqlCommand(sql)

mycommand.Connection = myconn

myconn.Open()mycommand.Parameters.AddWithValue("?teacher_subject_class_id&q
uot;, DropDownList2.Text)

mycommand.ExecuteNonQuery()

 

myconn.Close()

update teacher_subject set teacher_subject_class_id=? where
teacher_subject_teacher_id=? and teacher_subject_subject_id =? and
teacher_subject_class_id =?

mycommand.Parameters.AddWithValue("?", DropDownList2.Text)
mycommand.Parameters.AddWithValue("?", Label7.Text)
mycommand.Parameters.AddWithValue("?", Label5.Text) 
mycommand.Parameters.AddWithValue("?", Label6.Text)

NB: ? marks must be added in the same order they exist in the sql string. Also,
I would be using Odbc as the connection and not MySQL.

Why dont you use StoredProdedure instead of this ?

kakibola:

Dim sql AsString = "update teacher_subject set
teacher_subject_class_id=?teacher_subject_class_id where
teacher_subject_teacher_id='%" & Label7.Text & "%' and
teacher_subject_subject_id ='%" & Label5.Text & "%' and
teacher_subject_class_id ='%" & Label6.Text & "%' " 
<--PROBLEM..it will execute but never made it into the database (mysql)

My suggestion is use the like keyword instead of= symbol. Because whenever we
are using the wild characters ('%') it is better to use the like keyword.

Eg:

Dim sql AsString = "update teacher_subject set
teacher_subject_class_id=?teacher_subject_class_id where
teacher_subject_teacher_id like '%" & Label7.Text & "%' and
teacher_subject_subject_id like '%" & Label5.Text & "%' and
teacher_subject_class_id like '%" & Label6.Text & "%' "

 

Hope this will help you to achieve the result.

 Happy Coding....

thank you all..
oo..i'm not familiar with SP yet..since i need to complete this program in a
hurry i dont have the time to learn the SP yet..but later after i completed my
program then i'll study it more..

i've solve the problem...yups..i use the like keyword..thank you