Problems while doing update

[maas]

Hello All,

I am having difficulties while doing the update:

The first step is that I retrieved the data:

String query ="select n.nationality_name from NATIONALITY_LKUP n, cir c where n.country_code=c.nationality

Then I assigned the value:

Nationality= rs2.getString("nationality_name");
if (Nationality== null || Nationality.equals("")) { Nationality="";}

The drop down list items:

<select size="1" name="Nationality" class="content">
		<option selected value="<%=Nationality%>"><%=Nationality%></option>
				
<%
  
 	 try
 	 {
 	 
   		ResultSet rs=null;
  		
  		Statement st1=null;
		
		String query = "select country_code, nationality_name from nationality_lkup ";

		st1 = conn1.createStatement();
		
		rs = st1.executeQuery(query);
        
		while(rs.next())	
	{

%>
	<option value="<%=rs.getString("country_code")%>" >
        <%=rs.getString("nationality_name")%></option>
<%
		}
		
	}
	catch (Exception e) {
      e.printStackTrace();
    }
%>

			</select>

In the update statement:

String  sz_SQLUpdate = "UPDATE cir SET";
z_SQLUpdate = sz_SQLUpdate  + " , nationality ='"+Nationality+"'";

Now while updating, instead of putting the code of the country, it is storing the whole country name

e.x, it was UK, Now United Kingdom.

Can you please help

[mangstadt]

Now while updating, instead of putting the code of the country, it is storing the whole country name

Then you're not putting in the correct value in the update SQL statement. What is the "Nationality" variable even for? It looks like all you're doing is setting it to the value of the first result of the select statement.

Also, you should use the PreparedStatement class for when a SQL query has parameters.

PreparedStatement ps = connection.prepareStatement("UPDATE cir SET nationality = ?");
ps.setString(1, Nationality);

[Tolls]

How does the first bit of your post (with the query and the rs2) relate to the main chunk of JSP that you have given us?

I take it that supplies a single Nationality (variables start with a lower case by the way, class names start with an upper case) which seems to be used to give the first option for the drop down list. This doesn't make much sense to me.

What are you trying to achieve?

[arun9683]

you are not getting the value of the field properly in the business logic layer.

Please do check ..

And by the help of code you have provided its not possible to debug..

[maas]

I am trying to update the drop down list values in the database.

Is there is an example on how to update.

Now the form has retrieved the values and place them in the drop down list.

So, now how can I update the DB.

[arun9683]

request.getParameter("Nationality") will give you the value of the select box.

Use prepared statement :

PreparedStatement ps = connection.prepareStatement("update table set columnName = ?");
ps.setString(1,request.getParameter("Nationality") );
ps.executeUpdate();

[maas]

request.getParameter("Nationality") will give you the value of the select box.

Use prepared statement :

PreparedStatement ps = connection.prepareStatement("update table set columnName = ?");
ps.setString(1,request.getParameter("Nationality") );
ps.executeUpdate();

Hello Arun,

This result gave me the name, So in case I want the code what shall I do?

select size="1" name="Nationality" class="content">
		<option selected value="<%=Nationality%>"><%=Nationality%></option>
				
<%
  
 	 try
 	 {
 	 
   		ResultSet rs=null;
  		
  		Statement st1=null;
		
		String query = "select country_code, nationality_name from nationality_lkup ";

		st1 = conn1.createStatement();
		
		rs = st1.executeQuery(query);
        
		while(rs.next())	
	{

%>
	<option value="<%=rs.getString("country_code")%>" >
        <%=rs.getString("nationality_name")%></option>
<%
		}
		
	}
	catch (Exception e) {
      e.printStackTrace();
    }
%>

			</select>

[Tolls]

This is what's confusing me here.
You have a <select> being built, but the options are:

<option selected value="<%=Nationality%>"><%=Nationality%></option>

<option value="<%=rs.getString("country_code")%>" >
        <%=rs.getString("nationality_name")%></option>

The first uses Nationality for its value and display, whereas all the rest have a value based on country_code. Surely that's not correct?

[arun9683]

Instead of your query use the below query

String query ="select n.country_code,n.nationality_name from NATIONALITY_LKUP n, cir c where n.country_code=c.nationality";


String NationalityCode = rs2.getString("n.country_code");


<select size="1" name="Nationality" class="content">
				
<%
  
 	 try
 	 {
 	 
   		ResultSet rs=null;
  		
  		Statement st1=null;
		
		String query = "select country_code, nationality_name from nationality_lkup ";

		st1 = conn1.createStatement();
		
		rs = st1.executeQuery(query);
        
		while(rs.next())	
	{

%>
	<option value="<%=rs.getString("country_code")%>" <% if(rs.getString("country_code").equals(NationalityCode) ) out.print("selected");%>
        <%=rs.getString("nationality_name")%></option>
<%
		}
		
	}
	catch (Exception e) {
      e.printStackTrace();
    }
%>

			</select>

Use the above code..

It wil solve your problem

[maas]

Thank you arun

I will try this and modify the whole code since there are other 5 drop down lists in my jsp.

I hope that the value will be displayed to the user as United Kingdom and the stored will be UK

[Tolls]

And since I've managed to bite my tongue so far, a lot of this should really be done in a servlet, or at least via tags. Large amounts of <% ... %> makes what should be a display only thing very difficult to read, and harder to maintain. Of course if you're learning Java at the moment and haven't got to tags then that's understandable.

Oh, and that code given above doesn't close its resources...another reason for not doing this in a JSP directly. It's easy to miss the flow.

[RamyaSivakanth]

Hi,
One more suggestion you can't directly use ur results sets in condition checking or other things.

<option value="<%=rs.getString("country_code")%>" <% if(rs.getString("country_code").equals(Nationality Code) ) out.print("selected");%>
<%=rs.getString("nationality_name")%></option>

In the above code you will get problem,for each and every rs.getString(),record pointer will move to next record.U will not get the same one for all the above gets.

So,store it in some variable and use it.

[arun9683]

No RamyaSivakanth.. rs.getString() will not point to next record...

rs.next() will point to the next record..

I believe you got confused..

[RamyaSivakanth]

oh sorry....really I was thinking about rs.next I hve written :-

[maas]

Hello arun,

I have tried it in another way and success.

Thanks, but I also need some help.

In case if the user changed all the fields it will accept the change and will be updated. But, if he only changed one field, a blank "" will be shown. So, how can I deal with this?

[arun9683]

Which fields ? Since i dont know the your form and the fields inside it, i cant comment on anything..

If you post the form then it will help in debugging the prob..