VB.NET: Updating MySQL Database From (CheckedListBox-ComboBox) -

i have made form supposed attendance details specific session. used elements are:

1- checkedlistbox

2- combobox :cb_students

3- button : update

my table student, contains code| first name| last name| day1| day2| day3| day4| day5, days of type tinyint, referring presence or absence.

i want user check students' names checkedlistbox, populated checkedlistbox concatenation of first names , last names, each item showing full name.

since checkedlistbox indirectly created database records, suppose cannot connect database directly, have created hidden combobox values of code corresponding each student shown in checkedlistbox.

for example, student table below:

code | f_name | l_name

1 | f1 | l1

2 | f2 | l2

the checkedlistbox contains: f1 l1 , f2 l2, , combobox contains 1 , 2.

the day number found during form load , saved public variable inquiry.

below code update button:

dim j, s integer     sqlstr = "update student set day'" & inquiry & "'=@field1 code='" & cb_students.items.item(j) & "'"     dbcmd = new mysql.data.mysqlclient.mysqlcommand(sqlstr, dbconn)     dbcmd         j = 0 checkedlistbox1.items.count - 1             s = checkedlistbox1.getitemcheckstate(j)             if s = 1                 .parameters.addwithvalue("@field1", 1)             elseif s = 0                 .parameters.addwithvalue("@field1", 0)             end if         next j     end     dbcmd.executenonquery()     dbcmd.dispose() 

however, during execution, error: "parameter '@field1' has been defined."

how should deal problem? also, statement cb_students.items.item(j) used correctly give sql string student code in combobox?

update 1:

inquiry integer. tried following code:

 j = 0 checkedlistbox1.items.count - 1         s = checkedlistbox1.getitemcheckstate(j)         if s = 1             dbcmd                 .parameters.addwithvalue("@field1", 1)                 .executenonquery()                 .dispose()             end         elseif s = 0             dbcmd                 .parameters.addwithvalue("@field1", 0)                 .executenonquery()                 .dispose()             end         end if     next j 

but again, in response never_mind, following error @ first dbcmd.executenonquery() line: "you have error in sql syntax; check manual corresponds mysql server version right syntax use near ''4' =1 code='6'' @ line 1".

the values seem correct. day number 4, field1 1 , code 6. don't see problem is.

update 2:

below code populating checkedlistbox , combobox, in form load. c_code class code, field in student table. classid public integer variable saved in previous form. form appears using .showdialog method.

sqlstr = "select * student c_code = '" & classid & "'" dbcmd = new mysql.data.mysqlclient.mysqlcommand(sqlstr, dbconn) dbdr = dbcmd.executereader     while (dbdr.read())         std_name = dbdr("f_name") & " " & dbdr("l_name")         checkedlistbox1.items.add(std_name)         cb_students.items.add(dbdr("code"))     end while     dbcmd.dispose()     dbdr.close() 

also, code integer.

update 3:

i have changed inquiry's datatype string , removed single quotes.

anyhow, thinking maybe make different field numbers. this:

dim j, s, k integer     sqlstr = "update student set d" & inquiry & " =@field" & k & " code='" & cb_students.items.item(j) & "' " dbcmd = new mysql.data.mysqlclient.mysqlcommand(sqlstr, dbconn) j = 0 checkedlistbox1.items.count - 1         k = 1 checkedlistbox1.items.count             s = checkedlistbox1.getitemcheckstate(j)             if s = 1                 dbcmd                     .parameters.addwithvalue("@field" & k, 1)                     .executenonquery()                     .dispose()                 end             elseif s = 0                 dbcmd                     .parameters.addwithvalue("@field" & k, 0)                     .executenonquery()                     .dispose()                 end             end if         next k     next j 

but doesn't work. think there should wrong way i've concatenated @field , k while adding through command. how can work?

i appreciate help!

try this

 dbcmd         j = 0 checkedlistbox1.items.count - 1             s = checkedlistbox1.getitemcheckstate(j)             if s = 1                 .parameters.addwithvalue("@field1", 1)  .executenonquery()             elseif s = 0                 .parameters.addwithvalue("@field1", 0)  .executenonquery()             end if         next      end