Forums Register Login

How to update multiple rows in java jdbc on duplicate value

+Pie Number of slices to send: Send
I have a .jsp page that has a multi checkbox. I'm able to insert the multiple checkbox values but when use an update, it adds the current checkbox rows plus the additional checkbox value rows. If I check one more box it should add 1 more row and so on.

Here is my code that works for inserting data, this works okay:

CollDAO.java

   Insert checkbox records



If I select 2 check boxes this is what it looks like.

rID  |  qID  |  cID -- CORRECT OUTPUT

101 | 121 | 9

100 | 121 | 13

Here's code for Updating checkbox records, this does not work.




If I select 3 check boxes this is what get updated.

rID  |  qID  |  cID  -- WRONG OUTPUT

105 | 121 | 2

104 | 121 | 9

103 | 121 | 13

101 | 121 | 9

100 | 121 | 13

This is what it suppose to look like.

rID  |  qID  |  cID  -- CORRECT OUTPUT

103 | 121 | 2

101 | 121 | 9

100 | 121 | 13

I've been working on this for a week, can you help me?

Thank you



+Pie Number of slices to send: Send
You need to stick some debugging statements in there to log where in your code you have got to and what the values are.
At the moment you are guessing that it is going into the UPDATE section of your code, but since new lines are being inserted into the database it has to be going into the INSERT part.
+Pie Number of slices to send: Send
mmm so should this be and insert and select?
+Pie Number of slices to send: Send
Welcome to the Ranch

Use code tags instead of the quote button, which will make your post look much better. Also line 3 is too long. Since you are new, I have corrected both things for you.
+Pie Number of slices to send: Send
Hi Deon, welcome to the Ranch!

I fixed up the code tags for you, they were so nearly right It makes the code much easer to read don't you think?

I hope you get your problem sorted out swiftly.
Staff note (Tim Cooke) :

Unknown to each other, Campbell and I were tweaking the code tags at the same time.

+Pie Number of slices to send: Send
 

Deon Gee wrote:mmm so should this be and insert and select?



No, what I'm saying is that, for whatever reason (and debugging logs will help here) your code is not going into the updateCol1 method.  It is going into the addCol1 method.
To figure out why then you need logs.
+Pie Number of slices to send: Send
How do I check if record exist so that I will not insert duplicate records?  For example the arrayColId[i] should not be duplicated.
+Pie Number of slices to send: Send
What database are you using? For example, the merge statement might be available in which case you can have the database do that check for you.
+Pie Number of slices to send: Send
I'm using Oracle.
+Pie Number of slices to send: Send
Great. Oracle has this merge statement as well. You give it different logic if the row already exists vs is new.
+Pie Number of slices to send: Send
I'm using prepared statement is that okay, and JDBC from JAVA DAO.
+Pie Number of slices to send: Send
Oh and this is not two different tables, it is updating on one table.  Does merge apply to this?
+Pie Number of slices to send: Send
Yes, you can use merge with one table.

A PreparedStatement just calls SQL behind the scenes so that is ok.
Slideshow boring ... losing consciousness ... just gonna take a quick nap on this tiny ad ...
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 3707 times.
Similar Threads
How to insert data into a MySQL database
inserting multiple rows in database from struts 2
Updating Problem
How to use preparedStatements to store data to two different tables
Inserting date into an MS Access DB
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 18, 2024 22:28:53.