Solved

Inserting data into MS Sql

Posted on 2004-04-06
24
663 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
Comment Utility
do you have experience with jdbc programming?
0
 
LVL 30

Expert Comment

by:mayankeagle
Comment Utility
0
 

Expert Comment

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

Ken, have a look.. i am waiting for your comments... :-)
0
 

Expert Comment

by:xman19
Comment Utility
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
Comment Utility
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
Comment Utility
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:mayankeagle
Comment Utility
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
Comment Utility
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:mayankeagle
Comment Utility
>> 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
Comment Utility
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:mayankeagle
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 30

Accepted Solution

by:
mayankeagle earned 100 total points
Comment Utility
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
Comment Utility
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:mayankeagle
Comment Utility
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
Comment Utility
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:mayankeagle
Comment Utility
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
Comment Utility
Thanx a lot! It works. Can i just ask you another qn? how to get the system time?
0
 
LVL 30

Expert Comment

by:mayankeagle
Comment Utility
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
Comment Utility
I need the time and date. Can the codes you state have both?
0
 

Author Comment

by:Smiley31
Comment Utility
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:mayankeagle
Comment Utility
Calendar and Date will both hold date as well as time.
0
 

Author Comment

by:Smiley31
Comment Utility
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:mayankeagle
Comment Utility
Well, again I don't see the question-marks ?

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
decryption not working 5 80
difference between member and local variables. 29 121
grep code 4 153
maven webservice example issue 2 88
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now