Solved

Inserting data into MS Sql

Posted on 2004-04-06
24
675 Views
Last Modified: 2008-03-17
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?
0
Comment
Question by:Smiley31
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 3
  • +1
24 Comments
 
LVL 14

Expert Comment

by:kennethxu
ID: 10771547
do you have experience with jdbc programming?
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10772177
0
 

Expert Comment

by:xman19
ID: 10773025
http://oldlook.experts-exchange.com:8080/Web/Web_Languages/JSP/Q_20945954.html

Ken, have a look.. i am waiting for your comments... :-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:xman19
ID: 10773051
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
 

Author Comment

by:Smiley31
ID: 10780356
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
 

Author Comment

by:Smiley31
ID: 10780543
If I just need to get the userid and the time login and logout time when signout, how should I do it?
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10780724
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
 

Expert Comment

by:xman19
ID: 10781969
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
 
LVL 30

Expert Comment

by:Mayank S
ID: 10782035
>> 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
 

Author Comment

by:Smiley31
ID: 10847515
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
 
LVL 30

Expert Comment

by:Mayank S
ID: 10853432
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
 

Author Comment

by:Smiley31
ID: 10855987
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
 
LVL 30

Accepted Solution

by:
Mayank S earned 100 total points
ID: 10856419
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
 

Author Comment

by:Smiley31
ID: 10874473
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
 
LVL 30

Expert Comment

by:Mayank S
ID: 10875112
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
 

Author Comment

by:Smiley31
ID: 10884728
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
 
LVL 30

Expert Comment

by:Mayank S
ID: 10885397
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
 

Author Comment

by:Smiley31
ID: 10885604
Thanx a lot! It works. Can i just ask you another qn? how to get the system time?
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 10885970
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
 

Author Comment

by:Smiley31
ID: 10886597
I need the time and date. Can the codes you state have both?
0
 

Author Comment

by:Smiley31
ID: 10886621
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
 
LVL 30

Expert Comment

by:Mayank S
ID: 10887428
Calendar and Date will both hold date as well as time.
0
 

Author Comment

by:Smiley31
ID: 10895459
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
 
LVL 30

Expert Comment

by:Mayank S
ID: 10896284
Well, again I don't see the question-marks ?

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
servlet cookie finding by name 1 96
build fail in maven project 11 212
Handle form fields in spring mvc controller 1 72
how to add new optional parameter to JSP 1 61
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Invest in your employees with these five simple steps to improve employee engagement and retention.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question