auto populate values for text fields in jsp from sql server database

Question

i have a table called Members in sql server like this:

MemberId MemberName MemberAddress City      Pin     OpeningDate ClosingDate InstallmentAmount Status
100      Abilash    Hebbal        Mysore    570023  21-07-2016  22-07-2016  200               Active
102      rohit      Hootagalli    Mysore    570018  02-08-2016  18-08-2016  200               Active

I have a dropdown box which fetches MemberName from sql db as drop down values. As i select a MemberName in the dropdown, Text fields Member ID,Member Address,Member City,opening date and closing date need to be populated with corresponding values depending on the chosen membername from dropdown.

I have done displaying membernames from db into dropdown using JSTL in my jsp. like this:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>

    </head>
    <body>

        <form name="AddCollection" action="AddCollection" method="get">
     <sql:setDataSource var="pigmy" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
     url="jdbc:sqlserver://localhost:9900;DatabaseName=pigmy"
     user="pigmy"  password="pigmy"/>

       <sql:query dataSource="${pigmy}" var="result">
        SELECT MemberName from Members where Status='Active';
       </sql:query>
        Member:<select id="ClientDropdown" name="ClientDropdown" width="300"  style="width: 300px">
            <c:forEach var="row" items="${result.rows}">
            <option><c:out value="${row.MemberName}"/></option>
            </c:forEach>
               </select><br/><br/>


        Member ID:  <input type="text" name="MemberID" size="10"/><br/><br/>
        Member Address:  <input type="text" name="MemberID" size="10" /><br/><br/>
        Member City:  <input type="text" name="MemberID" size="10" /><br/><br/>
        Opening Date:  <input type="text" name="MemberID" size="10" /><br/><br/>
        Closing Date:  <input type="text" name="MemberID" size="10" /><br/><br/>


        Amount Collected: <input type="text" name="AmountCollected" value="0" size="40" /><br/><br/>
        Amount Collected on: <input type="date" name="CollectedDate" value="0" size="40" /><br/><br/>
        Collected for the Month: <input type="month" name="CollectedForMonth" value="0" size="40" /><br/><br/>

        <input type="submit" value="Submit" name="Submit" />
        </form>

    </body>
</html>

Please guide how do i proceed now. I know a bit of javascript, jquery and jstl along with servlet. I am going to fetch the auto populated text field values in my servlet for further processing. Please help me.

The simplest way would be IHMO to use [HTML data attributes](https://developer.mozilla.org/en/docs/Web/Guide/HTML/Using_data_attributes), i.e. adding to each `option` few data attributes (e.g. `data-member-id`, `data-member-address`, etc.) and using them in the ` — Jozef Chocholacek, Aug 02 '16 at 08:52
@Jozef: lets say, i have added two data attributes inside option tag in for each loop: I will add a js script file where i will write a function onchange for select tag: within this function how do i set values to text fileds? i mean how to fetch the data attribute of selected option? Thanks for your reply. — curiousboy, Aug 02 '16 at 09:11
Then use [this code](http://stackoverflow.com/a/12750327/3511123) to handle the `change` event of your `select` and get the selected option, and then the [.data(key)](https://api.jquery.com/data/#data2) jQuery function to get the values of `data` attributes. And then use Jquery [.val(value)](http://api.jquery.com/val/#val2) function to set the text fields' values (you need to name them properly, not all with the same name; even better - assign them a unique id). — Jozef Chocholacek, Aug 02 '16 at 09:19
Data attributes work when I have static options in select tag. But I am creating options in drop down from database, hence it's not possible to assign data attribute within for each loop jstl tag. :( — curiousboy, Aug 02 '16 at 18:33
Being in a good mood tonight, I've written an answer summarizing the hints into something more guiding. Note especially the change of the SQL command from `SELECT MemberName ...` to `SELECT * ...`. — Jozef Chocholacek, Aug 02 '16 at 19:26

score 2 · Accepted Answer · answered Aug 02 '16 at 19:23

Try something like the following (I've just summarized the hints from my comments):

...
<sql:query dataSource="${pigmy}" var="result">
  SELECT * FROM Members WHERE Status='Active'
</sql:query>
Member: 
<select id="ClientDropdown" name="ClientDropdown" width="300"  style="width: 300px">
  <c:forEach var="row" items="${result.rows}">
    <option data-member-id="${row.MemberId}" data-member-address="${row.MemberAddress}" data-member-city="${row.MemberCity}" ... ><c:out value="${row.MemberName}"/></option>
  </c:forEach>
</select>
<br/><br/>
Member ID:      <input type="text" name="MemberID" size="10"/><br/><br/>
Member Address: <input type="text" name="MemberAddress" size="10" /><br/><br/>
Member City:    <input type="text" name="MemberCity" size="10" /><br/><br/>
...
<script>
$(document).ready(function(){
  $('#ClientDropdown').on('change', function(event){
    var optionSelected = $("option:selected", this);
    var mID = $(optionSelected).data('memberId');  // note the data-member-id --> memberId change
    var mAddr = $(optionSelected).data('memberAddress');
    var mCity = $(optionSelected).data('memberCity');
    // ... read more data attributes
    $('input[name="MemberId"]').val(mID);
    $('input[name="MemberAddress"]').val(mAddr);
    $('input[name="MemberCity"]').val(mCity);
    // ... set more input field values
  });
});
</script>
...

Bingo! That works like charm!! Thank you so much Jozef! – curiousboy Aug 03 '16 at 04:53 — curiousboy, Aug 03 '16 at 04:53

auto populate values for text fields in jsp from sql server database

1 Answers1