asked on

problem with my code?

index.jsp
=============================================
<%
session.putValue("USER_INT_KEY", "INVALID");
session.putValue("option_chosen", "gen_rev");
%>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>

<FRAMESET ROWS="13%, *" >
<FRAME SRC="top.html" name="top" noresize scrolling="no" target="main">
<FRAMESET COLS="16%, *">
<FRAME SRC="main_menu.html" name="left" noresize scrolling="auto" target="main">
<FRAME SRC="main_information.html" name="main">
</FRAMESET>
<NOFRAMES><BODY></BODY></NOFRAMES>
</FRAMESET>
</HTML>

main_menu.html
=================================================
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<A HREF="login.jsp" target="main">Login</A><BR>
<A HREF="gen_rev.html" target="main">Review Items</A><BR>
</BODY>
</HTML>

login.jsp
================================================
<%@ page errorPage="error.jsp" import="java.util.*,java.sql.*"%>
<%
String strOptionChosen = null;
String strLoginID = "";
String strPassword = "";
String strMessage = "";
String strUSER_INT_KEY = null;

strOptionChosen = session.getValue("option_chosen").toString();

if (session.getValue("USER_INT_KEY") == null)
{
      session.putValue("USER_INT_KEY", "INVALID");
}

strUSER_INT_KEY = session.getValue("USER_INT_KEY").toString();

if (!(session.getValue("USER_INT_KEY").toString().equals("INVALID")))
{
      if (session.getValue("option_chosen") != null)
            response.sendRedirect(strOptionChosen + ".jsp");
      else
            response.sendRedirect("gen_rev.jsp");
}

if (session.getValue("USER_INT_KEY").toString().equals("INVALID"))
{
      if (request.getParameter("LOGIN_ID") != null)
      {
            strLoginID = request.getParameter("LOGIN_ID");
            strPassword = request.getParameter("PASSWORD");
            String strSQLQuery = null;

            Connection conOracle = null;

            try
            {
                  Class.forName("sun.jdbc.odbc.JbdcOdbcDriver");
                  conOracle = DriverManager.getConnection("jdbc:odbc:store", "Scott", "Tiger");

                  strSQLQuery = "Select USER_INT_KEY, LOGON_ID, PASSWORD ";
                  strSQLQuery += "from T_ACT_USER ";
                  strSQLQuery += "where lower(LOGON_ID) = lower('" + strLoginID.trim() + "')";
                  strSQLQuery += "and lower(PASSWORD) = lower('" + strPassword.trim() + "')";

                  Statement stmtSQL = null;
                  ResultSet rsSource = null;

                  stmtSQL = conOracle.createStatement();
                  rsSource = stmtSQL.executeQuery(strSQLQuery);

                  while (rsSource.next())
                  {
                        strUSER_INT_KEY = rsSource.getString("USER_INT_KEY");
                  }

                  if (strUSER_INT_KEY != null)
                  {
                        session.putValue("USER_INT_KEY", strUSER_INT_KEY);

                        String Is_SQLCustVisitCount = null;
                        Is_SQLCustVisitCount = "update T_ACT_USER ";
                        Is_SQLCustVisitCount += "set visit_count = visit_count + 1 ";
                        Is_SQLCustVisitCount += "where USER_INT_KEY = " + strUSER_INT_KEY;

                        PreparedStatement pstmtUpdate = null;
                        pstmtUpdate = conOracle.prepareStatement(Is_SQLCustVisitCount);
                        pstmtUpdate.executeUpdate();

                        if (session.getValue("option_chosen") != null)
                              response.sendRedirect(strOptionChosen + ".jsp");
                        else
                              response.sendRedirect("gen_rev.jsp");
                  }
                  else
                  {
                        strMessage = "User ID and Password not found";
                  }
            }
            catch(ClassNotFoundException e)
            {
                  strMessage += "ClassNotFoundException when connecting to Database";
            }
            catch(SQLException e)
            {
                  strMessage += "SQLException when connecting to Database" + e.getMessage();
            }
      }
}
%>

<HTML>
<HEAD>
<TITLE>Login ID TEST</TITLE>
</HEAD>
<BODY>

<%
      if (strMessage != "")
      {
            out.println("<P><FONT FACE=ARIAL SIZE=3 COLOR=RED>Error: " + strMessage);
      }
%>

<FORM ACTION="login.jsp" TARGET="main" METHOD="POST" >
<TABLE BORDER="0">
<TR><TD>Login ID: </TD>
<TD><INPUT TYPE="TEXT" NAME="LOGON_ID" SIZE="18" VALUE=<%=strLoginID%>></TD></TR>
<TR><TD>Password: </TD>
<TD><INPUT TYPE="PASSWORD" NAME="PASSWORD" SIZE="18" VALUE=<%=strPassword%>></TD></TR>
<TR><TD><INPUT TYPE="SUBMIT" VALUE="Submit"></TD>
<TD><INPUT TYPE="RESET" VALUE="Reset"></TD></TR>
</TABLE>
</FORM>

</BODY>
</HTML>

I established an ODBC Connection - Microsoft ODBC for Oracle. I created the table in SQL *PLUS and added the data to this table. I opened the index.jsp file using http://localhost:8100/index.jsp in the IE browser and clicked on login.jsp on the left navigation. When I entered the login id and password and clicked on submit button. It's nothing. Something wrong with my code?? Please advice.

vasan_sr

Hi,
Check whether u establish connection with the database.
Print the connection and see in ur browser.
If connection is null then problem is with backend or with the connection URL.(connection bean)

(please dont send code tell the problem we will analyse and try to give soln to U)

Good Luck
Vasan S

ASKER CERTIFIED SOLUTION

kotan

membership

This solution is only available to members.

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

Start Free Trial

vb6vb6

ASKER

I changed LOGIN_ID to LOGON_ID. I got an error : Error:ClassNotFoundException when connecting to Database. I think this means that it does not connect to Database.
I established an ODBC connection Microsoft ODBC for Oracle and created the T_ACT_USER table in SQL *PLUS and inserted the values to this table.

I tested the test code:

<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.sql.*"%>
<%
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url="jdbc:odbc:store";
Connection con=DriverManager.getConnection(url, "Scott", "Tiger");
Statement stmt = con.createStatement();
String query="CREATE TABLE COFFEES" +
"(COF_NAME VARCHAR(32),"+
"SUP_ID NUMBER,"+
"PRICE NUMBER,"+
"SALES NUMBER,"+
"TOTAL NUMBER)";
stmt.executeUpdate(query);
}
catch (Exception e) {}
out.println("table coffees created");
%>

I got no error and checked the Coffees table in SQL *PLUS. But I don't understand why I am getting an error for my login page. Please advice.

vb6vb6

ASKER

Never Mind. Sorry.

kotan

>I got an error : Error:ClassNotFoundException when connecting to Database.

I think the package sun.jdbc.odbc.JbdcOdbcDrive doesn't in your classpath. Please check your classpath.

vb6vb6

ASKER

I solved the ClassNotFoundException but I got another error!
java.sql.SQLException: [Microsoft][ODBC driver for Oracle][Oracle]ORA-00942: table or view does not exist
What does that mean?

vb6vb6

ASKER

Never mind! Again!

vb6vb6

ASKER

I solved it by fixing the ORA-00942 error.

kotan

Seem like it is sql syntax problem.

Check this,
strSQLQuery = "Select USER_INT_KEY, LOGON_ID, PASSWORD ";
strSQLQuery += "from T_ACT_USER ";
strSQLQuery += "where lower(LOGON_ID) = lower('" + strLoginID.trim() + "')";
strSQLQuery += "and lower(PASSWORD) = lower('" + strPassword.trim() + "')";

For the third statement, it should be ending with a space.