asked on

Updating users details

Hi there im trying to do an admin option where there is a drop down list of usernames to select from on the first page.

selectmember.jsp
here is the code this works fine:
----------------------------------

<%
Statement statement = null;
ResultSet resultset = null;
ResultSetMetaData rsmd = null;
Connection connection = null;

------------------connection stuff---------------------------------

ResultSet rs = statement.executeQuery("SELECT UserName FROM Member");
String name = null;
ArrayList list = new ArrayList();
while ( rs.next() ) {
name=rs.getString("UserName");
list.add(name);
}
%>
<%
{
connection.close();
}

%>
</p>

<p>
<form name="frm1" id="frm1" method="post" action="https://www.bcdtesting.com/selectedmember.jsp" onSubmit="return validateForm(frm1)">

<select name="name">
<%
if(list != null)
{
for(int i =0; i < list.size(); i++)
{
String value = (String) list.get(i);
%>
<option value="<%=value%>"><%=value%></option>

<%}
}
else
{%>
<option value=dummy>None</option>
<%}%>
</select>
<input type="submit" name="Submit3" value="Send">

------------------------

my next page is called selectedmember.jsp
This also works fine:
------------------

<%
Statement      Statement = null;
ResultSet resultset = null;
ResultSetMetaData rsmd = null;
Connection      connection = null;

-------------------connection stuff--------------------------------

String name = "'" + request.getParameter("name") + "'";
Statement = connection.createStatement();
ResultSet rs24 = Statement.executeQuery("SELECT * FROM Member WHERE UserName ="+name);

%>
<table width="81%" border="0" align="center" cellpadding="0" cellspacing="0">
<%while (rs24.next ()){%>

<tr>
<td colspan="3" valign=bottom class="subtitle"><div align="center">
<p><strong>Please Review Users Details</strong></p>
<p> </p>
</div></td>
</tr>
<form name="frm1" id="frm1" method="post" action="selectedmember1.jsp" onSubmit="return validateForm(frm1)">
<tr align="left" valign="top">
<td valign="center" height="35" colspan="4">
<p>
<table width="97%" height="362" border="0" align="center" cellpadding="3" cellspacing="0">

<td width="24%" colspan="4" rowspan="2" align=right valign=top>  </td>
</tr>
<tr>
<td height="4" colspan="2" align="right" valign="bottom" bgcolor="#ffffff" class="subtitle"><div align="left"></div> <div align="left"></div></td>

</tr>
<tr valign="bottom">
<td align="right" bgcolor="#ffffff" valign="bottom" class="subtitle">
<div align="left"><b>First Name:</b></div></td>
<td align="left" valign="bottom" colspan=2>
<input name="FirstName" size="30" maxlength="30" tabindex="4" value="<%=rs24.getString ("FirstName")%>">
</td>
</tr>
<tr>
<td align="right" valign="bottom" bgcolor="#ffffff" class="subtitle"><div align="left"><b>Last Name:</b></div></td>
<td colspan=3 align="left" valign="bottom">
<input name="LastName" size="30" maxlength="30" tabindex="5" value="<%=rs24.getString ("LastName")%>">
</td>
</tr>
<tr>
<td align="right" valign="bottom" bgcolor="#ffffff" class="subtitle"><div align="left"><b>Address:</b></div></td>
<td colspan=3 align="left" valign="bottom">
<input name="Address1" size="35" maxlength="30" tabindex="8" value="<%=rs24.getString ("Address1")%>"> </td>
</tr>
<tr>
<td align="right" valign="bottom" bgcolor="#ffffff"><div align="left"></div></td>
<td colspan=3 align="left" valign="bottom">
<input name="Address2" size="35" maxlength="30" tabindex="9" value="<%=rs24.getString ("Address2")%>"> </td>
</tr>
<tr>
<td align="right" valign="bottom" bgcolor="#ffffff" class="subtitle"><div align="left"><strong>Town:</strong></div></td>
<td colspan=3 align="left" valign="bottom">
<input name="Town" size="35" maxlength="25" tabindex="10" value="<%=rs24.getString ("Town")%>">
</td>
</tr>
<tr>
<td align="right" valign="bottom" bgcolor="#ffffff" class="subtitle"><div align="left"><strong>County:</strong></div></td>
<td colspan=3 align="left" valign="bottom">
<select name="County">

<OPTION selected ><%=rs24.getString ("County")%></OPTION>

<option value="Carlow">Carlow</option>
<option value="Cavan">Cavan</option>
<option value="Laois">Laois</option>
<option value="Leitrim">Leitrim</option>
<option value="Limerick">Limerick</option>
<option value="Longford">Longford</option>
<option value="Louth">Louth</option>
<option value="Wexford">Wexford</option>
<option value="Wicklow">Wicklow</option>
<option>None of the Above</option>
</select>
</td>
</tr>
<tr>
<td height="30" align="right" valign="bottom" bgcolor="#ffffff" class="subtitle"><div align="left"><strong>Mobile No.: </strong></div></td>
<td colspan=3 align="left" valign="bottom"><input name="Mobile" size="35" maxlength="25" tabindex="10" value="<%=rs24.getString ("Mobile")%>"></td>
</tr>
<tr>
<td height="29" align="right" valign="bottom" nowrap bgcolor="#ffffff" class="subtitle">
<div align="left"><strong>Home Phone:</strong></div></td>
<td colspan=3 align="left" valign="bottom">

<input name="AreaCode" id="AreaCode" tabindex="20"onKeyUp="countMeHomePhone1(this.value);" maxlength="4" size = "4" value="<%=rs24.getString ("AreaCode")%> ">

<input name="Phone" id="Phone" tabindex="21"onKeyUp="countMeHomePhone2(this.value);" size="15" maxlength="13" value="<%=rs24.getString ("Phone")%> ">
</td>
</tr>
<tr>
<td height="37" align="right" valign="bottom" bgcolor="#ffffff" class="subtitle">
<div align="left"><strong>Email Address:</strong></div></td>
<td colspan=3 align="left" valign="bottom">
<input name="EmailAddress" size="35" maxlength="40" tabindex="27" value="<%=rs24.getString ("EmailAddress")%>"> </td>
</tr>
<tr>
<td height="25" align="right" valign="bottom" bgcolor="#ffffff" class="subtitle">
<div align="left"><b>Gender:</b></div></td>
<td colspan=3 align="left" valign="bottom"><font face="Arial, Helvetica, san-serif" size="-1">
<input type="radio" name="Gender" tabindex="32" value="Male"checked>
Male
<input type="radio" name="Gender" tabindex="33" value="Female">
Female</font></td>
</tr>
<tr>
<TD height="40" align=right vAlign=bottom bgColor=#ffffff class=subtitle>
<DIV align=left>
<p><B>Date of Birth:</B></p>
</DIV></TD>
<TD vAlign=bottom align=left colSpan=3>
<TABLE width="78%" height="28" border=0 cellPadding=1 cellSpacing=1>
<TBODY>
<TR>
<TD height="20"><SELECT name=Day>
<OPTION selected ><%=rs24.getString ("Day")%></OPTION>
<OPTION value=01>01</OPTION>
<OPTION
value=02>02</OPTION>
<OPTION value=03>03</OPTION>
<OPTION
value=04>04</OPTION>
<OPTION value=05>05</OPTION>
<OPTION
value=06>06</OPTION>
<OPTION value=07>07</OPTION>
<OPTION
value=08>08</OPTION>
<OPTION value=09>09</OPTION>
<OPTION
value=10>10</OPTION>
<OPTION value=11>11</OPTION>
<OPTION
value=12>12</OPTION>
<OPTION value=13>13</OPTION>
<OPTION
value=14>14</OPTION>
<OPTION value=15>15</OPTION>
<OPTION
value=16>16</OPTION>
<OPTION value=17>17</OPTION>
<OPTION
value=18>18</OPTION>
<OPTION value=19>19</OPTION>
<OPTION
value=20>20</OPTION>
<OPTION value=21>21</OPTION>
<OPTION
value=22>22</OPTION>
<OPTION value=23>23</OPTION>
<OPTION
value=24>24</OPTION>
<OPTION value=25>25</OPTION>
<OPTION
value=26>26</OPTION>
<OPTION value=27>27</OPTION>
<OPTION
value=28>28</OPTION>
<OPTION value=29>29</OPTION>
<OPTION
value=30>30</OPTION>
<OPTION
value=31>31</OPTION>
</SELECT>
 
<SELECT name=Month>
<OPTION selected ><%=rs24.getString ("Month")%></OPTION> <OPTION
value=01>01</OPTION>
<OPTION value=02>02</OPTION>
<OPTION
value=03>03</OPTION>
<OPTION value=04>04</OPTION>
<OPTION
value=05>05</OPTION>
<OPTION value=06>06</OPTION>
<OPTION
value=07>07</OPTION>
<OPTION value=08>08</OPTION>
<OPTION
value=09>09</OPTION>
<OPTION value=10>10</OPTION>
<OPTION
value=11>11</OPTION>
<OPTION value=12>12</OPTION>
</SELECT>
<SELECT name=Year>
<OPTION selected ><%=rs24.getString ("Year")%></OPTION> <OPTION value=1996>1996</OPTION>
<OPTION value=1995>1995</OPTION>
<OPTION value=1994>1994</OPTION>
<OPTION value=1993>1993</OPTION>
<OPTION value=1992>1992</OPTION>
<OPTION value=1991>1991</OPTION>
<OPTION value=1990>1990</OPTION>
<OPTION value=1940>1940</OPTION>
</SELECT> </TD> </tr></table></td></tr> </table>
<table width="95%" height="77" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<th colspan="2" scope="row"><div align="center">
<p> </p>
<p><u>Memorable Data - In case of lost Password (optional)</u></p>
<p>  </p>
</div></th>
</tr>
<tr>
<th width="42%" scope="row">
<div align="left"><strong>Memorable Data Type: </strong> </div></th>
<td width="58%">
<SELECT name=MemDataType>
<OPTION selected ><%=rs24.getString ("MemDataType")%></OPTION> <OPTION
value=Name>Mother's Maiden Name</OPTION>
<OPTION value=Colour>Favourite Colour</OPTION>
<OPTION
value=Place>Memorable Place</OPTION>
<OPTION value=Date>Memorable Date</OPTION>
</SELECT>
 </td>
</tr>
<tr>
<th scope="row">
<div align="left"><strong>Memorable Data: </strong> </div></th>
<td><input name="MemData" type="text" size="23" maxlength="60" value ="<%=rs24.getString ("MemData")%>">
</td>
</tr>
</table>
<br>
<tr>
<td colspan="3" align="right" valign="center"></td>
<td width="1%" align="left" valign="center"> </td>
</tr>
<tr>
<td width="3%" height="24"></td>
<td width="38%"></td>

<td width="58%"><input type="submit" name="Submit" value="Update Details"></td>
<td>  </td>
</tr>
</form>
</table>
<%}%>

<%
{
connection.close();
}

%>

</form>
The update statement is not working thats my only problem on the 3 pages its not giving an error just not updating the page
This is my code for the third page called selectedmember1.jsp:
-------------------------------------

<%

Statement      statement = null;
ResultSet      resultset = null;
Connection connection = null;

--------------------connection stuff-------------------------

statement = connection.createStatement();
%>

<%
String name = "'" + request.getParameter("name") + "'";
String last = "'" + request.getParameter("LastName") + "'" ;
String Address1 = "'" + request.getParameter("Address1") + "'";
String Address2 = "'" + request.getParameter("Address2") + "'";
String Town = "'" + request.getParameter("Town") + "'";
String County = "'" + request.getParameter("County") + "'";
String Mobile = "'" + request.getParameter("Mobile") + "'";
String AreaCode = "'" + request.getParameter("AreaCode") + "'";
String Phone = "'" + request.getParameter("Phone") + "'";
String EmailAddress = "'" + request.getParameter("EmailAddress") + "'";
String Gender = "'" + request.getParameter("Gender") + "'";
String Day = "'" + request.getParameter("Day") + "'";
String Month = "'" + request.getParameter("Month") + "'";
String Year = "'" + request.getParameter("Year") + "'";
String MemDataType= "'" + request.getParameter("MemDataType") + "'";
String MemData= "'" + request.getParameter("MemData") + "'";

statement = connection.createStatement();

String query = "Update Member set FirstName = " + name + ", LastName = " + last + ", Address1 = " + Address1 +", Address2 = " + Address2 +", Town = " + Town +", County = " + County +", Mobile = " + Mobile +", AreaCode = " + AreaCode +", Phone = " + Phone +", EmailAddress = " + EmailAddress +", Gender = " + Gender +", Day = " + Day +", Month = " + Month +", Year = " + Year + ", MemDataType =" + MemDataType +", MemData = " + MemData +" WHERE UserName ="+name;

System.out.println ( "<TT>" + query + "</TT>" ) ;

statement.execute( query);

%>

Thank you for updating your details <a href="adminoptions.jsp">Return</a>

<%
{
connection.close();
}
%>

acslater

ASKER

Any ideas anyone its just my update statement thats wrong

String query = "Update Member set FirstName = " + name + ", LastName = " + last + ", Address1 = " + Address1 +", Address2 = " + Address2 +", Town = " + Town +", County = " + County +", Mobile = " + Mobile +", AreaCode = " + AreaCode +", Phone = " + Phone +", EmailAddress = " + EmailAddress +", Gender = " + Gender +", Day = " + Day +", Month = " + Month +", Year = " + Year + ", MemDataType =" + MemDataType +", MemData = " + MemData +" WHERE UserName ="+name;

Its the where part that im not sure about??????????

bloodredsun

you need to put single quotes around your String values
e.g.

String query = "Update Member set FirstName = '" + name + "',.....blah blah

but you really would be better off using a PreparedStatement as no quotes are required and it's much faster

TimYates

you have to put single quotes round string parameters...

ie:

String query = "Update Member set FirstName = '" + name + "', LastName = '" + last + "', Address1 = '" + Address1 +"', Address2 = '" + Address2 +"', Town = '" + Town +"', County = '" + County +"', Mobile = '" + Mobile +"', AreaCode = '" + AreaCode +"', Phone = '" + Phone +"', EmailAddress = '" + EmailAddress +"', Gender = '" + Gender +"', Day = " + Day +", Month = " + Month +", Year = " + Year + ", MemDataType ='" + MemDataType +"', MemData = '" + MemData +"' WHERE UserName ='"+name + "'" ;

Or....use a PreparedStatement (which tends to be a lot easier)

TimYates

LOL

GET OUT OF MY BRAIN!!! :-D

Nnnnng! ;-)

bloodredsun

HAHAHAHA....

You using HTMLAlert yet? What you don't know is that it secretly sends me what you're doing so I can get in there first,
MWAH HAHAHA, I will take over all of EE with my secret weapon!!!

acslater

ASKER

Yea but its what i should put in the where part im unsure of

TimYates

is username your ID? If so, that's fine!

You were just passing invalid SQL, and of course anyone typing a ' into any of those fields would crash it too...

bloodredsun

>>anyone typing a ' into any of those fields would crash it too...

another reason to use PreparedStatements as the JDBC driver does it for you...

ASKER CERTIFIED SOLUTION

bloodredsun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

acslater

ASKER

nice one