Inserting data into MS Sql

I have created a feedback system and wants to keep track of who have seen the report, what type of report and the time?

So if the user login successfully into the system, i have to get the userid and store in db. Then when the user clicks on a particular report, i have to get the type the report and the time and store them in the db.

So in the db i create a table named Log. Now i need to store the userid, date with time and the type of report in the db. How to do it in jsp?
Smiley31Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Mayank SConnect With a Mentor Associate Director - Product EngineeringCommented:
Try this:

Class.forName ( MM_test_DRIVER ) ;
Connection ConnRecordset6 = DriverManager.getConnection ( MM_test_STRING, MM_test_USERNAME, MM_test_PASSWORD ) ;
PreparedStatement StatementRecordset6 = ConnRecordset6.prepareStatement ( "INSERT INTO Log (StaffID) VALUES (?) " ) ;
StatementRecordset6.setString ( 1, userName ) ;
StatementRecordset6.executeUpdate () ;

0
 
kennethxuCommented:
do you have experience with jdbc programming?
0
 
Mayank SAssociate Director - Product EngineeringCommented:
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
xman19Commented:
http://oldlook.experts-exchange.com:8080/Web/Web_Languages/JSP/Q_20945954.html

Ken, have a look.. i am waiting for your comments... :-)
0
 
xman19Commented:
Smiley31,
writing to a dba dn retrieve from the db is basically using JDBC. Like mayankeagle mentioned, that is the best turotial i ever see. I think if still don't understand, have to get a book. My own personal recommandaion is thr Wrox, JSP for Beginner. That's will come will some same program and the concepts regarding all those program. Here i attach some of the simple data access for a look... offcourse, i do that in a jsp calling a javabean... have fun.

all.java
=========
package com.wrox.cars;

import java.sql.*;
import java.util.*;
import java.math.BigDecimal;
import java.io.*;
import java.text.*;
import java.lang.*;

public class all {
 
        String error;
        private Connection conn;
      private boolean valid = false;
      private ResultSet rs2;
                        
      public String ponumber="";
      
      public all() { }
      
      public boolean executeQuery(String div, String gp) throws SQLException, ClassNotFoundException {
             boolean success = false;
      
             if (conn==null) {  getConnection(); }
          try {
                  
              String queryString =("SELECT * FROM <yourTable> ");
              Statement stmt = conn.createStatement();
              rs2 = stmt.executeQuery(queryString);
            if (rs2==null) {success = false;}      
            else { success = true; }
         } catch (Throwable e) { success = false; }
       return success;
      }
         
        public boolean getValid(String div, String gp) throws SQLException, ClassNotFoundException {
              valid = executeQuery(div, gp);
            return valid;
      }

      public boolean hasNext() {
                  boolean hasElement = false;

            try {
                  if (rs2.next()) {
                  ponumber          = rs2.getString("TD2001");
                  hasElement             = true;
                  
            } else {
              release();      
              hasElement      =false;                  
                  }
            } catch(SQLException e) { e.printStackTrace(); }
            return hasElement;
        }
       
        public void getConnection() throws SQLException, ClassNotFoundException {
           try {
             
              Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
           conn = DriverManager.getConnection("jdbc:db2:*local");
           } catch (ClassNotFoundException e) { e.printStackTrace(); }
             catch (SQLException cnfe) { cnfe.printStackTrace(); }
        }
     
           public void release() throws SQLException {
              rs2 = null;
              if (conn!=null) { conn.close(); }
           }
           
       public String getPonumber() {
             return ponumber;
       }
}

********

My jsp page:

all.jsp
====
<%@ page language="java" import ="java.sql.*,
                          java.text.*,
                          java.math.*,  
                          java.io.*,
                          java.util.*,
                          java.lang.*,
                          com.wrox.cars.* "  %>

<%-- page contentType="text/html" errorPage="handle_error.jsp" --%>
<jsp:useBean id="all" class="com.wrox.cars.all" />            
<html>
<body >
<table>
 <tr>
   <td><img src="pic.jpg" alt="logo"> </td>
   <td><img src="can_msia.jpg" alt="banner"></td>
 </tr>
</table>
<table border=1 width="100%">
<tr>
      <td nowrap>No</td>
      <td nowrap>PO Number</td>
      <td nowrap>PO Date</td>
      
</tr>
<%
String div = request.getParameter("div");
String gp = request.getParameter("gp");

all.getValid(div, gp);
int counter =0;
while (all.hasNext()) {
counter++;

%>
<tr>
      <td nowrap><%= counter %></td>
      <td nowrap>&nbsp<a href="podetails.jsp?pod=<jsp:getProperty name="all" property="ponumber" />" /><jsp:getProperty name="all" property="ponumber" /></a></td>
      
</tr>
<%
}
all.release();
%>
</table>
</body>
</html>
      
Have fun...
0
 
Smiley31Author Commented:
I do not have any experience jdbc programming. So if possible can someone kind enough help me write out the code but not in javabean. Something simple and easy to understand.
0
 
Smiley31Author Commented:
If I just need to get the userid and the time login and logout time when signout, how should I do it?
0
 
Mayank SAssociate Director - Product EngineeringCommented:
I hope you have seen the tutorial that I posted? That should help you.

You also have plenty of code samples at: http://www.javaalmanac.com/cgi-bin/search/find.pl?words=sql
0
 
xman19Commented:
agree with mayankeagle, in www.javaalmanac.com there are plenty of sample code..

And if you want people to write for you.. hm.. quite tough since you can learn more.. unless you try.. :-)
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> you can learn more.. unless you try

perhaps meant: you CAN'T learn more, unless you try :-)

Have a nice time trying ;-) Good luck.
0
 
Smiley31Author Commented:
I now got a problem. If I use session to get the userid, why I still can't get the userid into the database. Did I code it wrongly?

My codes:
<%
session .setAttribute("userName", request.getParamter("staffid"));

Driver DriverRecordset6 = (Driver)Class.forName(MM_test_DRIVER).newInstance();
Connection ConnRecordset6 = DriverManager.getConnection(MM_test_STRING,MM_test_USERNAME,MM_test_PASSWORD);
PreparedStatement StatementRecordset6 = ConnRecordset5.prepareStatement("INSERT INTO Log WHERE StaffID = '"+ userName + "' );
ResultSet Recordset6 = StatementRecordset6.executeQuery();
boolean Recordset6_isEmpty = !Recordset6.next();  
boolean Recordset6_hasData = !Recordset6_isEmpty;
Object Recordset6data;
int Recordset6_numRows = 0;
%>
0
 
Mayank SAssociate Director - Product EngineeringCommented:
I don't see the VALUES clause in your query.

Also, the newInstance () call after Class.forName () is not needed, and DriverRecordset6 is not needed because you are not using it - you are using DriverManager.getConnection (). Also, you prepare the statement usng another connection ConnRecordset5, so why did you write the ConRecordset6?

You should use one connection, not so many.
0
 
Smiley31Author Commented:
ConnRrcordset5 is my mistake, should be ConnRecordset6.
That means for this line: Driver DriverRecordset6 = (Driver)Class.forName MM_test_DRIVER).newInstance(); I can ignore is it?
0
 
Smiley31Author Commented:
After a user has login into the system with the correct staffid and password which are already stored in the database, at this point how should i code it to be able to get the staffid and time at that point the user login?
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Ideally, you should store these details in the session. You might keep a UserContext object:

public class UserContext
{
  private String sUserName ;
  private String sPassword ;
  private Date dtLoginTime ;
  ..
  // getters/ setters
}

And you should make an instance of this object, populate and store it in the session using session.setAttribute ().
0
 
Smiley31Author Commented:
I still couldn't store the user name and time inot the database. Could someone help me see if my codes are wrong?

The codes:
<%
Class.forName ( MM_test_DRIVER ) ;
Connection ConnRecordset6 = DriverManager.getConnection ( MM_test_STRING, MM_test_USERNAME, MM_test_PASSWORD ) ;
PreparedStatement StatementRecordset6 = ConnRecordset6.prepareStatement ( "INSERT INTO Log (StaffID,[DATE]) VALUES (<%=request.getParameter("staffid")%>,<%=request.getParameter("date")%>)");
StatementRecordset6.setString ( 1, userName ) ;
StatementRecordset6.executeUpdate () ;
%>

0
 
Mayank SAssociate Director - Product EngineeringCommented:
Well, I told you to use "?" while preparing the PreparedStatement.

"INSERT INTO Log (StaffID) VALUES (?)"

Use: setString () after this to set the value.

If you have another parameter to be set, it should be like:

"INSERT INTO Log (StaffID, WHICHEVER_COLUMN) VALUES (?, ?)"

Use: setString () two times to set both the values.

StatementRecordset6.setString ( 1, request.getParameter("staffid") ) ;
StatementRecordset6.setString ( 2, request.getParameter("date") ) ; // setString () or setDate () or what-ever - depending upon the data-type
0
 
Smiley31Author Commented:
Thanx a lot! It works. Can i just ask you another qn? how to get the system time?
0
 
Mayank SAssociate Director - Product EngineeringCommented:
There are many ways:

long lMilliseconds = System.currentTimeMillis () ;
Date dtCurrent = new Date () ;
Calendar calCurrent = Calendar.getInstance () ;

- and many more. Which one do you want?
0
 
Smiley31Author Commented:
I need the time and date. Can the codes you state have both?
0
 
Smiley31Author Commented:
I need the time and date. Can the codes you state have both? I want the codes to be as simple as possible. So that i can just put the codes into the previous codes.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Calendar and Date will both hold date as well as time.
0
 
Smiley31Author Commented:
My date and time doesn't go into the db. Did i code wrongly?

My codes:
<%
java.util.Date today = new java.util.Date();
String strTime =today.getHours()+":"+today.getMinutes()+":"+today.getSeconds();
int year=today.getYear()+1900;
String strDate=today.getDate()+"-"+today.getMonth()+"-"+year;

Class.forName ( MM_test_DRIVER ) ;
Connection ConnRecordset6 = DriverManager.getConnection ( MM_test_STRING, MM_test_USERNAME, MM_test_PASSWORD ) ;
PreparedStatement StatementRecordset6 = ConnRecordset6.prepareStatement ( "INSERT INTO Log (StaffID,TIMEIN,DATEIN) VALUES (?,"+strTime+","+strDate+") " );
StatementRecordset6.setString ( 1, request.getParameter("staffid") ) ;
StatementRecordset6.setString ( 2, request.getParameter("strTime") ) ;
StatementRecordset6.setString ( 3, request.getParameter("strDate") ) ;
StatementRecordset6.executeUpdate () ;
%>
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Well, again I don't see the question-marks ?

PreparedStatement StatementRecordset6 = ConnRecordset6.prepareStatement ( "INSERT INTO Log (StaffID,TIMEIN,DATEIN) VALUES (?, ?, ?) " ) ;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.