insert data into database

[jilshi]

i am using jsp to create web site. How can i insert data into database but do not have text box in the web site. i want to have data automatically show in the database without having people key in data from web site. the purpose for doing this is i want to generate default answer for every member.

[tk_Sajesh]

what data u r expecting to store in the Database?

[jilshi]

i want to generate answer for the user. every user will have different answer. their answer will be what is their lot no. i already generate question for them which is "what is my lot no?"
i attach part of my code. Every data in the code are fine except "answer" part because data that insert into database was wrong. data in database under "answer" field all has same data.


<% if (session.getAttribute("user_name") != null) {

String uName = (String)session.getValue("user_name");
String sql_1 = "SELECT es_iden, es_access_no "+
"FROM esaccs "+
"WHERE es_usr_id='"+uName+"' ";
Statement stmt_1 = conn.createStatement();
ResultSet rset_1 = stmt_1.executeQuery(sql_1);
while (rset_1.next()) {
ident = rset_1.getString("es_iden");
accNo = rset_1.getString("es_access_no");
//answer = rset_1.getString("es_ans");
}

String sql_2 = "SELECT fs_email_addr "+
"FROM fcrsdt "+
"WHERE fs_access_no = '"+accNo+"' ";
Statement stmt_2 = conn.createStatement();
ResultSet rset_2 = stmt_2.executeQuery(sql_2);
while (rset_2.next()) {
emailAddr = rset_2.getString("fs_email_addr");
out.print(emailAddr);
//lotno = rset_2.getString("fs_lot_no");
//out.print(lotno);
}
String select_e = "SELECT fs_lot_no "+
"FROM fcrsdt "+
"WHERE fs_lot_no = '"+answer+"' ";
Statement stmt_e = conn.createStatement();
ResultSet rset_e = stmt_e.executeQuery(select_e);
if (rset_e.next()) {
answer = rset_e.getString("fs_lot_no");
out.print(answer);
session.setAttribute("answer", answer);
}
}

try {
int totalRecordInDatabase = 0;
int intAccsNo = 1;
String sql = "SELECT count(*) as recordCount "+
"FROM fcrsdt "+
"WHERE fs_cat_typ ='O' ";
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(sql);
if (rset.next()) {
totalRecordInDatabase = rset.getInt("recordCount");
}
int iZero, iNine, iA, iZ, randNum;
StringBuffer bufUserID;
StringBuffer bufPassword;
//StringBuffer bufQuestion;
StringBuffer bufAnswer;

String strAccessNo="",accessNo,userID,password, question;

iZero = '0';
iNine = '9';
iA = 'A';
iZ = 'Z';

Random randGen = new Random(System.currentTimeMillis());
bufUserID = new StringBuffer();
bufPassword = new StringBuffer();
//bufQuestion = new StringBuffer();
//bufAnswer = new StringBuffer();

while (intAccsNo <= totalRecordInDatabase) {
strAccessNo = String.valueOf(intAccsNo);
accessNo = strAccessNo+".00";

for (int i = 0; i< 6; i++) {
randNum = randGen.nextInt(iZ);
while (!(randNum >= iZero && randNum <= iNine) &&!(randNum >= iA && randNum <= iZ)) {
randNum = randGen.nextInt(iZ);
}
char c = (char)randNum ;
bufUserID.append(c);
}//for
for (int ii= 0; ii< 6; ii++) {
randNum = randGen.nextInt(iZ);
while (!(randNum >= iZero && randNum <= iNine) &&!(randNum >= iA && randNum <= iZ)){
randNum = randGen.nextInt(iZ);
}
char c = (char)randNum ;
bufPassword.append(c);
}//for

userID = bufUserID.toString();
password = bufPassword.toString();
question = "What is my lot no?";
answer = answer;
out.print(answer);

bufUserID.delete(0,7);
bufPassword.delete(0,7);
//bufQuestion.delete(0,7);
//bufAnswer.delete(0,7);
String sql_insert ="INSERT INTO esaccs(es_access_no,es_usr_id,es_password,es_sts,es_iden,es_question,es_ans) "+
"VALUES(?,?,?,?,?,?,?)";
PreparedStatement ps_1 = conn.prepareStatement(sql_insert);
ps_1.setString(1,accessNo);
ps_1.setString(2,userID);
ps_1.setString(3,password);
ps_1.setString(4,"A");
ps_1.setString(5,"O");
ps_1.setString(6,question);
ps_1.setString(7,answer);
ps_1.executeUpdate();
ps_1.close();

[tk_Sajesh]

Can u just explain the workflow!!
Im bit confused..
Is it something like this, a user if he forgets the password, click on the Forgot password link, then will be prompted 2 enter the answer for his password question.
Once he enters the right answer, he will be supplied with the password..

[jilshi]

actually this page is creted not for user to user. i create this page just for administrator to use. After administrator execute this page, the function will provide default id name, password, question and answer for user. now all data are display correctly in database except "answer". data in database under "answer" field has wrong data.

[tk_Sajesh]

Picture In my mind
+-+-+-+-+-+-+-+-+
Administrator will create the user.
UserId, Password, Password Question & Answer will be automatically generated & stored in the database.

Only the data for the "answer" is wrongly inserted in the database,then the problem surely will be in the insert statement which u have executed.

Inorder 2 c whether the query generated is correct, just output the query & c whether it is correct or not.

[jilshi]

yes, i know what you mean. do u have any idea to correct my mistake.
i already try out.print() but still can't find the answer

[tk_Sajesh]

if (rset_e.next()) {
answer = rset_e.getString("fs_lot_no");
out.print(answer);
session.setAttribute("answer", answer);
}

Is The answer getting displayed in the webpage correct?


answer = answer;
out.print(answer);


Y Is the answer variable getting reassigned?
is the answer getting printed correct?

is that the data in the table is getting swapped or u r getting a different data?

[jilshi]

VERY WEIRD. the answer don't display anything on the screen after out.print()
However there is data insert into database. all the value under "answer" in database has the same value. what i need is different value according to the relevevant data.

[tk_Sajesh]

can u send the entire code & the database script coz I need 2 execute & c the result..

[jilshi]

Below is the full coding. thanks for your advice
<%@page import = "java.util.Random,java.lang.*,java.sql.*,java.util.*,java.awt.*,java.io.*,
java.sql.Connection,java.sql.DriverManager,java.sql.SQLException,java.lang.String.*,
java.text.SimpleDateFormat" %>
<%//@page errorPage = "handle_errors.jsp"%>

<% response.setHeader("Cache-Control","no-store"); //HTTP 1.1
response.setHeader("Pragma","no-cache"); //HTTP 1.0
response.setDateHeader ("Expires", 0); //prevents caching at the proxy server %>

<% // VALIDATE USER IS FROM AUTHORIZED/CORRECT PAGE
if(session.getAttribute("user_name")==null)
response.sendRedirect(response.encodeRedirectUrl("relogin.jsp"));
else { %>

<% String serv = (String)session.getAttribute("server");
String db = (String)session.getAttribute("database");

Class.forName("org.gjt.mm.mysql.Driver");
DriverManager.registerDriver((Driver) Class.forName("org.gjt.mm.mysql.Driver").newInstance());
String url = ("jdbc:mysql://"+serv+"/"+db+"" );
Connection conn = (Connection)DriverManager.getConnection(url,"",""); %>

<%! String ident, accNo, emailAddr, lotno, answer; %>

<html>
<head>
<title>Ramdom Generate Owner's Login Name and Password</title>
<link rel="stylesheet" href="facilities.css">
<script language="Javascript">

var this_weekday_name_array = new Array
(
"Sunday",
"Monday",
"Tuesday",
"Wednesday",
"Thursday",
"Friday",
"Saturday"
)

var this_month_name_array = new Array
(
"January",
"February",
"March",
"April",
"May",
"June",
"July",
"August",
"September",
"October",
"November",
"December"
)

var this_date_timestamp=new Date()

var this_weekday = this_date_timestamp.getDay()
var this_date = this_date_timestamp.getDate()
var this_month = this_date_timestamp.getMonth()
var this_year = this_date_timestamp.getYear()

if (this_year < 1000)
this_year+= 1900;
if (this_year==101)
this_year=2001;

var this_week_string = this_weekday_name_array[this_weekday]
var this_date_string = this_date + " " + this_month_name_array[this_month] + " " + this_year

</script>


<script language="JavaScript">
javascript:window.history.forward(1);
</script>
</head>

<BODY oncontextmenu="return false" leftMargin=0 text=#0080c0 topMargin=0>

<table border="0" width="100%" height="100%">
<tr><td valign="top" topmargi="0">
<% if (session.getAttribute("user_name") != null) {

String uName = (String)session.getValue("user_name");
String sql_1 = "SELECT es_iden, es_access_no "+
"FROM esaccs "+
"WHERE es_usr_id='"+uName+"' ";
Statement stmt_1 = conn.createStatement();
ResultSet rset_1 = stmt_1.executeQuery(sql_1);
while (rset_1.next()) {
ident = rset_1.getString("es_iden");
accNo = rset_1.getString("es_access_no");
//answer = rset_1.getString("es_ans");
}

String sql_2 = "SELECT fs_email_addr, fs_lot_no "+
"FROM fcrsdt "+
"WHERE fs_access_no = '"+accNo+"' ";
Statement stmt_2 = conn.createStatement();
ResultSet rset_2 = stmt_2.executeQuery(sql_2);
while (rset_2.next()) {
emailAddr = rset_2.getString("fs_email_addr");
//out.print(emailAddr);
answer = rset_2.getString("fs_lot_no");
out.print(answer);
}
}
//String select_e = "SELECT fs_lot_no "+
// "FROM fcrsdt "+
//"WHERE fs_access_no = '"+accNo+"' ";
//Statement stmt_e = conn.createStatement();
//ResultSet rset_e = stmt_e.executeQuery(select_e);
//if (rset_e.next()) {
//answer = rset_e.getString("fs_lot_no");
//out.print(answer);
//session.setAttribute("answer", answer);
//}
try {


int totalRecordInDatabase = 0;
int intAccsNo = 1;
String sql = "SELECT count(*) as recordCount "+
"FROM fcrsdt "+
"WHERE fs_cat_typ ='O' ";
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(sql);
if (rset.next()) {
totalRecordInDatabase = rset.getInt("recordCount");
}
int iZero, iNine, iA, iZ, randNum;
StringBuffer bufUserID;
StringBuffer bufPassword;
//StringBuffer bufQuestion;
StringBuffer bufAnswer;

String strAccessNo="",accessNo,userID,password, question;

iZero = '0';
iNine = '9';
iA = 'A';
iZ = 'Z';

Random randGen = new Random(System.currentTimeMillis());
bufUserID = new StringBuffer();
bufPassword = new StringBuffer();
//bufQuestion = new StringBuffer();
//bufAnswer = new StringBuffer();

while (intAccsNo <= totalRecordInDatabase) {
strAccessNo = String.valueOf(intAccsNo);
accessNo = strAccessNo+".00";

for (int i = 0; i< 6; i++) {
randNum = randGen.nextInt(iZ);
while (!(randNum >= iZero && randNum <= iNine) &&!(randNum >= iA && randNum <= iZ)) {
randNum = randGen.nextInt(iZ);
}
char c = (char)randNum ;
bufUserID.append(c);
}//for
for (int ii= 0; ii< 6; ii++) {
randNum = randGen.nextInt(iZ);
while (!(randNum >= iZero && randNum <= iNine) &&!(randNum >= iA && randNum <= iZ)){
randNum = randGen.nextInt(iZ);
}
char c = (char)randNum ;
bufPassword.append(c);
}//for

userID = bufUserID.toString();
password = bufPassword.toString();
question = "What is my lot no?";
answer = answer;
//out.print(answer);

bufUserID.delete(0,7);
bufPassword.delete(0,7);
//bufQuestion.delete(0,7);
//bufAnswer.delete(0,7);
String sql_insert ="INSERT INTO esaccs(es_access_no,es_usr_id,es_password,es_sts,es_iden,es_question,es_ans) "+
"VALUES(?,?,?,?,?,?,?)";
PreparedStatement ps_1 = conn.prepareStatement(sql_insert);
ps_1.setString(1,accessNo);
ps_1.setString(2,userID);
ps_1.setString(3,password);
ps_1.setString(4,"A");
ps_1.setString(5,"O");
ps_1.setString(6,question);
ps_1.setString(7,answer);
ps_1.executeUpdate();
ps_1.close();

//out.println("<br>Access no="+accessNo+"~~~Random User ID="+userID+"~~~Random Password="+password+"~~~Total Record In Database="+totalRecordInDatabase+"\n");
//out.println("<br>Random User ID = "+userID);
//out.println("<br>Random Password = "+password);
//out.println("<br>Total Record In Database = "+totalRecordInDatabase+"\n");

intAccsNo++;
} //while(1<=totalRecordInDatabase)

} catch (Exception ex) {
ex.printStackTrace();
} %>

<h1>&nbsp;</h1>
<h1>&nbsp;</h1>
<META HTTP-EQUIV="refresh" CONTENT="2; URL=maintenance_main.jsp">
<h1 align="center"><font color="#000099">Default Login Name and Password have been generated for owners.</font></h1>

</td></tr>

</table>

</body>
</html>
<% } // if(session.getAttribute("user_name")==null) %>