SQL INSERT problem - help me please


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: SQL INSERT problem - help me please

  1. #1
    Join Date
    Jun 2004
    Posts
    17

    SQL INSERT problem - help me please

    Hello,

    I'm having a problem with INSERT statement.

    There is a "ShowFinal.jsp" page, which is a list of candidates who selected from the second interview. The user picked some candidates from the list and conduct the 3rd interview. After he check suitable candidates(who are selected from the 3rd interview) from the list , enter basic salary for every selected candidate, enter date of interview and finally submit the form.

    These data should be save into these tables.

    FinalSelect(nicNo,date)
    EmpSalary(nicNo,basicSal)

    In this "ShowFinal.jsp" page, it validates the following conditions using JavaScript.

    1) If the user submit the form without checking at least one checkbox, then the system should be display an alert message ("Please select at least one candidate").

    2) If the user submit the form without entering the basic salary of that candidate which was checked, then the system should be display an alert message ("Please enter basic salary").

    These are working well. But my problem is how to wrote the "AddNewFinal.jsp" page to save these data into the db.

    Here is my code which I have wrote. But it points an error.
    "AddNewFinal.jsp"
    -----------------

    Code:
    String interviewDate = request.getParameter("date");
    String[] value = request.getParameterValues("ChkNicno");
    String[] bs = request.getParameterValues("basicSal");
    
    String sql ="INSERT INTO finalselect (nicNo,date) VALUES(?,?)";
    String sql2 ="INSERT INTO EmpSalary (nicNo,basicSal) VALUES(?,?)";
    
    for(int i=0; i < value.length; i++){
    	String temp = value[i];	
    	for(int x=0; x < bs.length; x++){
    			
    		String basic = bs[x];
    		pstmt2 = connection.prepareStatement(sql2);
    		pstmt2.setString(1, temp);
    		pstmt2.setString(2, basic);
    		int RowCount1= pstmt2.executeUpdate();
    	}
    			
    	pstmt1 = connection.prepareStatement(sql);
    	pstmt1.setString(1, temp);
    	pstmt1.setString(2, interviewDate);
    	int RowCount= pstmt1.executeUpdate();
    }
    ..........................................

    Here is the code for "ShowFinal.jsp".

    Code:
    <form name="ShowFinal" method="POST" action="AddNewFinal.jsp" onsubmit="return checkEmpty() && ValidateDate();">
    
    <%--  Loop through the list and print each item --%>
    <%
    	int iCounter = 0; //counter for incremental value
    	while (igroups.hasNext()) {
    		Selection s = (Selection) igroups.next();
    		iCounter+=1; //increment
    %>
    <tr>
    	<td style="background-color:ivory" noWrap width="20">
    	<input type="checkbox" name="<%= "ChkNicno" + iCounter %>" 	value="<%=s.getNicno()%>"></td> 
    
       	<td style="background-color:ivory" noWrap width="39">
       		<%= s.getNicno() %>&nbsp; </td>
       		
       	.....
    	.....
      	
    		
    	<td style="background-color:ivory" noWrap width="174">
    		<input type="text" name="<%= "basicSal" + iCounter %>" size="10">&nbsp;</td>
    
    	
    </tr>
    <%
       }
    %>
    Date of interview<input type="text" name="date" size="17"></td>
    
    <input type="submit" value="APPROVE CANDIDATE" name="B1" style="border: 1px solid #0000FF">
    </form>
    ........................................................
    Here is the error generated by TOMCAT.

    root cause

    java.lang.NullPointerException
    at org.apache.jsp.AddNewFinal_jsp._jspService(AddNewFinal_jsp.java:70)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)

    ...

    I have goto the file "AddNewFinal_jsp.java". The line 70 points to the following line.

    for(int i=0; i < value.length; i++){


    Please can someone help me to solve this problem? Please help me to do this task.
    Thanks.

  2. #2
    Join Date
    Jun 2004
    Posts
    199
    The code "String[] value = request.getParameterValues("ChkNicno");"
    must not be returning anything. I looks like the fields are actually that plus a counter value like ChkNicno1

  3. #3
    Join Date
    Jun 2004
    Posts
    17
    Hi,

    As I metion earlier, I want to validate these conditions in this "ShowFinal.jsp" page.

    1) If the user submit the form without checking at least one checkbox, then the system should be
    display an alert message ("Please select at least one candidate").

    2) If the user submit the form without entering the basic salary of that candidate which was
    checked, then the system should be display an alert message ("Please enter basic salary").

    So I wrote the following function using JavaScript to do this task. It's working fine. Here is the code for JavaScript.

    <script language="JavaScript">
    function checkEmpty(){
    var frm = window.document.ShowFinal;
    var selectedArray = Array();
    for(i=0;i<frm.elements.length;i++){
    if(frm.elements[i].type=="checkbox" && frm.elements [i].name.indexOf("ChkNicno") != -1){
    if(frm.elements[i].checked==true){
    var chkName = frm.elements[i].name;
    selectedArray[selectedArray.length] = chkName.substr("ChkNicno".length,chkName.length);
    }
    }
    }
    if(selectedArray.length == 0){
    alert("Please select at least one candidate")
    return false;
    }else{
    for(i=0;i<selectedArray.length;i++){
    var salField ;
    salField = eval ("window.document.ShowFinal.basicSal"+selectedArray[i]) if(salField){
    if(salField.value==""){
    alert("Please enter basic salary");
    salField.focus();
    return false;
    }
    }
    }
    }
    return true;
    }
    </script>


    Rest of the codes in the "ShowFinal.jsp" page are same as above(see top of the page). Based on the javaScript which I mention earlier, I have to include the following textbox and checkbox to the "ShowFinal.jsp" page as follows.

    <input type="checkbox" name="<%= "ChkNicno" + iCounter %>" value="<%=s.getNicno() %>">
    <input type="text" name="<%= "basicSal" + iCounter %>"


    In my AddNewFinal.jsp, I have replace the following part and try it again.

    request.getParameterValues("ChkNicno"); replace with
    String[] value = request.getParameterValues("ChkNicno" + iCounter);

    It also gives me an error. Cannot resolove symbol :iCounter

    Any way, is there any way to do this tasks?(Validation using JavaScript and saving using JSP)
    If YES : what is the method?

    If No: then is there any facility to validate the above 2 conditions and finally save them to db by using JSP? and what is the method?

    Please help me to solve this. If you need any more details,feel free to ask.

    Thanks.

  4. #4
    Join Date
    Jun 2004
    Posts
    199
    iCounter is not declared in the JSP code. You will need to loop inside JSP tags to get all the values. Just like you did in the Javascript.

    The Javascript and the JSP code know nothing about each other. JSP code can create Javascript but for all it knows, it is just string manipulation.

  5. #5
    Join Date
    Jun 2004
    Posts
    17
    Hello mnuttall,

    Thanks for your reply. But I can't understood what you are saying. What is the meaning of "You will need to loop inside JSP tags to get all the values. Just like you did in the Javascript".
    In which file am I need to do this?

    I have declared the iCounter in my "GetFinal.jsp" page.
    <%-- Loop through the list and print each item --%>
    <%
    int iCounter = 0; //counter for incremental value
    while (igroups.hasNext()) {
    Selection s = (Selection) igroups.next();
    iCounter+=1; //increment
    %>

    You can see it at the top of the page also.
    But iCounter variable is not in the AddNewFinal.jsp page. Now my problem is:-

    -why this iCounter is not available in the "AddNewFinal.jsp" page?
    (I declared it between <% ... %>)

    I'm new to JSP. Could you please explain the method that you told me above. Specially, I want to know how to code this part.

    request.getParameterValues("ChkNicno"); or
    String[] value = request.getParameterValues("ChkNicno" + iCounter); or ...............?

    Please help me to solve this.
    Thank you.

  6. #6
    Join Date
    Jun 2004
    Posts
    199
    It is really difficult to tell without seeing all the code.

    But here goes ...

    You need to declare the variable in each page the you use it unless the page is an include or includes an include page that declares it.

    Also, it needs to have the proper scope. A function won't be able to see a variable from another function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center