GeeCON Prague 2014*
The moose likes JDBC and the fly likes Sequencial ID by Date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sequencial ID by Date" Watch "Sequencial ID by Date" New topic
Author

Sequencial ID by Date

abu kamruzzaman
Greenhorn

Joined: Aug 03, 2004
Posts: 12
I want to assign a job number for a user request form. Our dept. wants the job number to be reset everyday. Like say for example, if there are some job requests today it will get the number assigned sequentially 1, 2, 3, 4 �and it will follow as many requests are there for today. But tomorrow the job requests have to start with number 1, 2, 3, 4 �. So, everyday the job number will reset to one. I am having problem.
I am trying to compare the today�s date with the date of the last record entered in the database, but getting errors.
Any Help!
Thanks
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61315
    
  66

You'll need to provide a great deal more detail regarding the nature of the errors before anyone can begin to help you.

And as this really has nothing to do with JSP, I'm moving this to the JDBC forum.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
abu kamruzzaman
Greenhorn

Joined: Aug 03, 2004
Posts: 12
Thanks. I am showing the code.

<%@ page contentType="text/html; charset=utf-8" import="java.sql.*, javax.mail.*, javax.mail.internet.*, java.util.*" errorPage="error.jsp"%>


<%
// define variables and assign values to variables
String Client_Time = request.getParameter("Client_Time"); //Value: new java.util.Date() Getting the system date and time from the client
String request_Date = request.getParameter("request_Date"); //User typed date in a textbox
int Day_id = 0; //initialized the day it

//load the MS Access JDBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//open a connection to the "Programming_Job_Request" database
String url="jdbc dbc rogramming_Job_Request";

Connection testConn= DriverManager.getConnection(url,"test1","test1");

%>


<%
String jsql2="Select * from Job_History ORDER by ID DESC";
Statement stmt2 = testConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs1 = stmt2.executeQuery(jsql2);

//check the date and increment the day id if already a id exist by that date
while(rs1.first()){
String pre_request_Date= rs1.getString("request_Date");
if (request_Date != pre_request_Date){
Day_id = 1;
}
else{
Day_id = Day_id +1;

//Print to Browser
//out.println(jsql2);
//Print to System
System.out.println(jsql2);
}
//System.out.println(Day_id);

}
rs1.close();

%>



<%
//place query results in a ResultSet object and insert to teh DB

String jsql="INSERT INTO Job_History(Day_id, request_Date, Client_Time) VALUES (?, ?, ?)" ;
//Print to Browser
//out.println(jsql);
//Print to System
//System.out.println(jsql);

%>>>><%=Client_Time%>>>>><%

//create a statement object for sending SQL queries
PreparedStatement stmt = testConn.prepareStatement(jsql);
stmt.setInt(1, Day_id);
stmt.setString(2, request_Date);
stmt.setString(3, Client_Time);

// stmt.setInt(7, Integer.parseInt(uid));
stmt.executeUpdate();

//clean up current resultse object
if (stmt!=null)stmt.close();
%>


<%
String jsql1="Select * from Job_History where Client_Time = '"+Client_Time+"'";
//"SELECT * FROM user WHERE user_name = '"+Client_Time+"'";
Statement stmt1 = testConn.createStatement();
ResultSet rs = stmt1.executeQuery(jsql1);
while(rs.next()){
String user_id= rs.getString("ID"); //An AutoNumbered ID from the Database and the Client time will print the current ID

%>

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Information Technology Resources</title>
</head>
<body>
<table border="0" width="450" id="table10">
<form method="POST" action="Implementation_estimated.jsp">

<tr>
<td><h3>Information Technology Resources<br>Programming Request Form</h3><br>

<table border="0" width="480" id="table11" height="0">
<tr>
<td colspan="4" width="0">
<b><font size="4">Control Number:<%=user_id%></font></b> (Please remember this Number)</td>

</tr>
<tr>
<td colspan="4">
Day ID: <%=Day_id%></td>
</tr>
<tr>
<td colspan="4">
<font face="Arial" size="2">Request Date:</font><%=request_Date%> 
</td>
</tr>
<%
}
rs.close();
%>
<%
if (testConn!=null)testConn.close();
%>
</table>
</body>
</html>
abu kamruzzaman
Greenhorn

Joined: Aug 03, 2004
Posts: 12
i think i resolved it. Thanks to all.
 
GeeCON Prague 2014
 
subject: Sequencial ID by Date