PDA

Click to See Complete Forum and Search --> : JSP-JDBC Connectivity

Zenzii
06-21-2005, 03:02 PM
Hi All,

I'm having a prob with the foll code. I've to make a JSP page with a registration form and when I click the button submit it has to store the data in the database. The prob is that it does not store the radio button data and the list data(eg. male/female and birthdate(month/date/year)). I'll just paste the prob code:

a.jsp

<TR>
<TD align="right" width="150">
<B><FONT color="#99CC00">Gender: </FONT></B></TD>
<TD align="left">
<INPUT type=radio CHECKED value=maleRadioButton name=gender>
<LABEL for=maleRadioButton>Male</LABEL> <INPUT type=radio value=femaleRadioButton name=gender> <LABEL for=femaleRadioButton> Female </LABEL></TD>
<TD></TD></TR>
<TR>
<TD width="150" align="right">
<B><FONT color="#99CC00">Birth Date: </FONT></B></TD>
<TD align="left">
<SELECT name=month[1]>
<OPTION value="" selected>Month</OPTION>
<OPTION value=1>January </OPTION>
<OPTION value=2>February </OPTION>
<OPTION value=3>March </OPTION>
<OPTION value=4>April </OPTION>
<OPTION value=5>May </OPTION>
<OPTION value=6>June </OPTION>
<OPTION value=7>July </OPTION>
<OPTION value=8>August </OPTION>
<OPTION value=9>September </OPTION>
<OPTION value=10>October </OPTION>
<OPTION value=11>November </OPTION>
<OPTION value=12>December</OPTION> </SELECT> &nbsp;

<SELECT name=day[2]>
<OPTION value="" selected>Day</OPTION>
<OPTION value=1>1 </OPTION>
<OPTION value=2>2 </OPTION>
<OPTION value=3>3</OPTION>
<OPTION value=4>4</OPTION>
<OPTION value=5>5</OPTION>
<OPTION value=6>6</OPTION>
<OPTION value=7>7</OPTION>
<OPTION value=8>8</OPTION>
<OPTION value=9>9</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>&nbsp;

<SELECT name=year[0]>
<OPTION value="" selected>Year</OPTION>
<OPTION value=1987>1987</OPTION>
<OPTION value=1986>1986</OPTION>
<OPTION value=1985>1985</OPTION>
<OPTION value=1984>1984</OPTION>
<OPTION value=1983>1983</OPTION>
<OPTION value=1982>1982</OPTION>
<OPTION value=1981>1981</OPTION>
<OPTION value=1980>1980</OPTION>
<OPTION value=1979>1979</OPTION>
<OPTION value=1978>1978</OPTION>
<OPTION value=1977>1977</OPTION>
<OPTION value=1976>1976</OPTION>
<OPTION value=1975>1975</OPTION>
<OPTION value=1974>1974</OPTION>
<OPTION value=1973>1973</OPTION>
<OPTION value=1972>1972</OPTION>
<OPTION value=1971>1971</OPTION>
<OPTION value=1970>1970</OPTION>
<OPTION value=1969>1969</OPTION>
<OPTION value=1968>1968</OPTION>
<OPTION value=1967>1967</OPTION>
<OPTION value=1966>1966</OPTION>
<OPTION value=1965>1965</OPTION>
<OPTION value=1964>1964</OPTION>
<OPTION value=1963>1963</OPTION>
<OPTION value=1962>1962</OPTION>
<OPTION value=1961>1961</OPTION>
<OPTION value=1960>1960</OPTION>

And this is in another jsp page to insert the data:

String gender="";
String month="";
String day="";
String year="";

gender=request.getParameter("gender");
month=request.getParameter("month");
day=request.getParameter("day");
year=request.getParameter("year");

stat.setString(6, gender);
stat.setString(7, month +"/"+ day +"/"+year);

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

The type for birthdate in sql is given as datetime and this also posing as a prob!

The error I'm getting is :
Error encountered while entering data in the database:java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.

And also in the country text, if I click on to Saint Helena as a country it is just saving it as "Saint" in the database.

I dunno where I'm going wrong. Anybody got any solution???
Ximvu
06-21-2005, 07:31 PM
My knowledge of JSP and SQL is a little limited, but as far as the datetime method goes, I know SQL will be looking for 'YYYY-MM-DD HH:MM:SS', so you want to make sure that instead of using 1, 2, 3, etc. that you use 01, 02, 03, etc. Also on the font issue, do you have quotes around the entire font name? It sounds like it's truncating when it hits whitespace, try "Saint Helena" or 'Saint Helena' (not sure which quote mark to use) if you aren't already. I'm afraid I don't know enough to help on the data storage problem.
Zenzii
06-22-2005, 02:06 AM
My knowledge of JSP and SQL is a little limited, but as far as the datetime method goes, I know SQL will be looking for 'YYYY-MM-DD HH:MM:SS', so you want to make sure that instead of using 1, 2, 3, etc. that you use 01, 02, 03, etc. Also on the font issue, do you have quotes around the entire font name? It sounds like it's truncating when it hits whitespace, try "Saint Helena" or 'Saint Helena' (not sure which quote mark to use) if you aren't already. I'm afraid I don't know enough to help on the data storage problem.

Thanks for responding so quickly! :)

But I still dint get you. Where do I have to write 01,02,03 instead of 1,2,3 and here's how I have written the country code:

In a.jsp
<TR>
<TD width="150" align="right">
<B><FONT color="#99CC00">Country: </FONT></B></TD>
<TD align="left" >
<SELECT name=location_country>
<OPTION value="" selected>Country</OPTION>
<OPTION value=Afghanistan> Afghanistan</OPTION>
<OPTION value=Albania>Albania</OPTION>
<OPTION value=Algeria>Algeria</OPTION>

In the other jsp file:
String location_country="";

location_country=request.getParameter("location_country");

stat.setString(8, location_country);

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

Where do I have to put the quotes/double quotes??
-->here <OPTION value=Algeria> or here
--> Algeria</OPTION>
Ximvu
06-22-2005, 05:52 AM
I would suggest putting quotes and padding numbers around the value field: Thus...

the month stuff becomes:
<OPTION value="" selected>Month</OPTION>
<OPTION value=01>January </OPTION>
<OPTION value=02>February </OPTION>
<OPTION value=03>March </OPTION>
<OPTION value=04>April </OPTION>
<OPTION value=05>May </OPTION>
<OPTION value=06>June </OPTION>
<OPTION value=07>July </OPTION>
<OPTION value=08>August </OPTION>
<OPTION value=09>September </OPTION>
<OPTION value=10>October </OPTION>
<OPTION value=11>November </OPTION>
<OPTION value=12>December</OPTION> </SELECT> &nbsp;

The day stuff becomes:

<OPTION value="" selected>Day</OPTION>
<OPTION value=01>1 </OPTION>
<OPTION value=02>2 </OPTION>
<OPTION value=03>3</OPTION>
<OPTION value=04>4</OPTION>
<OPTION value=05>5</OPTION>
<OPTION value=06>6</OPTION>
<OPTION value=07>7</OPTION>
<OPTION value=08>8</OPTION>
<OPTION value=09>9</OPTION>

and the country stuff:
<OPTION value="" selected>Country</OPTION>
<OPTION value="Afghanistan"> Afghanistan</OPTION>
<OPTION value="Albania">Albania</OPTION>
<OPTION value="Algeria">Algeria</OPTION>

and if you still have trouble with the date stuff...try changing the setString line to
stat.setString(7, "'" + year + month + day + "'"); or maybe...
stat.setString(7, "\'" + year + month + day + "\'"); (I can never remember if backquotes are needed for single quotes)

Someone told me that 'YYYYMMDD' is a more universially accepted date format.
Zenzii
06-22-2005, 02:14 PM
I would suggest putting quotes and padding numbers around the value field: Thus...

the month stuff becomes:
<OPTION value="" selected>Month</OPTION>
<OPTION value=01>January </OPTION>
<OPTION value=02>February </OPTION>
<OPTION value=03>March </OPTION>
<OPTION value=04>April </OPTION>
<OPTION value=05>May </OPTION>
<OPTION value=06>June </OPTION>
<OPTION value=07>July </OPTION>
<OPTION value=08>August </OPTION>
<OPTION value=09>September </OPTION>
<OPTION value=10>October </OPTION>
<OPTION value=11>November </OPTION>
<OPTION value=12>December</OPTION> </SELECT> &nbsp;

The day stuff becomes:

<OPTION value="" selected>Day</OPTION>
<OPTION value=01>1 </OPTION>
<OPTION value=02>2 </OPTION>
<OPTION value=03>3</OPTION>
<OPTION value=04>4</OPTION>
<OPTION value=05>5</OPTION>
<OPTION value=06>6</OPTION>
<OPTION value=07>7</OPTION>
<OPTION value=08>8</OPTION>
<OPTION value=09>9</OPTION>


I tried all this, but still it is not retrieving the birthdate data! the output is given as(in the sql database):

null/null/null




and the country stuff:
<OPTION value="" selected>Country</OPTION>
<OPTION value="Afghanistan"> Afghanistan</OPTION>
<OPTION value="Albania">Albania</OPTION>
<OPTION value="Algeria">Algeria</OPTION>

Thanks for this. This part helped me, now its displaying the whole thing!! :)


and if you still have trouble with the date stuff...try changing the setString line to
stat.setString(7, "'" + year + month + day + "'"); or maybe...
stat.setString(7, "\'" + year + month + day + "\'"); (I can never remember if backquotes are needed for single quotes)

Someone told me that 'YYYYMMDD' is a more universially accepted date format.
Tried all that you advised, but still not getting the output!! :confused:
Zenzii
06-22-2005, 02:35 PM
I also tried using the datatype of Birthdate as datetime in sql, but then what datatype should should we use in jsp???

I tried string, char, int, float, etc...but all were clashing with the datetime!!
Ximvu
06-22-2005, 06:15 PM
Are you using JDBC and MS SQL Server? If not, what kind of server are you running. Is there anyway java.sql.Timestamp would help?
Zenzii
06-23-2005, 02:04 PM
Yes, I'm using JDBC and MS SQL Server. Im sorry but Im ignorant about the Timestamp thing. What is it and how to embed it into the jsp???
Ximvu
06-23-2005, 03:22 PM
Alright, you'll want to create a timestamp object and then enter it in as a value. It should look something like this:

gender=request.getParameter("gender");
month=request.getParameter("month");
day=request.getParameter("day");
year=request.getParameter("year");

stat.setString(6, gender);
GregorianCalendar cal = new GregorianCalendar(Integer.parseInt(year), Integer.parseInt(month), Integer.parseInt(day), 0, 0, 0);
java.sql.Timestamp ts = new java.sql.Timestamp(cal.getTimeInMillis());
stat.setTimestamp(7, ts);

The Calendar object is in the java.util package, so you either need to import that package or say...
java.util.GregorianCalendar cal = new java.util.GregorianCalendar(Integer.parseInt(year), Integer.parseInt(month), Integer.parseInt(day), 0, 0, 0);

[Edit] Just noticed that the month field in GregorianCalendar uses 0 for January, 1 for February, etc. So you can either do
GregorianCalendar cal = new GregorianCalendar(Integer.parseInt(year), Integer.parseInt(month) - 1, Integer.parseInt(day), 0, 0, 0);

or...

<OPTION value=00>January </OPTION>
<OPTION value=01>February </OPTION>
<OPTION value=02>March </OPTION>
<OPTION value=03>April </OPTION>

etc...
Zenzii
06-25-2005, 11:03 AM
Thanks a lot! I've got it!

Now, can you tell me, how to validate in a jsp?? Supposing i dont want the user to leave the username field blank or the country option, then how should I write the code in jsp??

I wrote a java code in jsp but it doesnt recognise the methods!
Ximvu
06-26-2005, 01:55 AM
Hmm...right after you get your variables, you should be able to check if they have value. For example, right after

gender=request.getParameter("gender");
month=request.getParameter("month");
day=request.getParameter("day");
year=request.getParameter("year");

You could say...

if(gender.equals(""))
{
What to do if there's no data
}
Zenzii
06-26-2005, 10:11 AM
That means before connecting to the database???