farhan ahmed butt

Howard Watson wrote:I think that is why you are struggling with this. And consider implementing it as a transaction.
conn.setAutoCommit(false); /*enable transaction*/ insert record to first table ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()){ int foreignKey = rs.getInt(1); insertStatement with foreignKey insertStatement with foreignKey } conn.commit(); /*commit transaction*/

Thanks a million dude.. You saved me! ))
If you could find little more time.. I have another issue Kindly check that as well...

https://coderanch.com/t/589165/JSP/java/Jsp-servlet-back-button

Howard Watson wrote:Just curious, are you doing something like this?
ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()){ yourKey = rs.getInt(1); foreignKey = yourKey; //this would not be necessary, just for clarity insertStatement with foreignKey insertStatement with foreignKey }

Well something like this..
Let me post the code...

Insert statement with ID which is required later as foreign key however without mentioning it in the statement so basically its auto generating.. ResultSet rs = stmt.getGeneratedKeys(); int ID=0; if (rs.next()){ ID = rs.getInt(1); } insertStatement with ID as a foreign key.. insertStatement with ID as a foreign key..

Bear Bibeault wrote:Please be sure to use code tags when posting code to the forums. Unformatted or unindented code is extremely hard to read and many people that might be able to help you will just move along to posts that are easier to read. Please click this link ⇒ UseCodeTags ⇐ for more information. Properly indented and formatted code greatly increases the probability that your question will get quicker, better answers.

Hi bear,
Just edited the code.. Kindly have a look now..

Bear Bibeault wrote:Please be sure to use code tags when posting code to the forums. Unformatted or unindented code is extremely hard to read and many people that might be able to help you will just move along to posts that are easier to read. Please click this link ⇒ UseCodeTags ⇐ for more information. Properly indented and formatted code greatly increases the probability that your question will get quicker, better answers.

thanks dude.. will do! :)

jatan bhavsar wrote:Hi Farhan,

Please Post some code here so we can help you better way.. if the values are stored in the session it must be avaiable.

Regards
Jatan

Hey bro just uploaded the code.. Kindly have a look and suggest me what to do...

Okay I will post all the code here alongwith description...

Okay here is code for first index called.. indexnpro.jsp which upon getting the values forwards them to Servletnpro like this.. <form class="form-class" name="myform" action="Servletnpro" method="POST"> Note: remember since architecture here is MVC so every jsp will have its own servlet so values are not going direclty to another jsp but a servlet which then passes them to next jsp..... Now in Servletnpro's dopost method...
String connectionURL = "jdbc:mysql://localhost/secnok?"+ "user=root&password="; Connection connection=null; response.setContentType("text/html"); PrintWriter out = response.getWriter(); HttpSession session = request.getSession(true); //these are different values I am getting from indexnpro.jsp, some of them are dropdown boxes but I shouldnt really matter.. String exproject=request.getParameter("country"); String corrcust=request.getParameter("state"); String excust=request.getParameter("country1" ); String corrproject=request.getParameter("state1"); String corrtoepost= request.getParameter("corrtoe");
<--------------- this value I keep on losing when pressing back button.. It changes to null when it comes back again to this servlet..

System.out.println(corrtoepost); System.out.println("doPost is running"); System.out.println("Session id on dopost: " + session.getId() ); request.setAttribute("corrtoepost", corrtoepost); <--------------- storing value in session, still i keep on losing it..
/////////// From now is the simple code of database connectivity which will connect to database fetch the data based on user choice namely "corrtoepost" that I just stored in session.. and then will display it back on indexa.jsp ..


try{ int rs1; // Load the database driver Class.forName("com.mysql.jdbc.Driver"); // Get a Connection to the database connection = DriverManager.getConnection(connectionURL); //Add the data into the database Statement stmt = connection.createStatement(); Statement stmt1 = connection.createStatement(); Statement stmt2 = connection.createStatement(); Statement stmt3 = connection.createStatement(); Statement stmt4 = connection.createStatement(); Statement stmt5 = connection.createStatement(); Statement stmt6 = connection.createStatement(); ResultSet rs = stmt.executeQuery( "Select * from toe_description where toe_id= '" + corrtoepost + "' " ) ; ResultSet rs2 = stmt1.executeQuery( "Select * from toe_text where type_id=1 AND toe_id= '" + corrtoepost + "' " ) ; ResultSet rs3 = stmt2.executeQuery( "Select * from toe_text where type_id=2 AND toe_id= '" + corrtoepost + "' " ) ; ResultSet rs4 = stmt3.executeQuery( "Select * from toe_text where type_id=3 AND toe_id= '" + corrtoepost + "' " ) ; ResultSet rs5 = stmt4.executeQuery( "Select * from toe_text where type_id=4 AND toe_id= '" + corrtoepost + "' " ) ; ResultSet rs6 = stmt5.executeQuery( "Select * from toe_text where type_id=5 AND toe_id= '" + corrtoepost + "' " ) ; ResultSet rs7 = stmt6.executeQuery( "Select * from toe_text where type_id=6 AND toe_id= '" + corrtoepost + "' " ) ; while(rs.next()){ String toeid= rs.getString(1); String toename= rs.getString(2); String Intname= rs.getString(4); String Assetname= rs.getString(5); String Objname= rs.getString(6); String ProjectID= rs.getString(7); request.setAttribute("toeid", toeid); request.setAttribute("toename", toename); request.setAttribute("Intname", Intname); request.setAttribute("Assetname", Assetname); request.setAttribute("Objname", Objname); request.setAttribute("ProjectID", ProjectID); while(rs2.next()){ String purpose= rs2.getString(4); request.setAttribute("purpose", purpose); while(rs3.next()){ String scope= rs3.getString(4); request.setAttribute("scope", scope); System.out.println(scope); while(rs4.next()){ String toe_desc= rs4.getString(4); request.setAttribute("toe_desc", toe_desc); System.out.println(toe_desc); while(rs7.next()){ String toe_ass= rs7.getString(4); request.setAttribute("toe_ass", toe_ass); while(rs6.next()){ String enviroment= rs6.getString(4); request.setAttribute("enviroment", enviroment); while(rs5.next()){ String ass_env= rs5.getString(4); request.setAttribute("ass_env", ass_env); } } } } } } } request.getRequestDispatcher("/WEB-INF/indexa.jsp").forward(request, response);
// here I am sending all the data fetched from db to the required jsp which will then dislpay the data.. Namely "indexa.jsp"

System.out.println("Connected to the database"); connection.close(); System.out.println("Disconnected from database"); } catch (Exception e) { e.printStackTrace(); } } }
////////Now code Of Indexa.jsp

This page will show data fetched from Servletnpro discussed earlier..

Under body tag of page I retrieve that value of "corrtoepost" which I saved earlier in Servletnpro in a session.. I can see this value as of now..

String corrtoepost1=request.getParameter("corrtoepost"); String corrtoepost=(String) session.getAttribute("corrtoepost"); session.setAttribute("corrtoepost",corrtoepost);
and then display it on jsp page.. which is showing the right value as of now..

Now code for the next button in this page Remember next button is suppose to take me to next page with same correcttoepost value which I got from servlet.

Next

Now lets go to next page which is indexb.jsp

Here I again get the value of "corrtoe" from indexa.jsp like this..

String corrtoe=(String) session.getAttribute("corrtoe"); session.setAttribute("corrtoe",corrtoe);
Uptill Now I am getting the right value even on this page... Problem Arises once I press back button on this page....

Now code for back button on this page..
<a href="Servletnpro?Toe=<%=corrtoe%>" > <img src="" alt="Previous"/> Previous </a>
Now when I press this previous button I lose the toe value in indexa.jsp page and it shows corrtoe value on interface as null..... After this I tried to use Servlets Get method to get the value which would work once but after pressing next on indexa.jsp it would show null on indexb.jsp and rest of the pages... Problem is that why am I losing this value when I am storing it in session as well.!!! Kindly give me some idea about....

Another question is that when I press submit on indexnpro page why does it shows Servletnpro in address bar instead of indexa.jsp ... However it does shows the page indexa.jsp populated with values from indexa.jsp... I think this is the reason why I keep on losing the "corrtoe" value between jsp pages..

Mukesh Negi wrote:@farhan: I'l stick to my above post. This will serve your purpose.
You first insert into your main table where there is auto increment value.

Than by using the following code you will be getting last inserted key. i.e., your last auto incerement value as database system stores the last key value inserted.

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
yourKey = rs.getInt(1);
}

After getting the auto increment value you can insert it into the tables where it is the foreign key.

Hope this will help you.

Hi Mukesh...
As I previously mentioned problem is that the key I need to use out side... which in your code is "yourkey" is declared inside the if statement and cant be access outside it... and if declare it outside if then it will ask me to initialize it aswell which upon doing will result in same problem.. Let me quote you an example...


int youtkey=0;
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
yourKey = rs.getInt(1);
}

now the code is compileable but still will get the same error afterwards... Any suggestions!??

Shrenik Patel wrote:Hi Farhan,

continuing from the last post, as Paul suggested you don't need to externally specify the auto increment value, let database handle this id generator. You just focus on inserting other values in the tables. After filtering your query looks like:

String sql = "INSERT INTO toe_description (toe_name , Date , interface_id ) VALUES ('" + toe_name + "' ,'" + (dateFormat.format(date)) + "','" + InterfaceName + "' )"; String sql1 = "INSERT INTO toe_text (toe_id, type_id , text) VALUES (LAST_INSERT_ID(), '1' , '" + Purpose + "')"; String sql2 = "INSERT INTO toe_text (toe_id, type_id , text) VALUES (LAST_INSERT_ID(), '2' , '" + Scope + "')";

where in first query toe_id will get generated by database Auto_Increment. where in sql1 & sql2, LAST_INSERT_ID() will be the latest toe_id which was generated in first query.

Thanks

,

Just tried this method and its giving me this error

SQLException caught: Cannot add or update a child row: a foreign key constraint fails (`secnok`.`toe_text`, CONSTRAINT `toe_text_ibfk_1` FOREIGN KEY (`toe_id`) REFERENCES `toe_description` (`toe_id`))
So basically problem is that it is inserting sql1 query but when it moves to sql2 its losing the lastt toe_id value I assume thus giving this error!? what should I do now!?

Howard Watson wrote:I've always used something like this in a transaction:
stmt.executeUpdate(sqlInsert); /*retrieve new ID*/ sql = "SELECT @@IDENTITY"; rs = stmt.executeQuery(sql); if (rs.next()) { int ID = rs.getInt(1); } /*use ID however you want*/
But, the MySQL documentation:
http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id

Okay this seems legitimate but the problem is that ID variable is not initialized outside the if statement.. I have tried it several times but since your ID is not initialized outside IF.. If i do initialize it outside if like int ID; then java keeps on giving error that "ID" in this case must be initialized.. and if I initialize it outside if with int ID=0; then it will duplicate the key next time it executes... What should I doo.. em stuck here.. ((

Mukesh Negi wrote:@farhan: Use the following code.

Statement stmt = db.prepareStatement("your query", Statement.RETURN_GENERATED_KEYS);

stmt.executeUpdate();

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
yourKey = rs.getInt(1);
}

Hey guys.. Probelm is that I cannot direclty insert in the database with this code.... I think I shuold give you guys an idea.. Thing is that I do know the auto increment method is a better one but my scnerio is a bit different.. Actually I have to get the toe_id value beforehand because I am using it to execute multiple queries after that.....

For example....

ResultSet rs=st.executeQuery("SELECT toe_id FROM toe_description");
int toe_id=0;
if(rs.last()){
System.out.println(toe_id);
toe_id=rs.getInt("toe_id")+1;

System.out.println(toe_id);


}

// So unless I get the exact value of toe_id, how am I suppose to give it later in the queries below which are dependent on this value..

Here I want to get the toe_id field and then use it like this afterwards in the same servlet..

String sql = "INSERT INTO toe_description ( toe_id, toe_name , Date , interface_id ) VALUES ('" + toe_id + "' ,'" + toe_name + "' ,'" + (dateFormat.format(date)) + "','" + InterfaceName + "' )";

String sql1 = "INSERT INTO toe_text (toe_id, type_id , text) VALUES ('" + toe_id + "' , '1' , '" + Purpose + "')";
String sql2 = "INSERT INTO toe_text (toe_id, type_id , text) VALUES ('" + toe_id + "' , '2' , '" + Scope + "')";

So basically I have to use same toe_id value in other tables namely toe_text and toe_description since toe_id is the foreign key in those tables and cannot be changed with an increment of new query.....

em really stuck into a problem so need help from you guys.. Actually I am creating a simple website in j2ee however I am not using beans since I want to keep it simple.. the structure of website is like I have different Jsp pages and every jsp page has a corresponding servlet which then connects the jsp page to database to store and retrieve data..

Now the db structure of my application requires that I should know about the primary key that is going to be entered in the db before pressing submit in the form of jsp page.. Lets say I have two tables...
ToeDescription and ToeText..
ToeDescription has ToeID and ToeName etc and
ToeText has ToeID as foreign key among other fields and I have to update in both of them at the same time as per db schema.. So I need to know next ToeID of ToeDescription beforehand in order to maintain the foreign key constraint of ToeText.. Auto increment in mysql simply wont work as toeid has to be same for all the toetext tables toeid fields... I searched and got this algo from internet but It wont add after few increments after which it gives error of "Duplicate primary key"....



ResultSet rs=st.executeQuery("SELECT toe_id FROM toe_description");
int toe_id=0;
if(rs.last()){
System.out.println(toe_id);
toe_id=rs.getInt("toe_id")+1;

System.out.println(toe_id);

This algo however seems perfect since it takes the last primary key and increments it by 1 so we can know in our servlet about the value which will be entered in the table however it gives an duplicate primary key error after inserting few values in the table.. Do you guys know of any other algo which would work or any other method of resolving this problem.???

Hey folks.. em really stuck into a problem so need help from you guys.. Actually I am creating a simple website in j2ee however I am not using beans since I want to keep it simple.. the structure of website is like I have different Jsp pages and every jsp page has a corresponding servlet which then connects the jsp page to database to store and retrieve data.. Now problem is that I have this one page index1 in which I select some option from drop down and then According to that option my next jsp page which is index2.jsp should load data from the database in its fields according to the option selected from index1.jsp... now index2 has a next and previous button.. I am storing the value of option selected from index1 to show on every jsp page just for now.. However when I click next on this index2.jsp it takes me to index3 with same option value but when I press previous button on index3 it takes me to the servlet of index2 which is Servlet2 in this case with the value of option being shown as NULL.. So it is kind of destroying the value from session when I press "previous" button. and after that all other pages show value of option as null as well. Another question is why I am having address of servlet instead of indexa when loading data into indexa from db using servlet.. Guys I really need help fast.. would really appreciate quick help..