vb.net - UPDATE statement in VB connecting to Access -
i'm trying update position column in access database problem i'm having problem update column while rest of column not give out error message.
the error message: syntax error in update statement, microsoft jet database engine...
the code:
dim myconnection oledbconnection = new oledbconnection dim ds new dataset dim da oledbdataadapter dim maxrows integer dim integer dim sql string private sub updatebutton_click(sender object, e eventargs) handles updatebutton.click using myconnection = new oledbconnection("provider = microsoft.ace.oledb.12.0; data source =c:\lecturerdetail.mdb") myconnection.open() dim str string str = "update lecturer " & _ "set [empname] = ?,[empid] = ?, [position] =?, [faculty] = ? " & _ " , [degree1] = ?, [degree2] = ?, [degree3] = ?,[degree] = ?, [emptype] = ? " & _ " ,[icno] = ?, [citizenship] = ?, [phoneno] = ?, [email] = ?,[permitno] = ? " & _ " , [permitstartdate] = ?, [permitenddate] = ?, [pstatus] =?, [remark] =? " & _ " ([empid] = ?) " dim cmd oledbcommand = new oledbcommand(str, myconnection) cmd.parameters.addwithvalue("@empname", nametxt.text) cmd.parameters.addwithvalue("@empid", empidtxt.text) cmd.parameters.addwithvalue("@position", positioncombobox.selectedtext) cmd.parameters.addwithvalue("@faculty", facultycombobox.selectedtext) cmd.parameters.addwithvalue("@degree1", empdeg1.text) cmd.parameters.addwithvalue("@degree2", empdeg2.text) cmd.parameters.addwithvalue("@degree3", empdeg3.text) cmd.parameters.addwithvalue("@degree", empdeg4.text) cmd.parameters.addwithvalue("@emptype", emptypecombobox.selectedtext) cmd.parameters.addwithvalue("@icno", ictxt.text) cmd.parameters.addwithvalue("@citizenship", citizenshipcombobox.selectedtext) cmd.parameters.addwithvalue("@phoneno", phonetxt.text) cmd.parameters.addwithvalue("@email", emailtxt.text) cmd.parameters.addwithvalue("@permitno", permitnotxt.text) cmd.parameters.addwithvalue("@permitstartdate", datetimepicker1.text) cmd.parameters.addwithvalue("@permitenddate", datetimepicker2.text) cmd.parameters.addwithvalue("@pstatus", statuscombobox.text) cmd.parameters.addwithvalue("@remark", remark.text) cmd.parameters.addwithvalue("@empid", empidtxt.text) try cmd.executenonquery() dim cb new oledb.oledbcommandbuilder(da) ds.tables("lecturer").rows(i).item(0) = empidtxt.text ds.tables("lecturer").rows(i).item(1) = nametxt.text ds.tables("lecturer").rows(i).item(2) = positioncombobox.text ds.tables("lecturer").rows(i).item(3) = facultycombobox.text ds.tables("lecturer").rows(i).item(4) = empdeg1.text ds.tables("lecturer").rows(i).item(5) = empdeg2.text ds.tables("lecturer").rows(i).item(6) = empdeg3.text ds.tables("lecturer").rows(i).item(7) = empdeg4.text ds.tables("lecturer").rows(i).item(8) = emptypecombobox.text ds.tables("lecturer").rows(i).item(9) = ictxt.text ds.tables("lecturer").rows(i).item(10) = citizenshipcombobox.text ds.tables("lecturer").rows(i).item(11) = phonetxt.text ds.tables("lecturer").rows(i).item(12) = emailtxt.text ds.tables("lecturer").rows(i).item(13) = permitnotxt.text ds.tables("lecturer").rows(i).item(14) = datetimepicker1.value ds.tables("lecturer").rows(i).item(15) = datetimepicker2.value ds.tables("lecturer").rows(i).item(16) = statuscombobox.text ds.tables("lecturer").rows(i).item(17) = remark.text da.update(ds, "lecturer") ds.acceptchanges() myconnection.close() msgbox("record updated") catch ex exception messagebox.show(ex.message & "-" & ex.source) end try end using end sub my code update columns , allow user navigate next record.
i'm new visual basic detail description , guidance appreciated. thanks. in addition, i'm trying create auto notification system based on dates. might enlighten me on methods or applications used in visual basic it.
no need create update query hand. commandbuilder generate necessary instructions update database.
dim ds new dataset dim da oledbdataadapter dim maxrows integer dim integer dim sql string private sub updatebutton_click(sender object, e eventargs) handles updatebutton.click using myconnection = new oledbconnection("provider = microsoft.ace.oledb.12.0; data source =c:\lecturerdetail.mdb") myconnection.open() dim str string str = "select * lecturer ([empid] = @empid)" dim cmd oledbcommand = new oledbcommand(str, myconnection) cmd.parameters.addwithvalue("@empid", empidtxt.text) try da = new oledbdataadapter(cmd) da.fill(ds,"lecturer") ds.tables("lecturer").rows(i).item(0) = empidtxt.text ds.tables("lecturer").rows(i).item(1) = nametxt.text ds.tables("lecturer").rows(i).item(2) = positioncombobox.text ds.tables("lecturer").rows(i).item(3) = facultycombobox.text ds.tables("lecturer").rows(i).item(4) = empdeg1.text ds.tables("lecturer").rows(i).item(5) = empdeg2.text ds.tables("lecturer").rows(i).item(6) = empdeg3.text ds.tables("lecturer").rows(i).item(7) = empdeg4.text ds.tables("lecturer").rows(i).item(8) = emptypecombobox.text ds.tables("lecturer").rows(i).item(9) = ictxt.text ds.tables("lecturer").rows(i).item(10) = citizenshipcombobox.text ds.tables("lecturer").rows(i).item(11) = phonetxt.text ds.tables("lecturer").rows(i).item(12) = emailtxt.text ds.tables("lecturer").rows(i).item(13) = permitnotxt.text ds.tables("lecturer").rows(i).item(14) = datetimepicker1.value ds.tables("lecturer").rows(i).item(15) = datetimepicker2.value ds.tables("lecturer").rows(i).item(16) = statuscombobox.text ds.tables("lecturer").rows(i).item(17) = remark.text dim cb new oledb.oledbcommandbuilder(da) da.update(ds, "lecturer") ds.acceptchanges() myconnection.close() msgbox("record updated") catch ex exception messagebox.show(ex.message & "-" & ex.source) end try end using end sub 
Comments
Post a Comment