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
Solved

Inserting data into MS Sql

Posted on 2004-04-06
24
672 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
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

IT certifications are a concrete representation of continual learning on the part of the candidate.  Continual learning is necessary for the long term success of an IT professional, but are IT certifications the right path for you?
Starting your own business is always a daunting process, and for most people it is brand new experience. Avoid the common pitfalls by following these tips to start on the road to success.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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