i have codes update ms access database using ucanaccess. codes works find if update first row on database problem if update second row last row error appears.
"net.ucanaccess.jdbc.ucanaccesssqlexception:integrity constraint violation:unique constrain or index violation; sys_pk_10339 table:employeetable2"
i suspect made mistake in ms access set-up, primary key employee name , data type text.
public void actionperformed(actionevent e) { string employeename = endrollnamefields.gettext(); string employeeaddress = endrolladdressfields.gettext(); string employeestatus = endrollemployeestatusfields.gettext(); string employeedateofmembership = dateofmembershipfields.gettext(); string employeeage = agefields.gettext(); string employemstatus = maritalstatusfields.gettext(); string employeebloodtype = bloodtypefields.gettext(); string employeegender = genderfields.gettext(); string beginningcapital = beginningcapitalfields.gettext(); string grosssalary = grosssalaryfields.gettext(); string salarydeductions = salarydeductionfields.gettext(); string netsalary = netsalaryfields.gettext(); try { //------------create connection data base--------------/ string dbpad = "sourcefolder/employeetable2.mdb"; string db = "jdbc:ucanaccess://" + dbpad; con = drivermanager.getconnection(db); st = con.createstatement(resultset.type_scroll_insensitive,resultset.concur_updatable); string sql = "select * employeetable2"; rs = st.executequery(sql); if (rs != null) while (rs.next()) { rs.updatestring("employee name", employeename); rs.updatestring("employee age", employeeage); rs.updatestring("employee address", employeeaddress); rs.updatestring("employee marital status", employemstatus); rs.updatestring("employee date of membership", employeedateofmembership); rs.updatestring("employee blood type", employeebloodtype); rs.updatestring("employee status", employeestatus); rs.updatestring("employee gender", employeegender); rs.updatestring("beginning capital", beginningcapital); rs.updatestring("gross salary", grosssalary); rs.updatestring("salary deductions", salarydeductions); rs.updatestring("net salary", netsalary); rs.updaterow(); } st.close(); st = con.createstatement(resultset.type_scroll_insensitive,resultset.concur_updatable); string sql1 = "select * employeetable2"; rs = st.executequery(sql1); joptionpane.showmessagedialog(null, "<html>" + "<font color=\"#008000\">" + "<html><span style='font-size:1.5em'>update data base successful"); endrollnamefields.settext(" "); endrolladdressfields.settext(" "); endrollemployeestatusfields.settext(" "); dateofmembershipfields.settext(" "); agefields.settext(" "); maritalstatusfields.settext(" "); genderfields.settext(" "); bloodtypefields.settext(" "); beginningcapitalfields.settext(" "); grosssalaryfields.settext(" "); salarydeductionfields.settext(" "); netsalaryfields.settext(" "); } catch(exception e1) { joptionpane.showmessagedialog(null, e1); } } });
you're trying modify primary key column, employee name, resulting in duplication. caused fact selecting rows employeetable2 , trying update them same value
to insert new value, use insert statement, like...
string employeename = endrollnamefields.gettext(); string employeeaddress = endrolladdressfields.gettext(); string employeestatus = endrollemployeestatusfields.gettext(); string employeedateofmembership = dateofmembershipfields.gettext(); string employeeage = agefields.gettext(); string employemstatus = maritalstatusfields.gettext(); string employeebloodtype = bloodtypefields.gettext(); string employeegender = genderfields.gettext(); string beginningcapital = beginningcapitalfields.gettext(); string grosssalary = grosssalaryfields.gettext(); string salarydeductions = salarydeductionfields.gettext(); string netsalary = netsalaryfields.gettext(); string dbpad = "sourcefolder/employeetable2.mdb"; string db = "jdbc:ucanaccess://" + dbpad; try (connection con = drivermanager.getconnection(db)) { try (preparedstatement stmt = con.preparestatement("insert employeetable2 (" + "'employee name', " + "'employee age', " + "'employee address', " + "'employee marital status', " + "'employee date of membership', " + "'employee blood type'" + "'employee status'," + "'employee gender'," + "'beginning capital'," + "'gross salary'," + "'salary deductions'," + "'net salary') values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) { stmt.setstring(1, employeename); stmt.setstring(2, employeeage); stmt.setstring(3, employeeaddress); stmt.setstring(4, employemstatus); stmt.setstring(5, employeedateofmembership); stmt.setstring(6, employeebloodtype); stmt.setstring(7, employeestatus); stmt.setstring(8, employeegender); stmt.setstring(9, beginningcapital); stmt.setstring(10, grosssalary); stmt.setstring(11, salarydeductions); stmt.setstring(12, netsalary); joptionpane.showmessagedialog(null, "<html>" + "<font color=\"#008000\">" + "<html><span style='font-size:1.5em'>update data base successful"); endrollnamefields.settext(" "); endrolladdressfields.settext(" "); endrollemployeestatusfields.settext(" "); dateofmembershipfields.settext(" "); agefields.settext(" "); maritalstatusfields.settext(" "); genderfields.settext(" "); bloodtypefields.settext(" "); beginningcapitalfields.settext(" "); grosssalaryfields.settext(" "); salarydeductionfields.settext(" "); netsalaryfields.settext(" "); } } catch (exception e1) { joptionpane.showmessagedialog(null, e1); } now, if want update existing row, can use update statement, or using current coding style, select row matches name of employee , update row, but, assumes name didn't change...
string employeename = endrollnamefields.gettext(); string employeeaddress = endrolladdressfields.gettext(); string employeestatus = endrollemployeestatusfields.gettext(); string employeedateofmembership = dateofmembershipfields.gettext(); string employeeage = agefields.gettext(); string employemstatus = maritalstatusfields.gettext(); string employeebloodtype = bloodtypefields.gettext(); string employeegender = genderfields.gettext(); string beginningcapital = beginningcapitalfields.gettext(); string grosssalary = grosssalaryfields.gettext(); string salarydeductions = salarydeductionfields.gettext(); string netsalary = netsalaryfields.gettext(); string dbpad = "sourcefolder/employeetable2.mdb"; string db = "jdbc:ucanaccess://" + dbpad; try (connection con = drivermanager.getconnection(db)) { try (preparedstatement stmt = con.preparestatement("select * employeetable2 'employee nam' == ?", resultset.type_scroll_insensitive, resultset.concur_updatable)) { stmt.setstring(1, employeename); try (resultset rs = stmt.executequery()) { if (rs.next()) { rs.updatestring("employee name", employeename); rs.updatestring("employee age", employeeage); rs.updatestring("employee address", employeeaddress); rs.updatestring("employee marital status", employemstatus); rs.updatestring("employee date of membership", employeedateofmembership); rs.updatestring("employee blood type", employeebloodtype); rs.updatestring("employee status", employeestatus); rs.updatestring("employee gender", employeegender); rs.updatestring("beginning capital", beginningcapital); rs.updatestring("gross salary", grosssalary); rs.updatestring("salary deductions", salarydeductions); rs.updatestring("net salary", netsalary); rs.updaterow(); joptionpane.showmessagedialog(null, "<html>" + "<font color=\"#008000\">" + "<html><span style='font-size:1.5em'>update data base successful"); endrollnamefields.settext(" "); endrolladdressfields.settext(" "); endrollemployeestatusfields.settext(" "); dateofmembershipfields.settext(" "); agefields.settext(" "); maritalstatusfields.settext(" "); genderfields.settext(" "); bloodtypefields.settext(" "); beginningcapitalfields.settext(" "); grosssalaryfields.settext(" "); salarydeductionfields.settext(" "); netsalaryfields.settext(" "); } else { // no matching row, maybe insert insetad } } } } catch (sqlexception e1) { joptionpane.showmessagedialog(null, e1); } another approach might maintain reference original resultset used load data , update "current" row new data, need careful make sure cursor position didn't change new row. otherwise, should keep copy of original key (employee name) row , use select data database when want perform update...
personally, think update statement simpler...
you should taking more care resources, if exception occurs in code, it's unlikely close outstanding, open resources.
take @ the try-with-resources statement more details
(ps it's been long time since did work against ms access database, might have got column quoting wrong)
Comments
Post a Comment