how to generate autogenerate serial no.

[yogita]

i m developing helpdesk web application.Basically, this system required a employee to first log the Incident so for each incident i have to generate a specific ticketnumber as a reference number so the emp can refer this ticketnumber to see the status of the incident. so plz can anyone help me tht how to provide this autogenerate ticketno. and how it can store in database. ?
In table i have taken one field ticketno as primary key.

[alex.w747]

something wrong with DateTime + random value or guid?

[yogita]

i dont knw how to code for autogenerate ticketno.

[aspapp]

Here is a simple method that will gen a date-based number:

public string GenerateNo()
{
string Num= "";
Num = DateTime.Now.ToString("yyyyMMddhhmmssff");
return Num;
}

[kunalraj]

Hello,

You can do this by creating sequence on that column in database.
It will auto generate number sequentially like 1 than 2 than 3 etc.. at the n number of times.

1.) First you have to create sequence in database.
create sequence sequncename increment by 1 start with 1;


2.) In your code,when inserting records in that table you have to write it

insert into tablename (ticketno) values (sequncename.nextval);


so through db you can generate autono and insert into database.

But if you want to generate number like TIC0001 than it will not possible by the above sequence solution. For that you have to code.

So what you want auto generate number like 1to n OR TIC001 to n?

If you have any query than let me know.


thanks,
kunalraj

[yogita]

i have to do through hardcode like tic001 in tht way.... 1,2 ,3....n tht i knw how to do through query but abt this hardcode tht i dnt knw so can u plz tell me abt it.

[kunalraj]

hi,

first of all you have to write method for it like

public static String generateAutoCode(String code) throws Exception {
String genCode=null;
try{
String leadingCode=code.substring(0,3);
String trailCode=code.substring(3,code.length());
int inc=Integer.parseInt(trailCode)+1;
String fin=String.valueOf(inc);
if(fin.length()==1)
genCode=leadingCode+"000"+fin;
else if(fin.length()==2)
genCode=leadingCode+"00"+fin;
else if(fin.length()==3)
genCode=leadingCode+"0"+fin;
else
genCode=leadingCode+fin;
}
catch(Exception e){
throw e;
}
return genCode;
}

[r937]

yogita, which database system are you using? access? oracle? mysql? informix? db2? sybase?

[kunalraj]

after that you have to write code for check
when first time it will null or not.
If it null than start with tic0001 and from next it will auto generate.
and if not null than start with next.
For that you have to write like

String tmpID = null;

try {
connection code
then write query for check next val or new val

rsk=stmt.executeQuery("select ticno from tablename");--> rsk is object of datareader
if(rsk!=null && rsk.next())
{
rsk.last();
tmpID = rsk.getString("ticno");
}
else
{
tmpID="";
}

cn.close();
stmt.close();
rsk.close();
}

[kunalraj]

after that finally call that two method like

if(txtticno.Text)!=null)
{
userdl su= new userdl();--> this userdl is package name where your method arises which is last i have send (connection and query)
String ucode = su.getlastId();--> getlastid is method
if(ucode.equals(""))
{
autocode="tic0001";--> here you can define your number tic or whatever
}
else
{
autocode=CommonClass.generateAutoCode(ucode);--> CommonClass is package in which the generateAutoCode method arises. which i have send to first.(genCode=leadingCode+"000"+fin

}
}


OK If you can`t understand let me know

thanks,
kunalraj

[yogita]

i m using oracle database

[kunalraj]

hello,

I will understand you the whole flow.

1. you have to make method for generate autocode which i sent you first.
in that you can modify number like 0001 or 001 or 00001 whatever you want.
by modifying the code you can do it read it twice and under stand logic.

2. after that you have to make method for check the ticno is it blank or generated number? means first time the ticno column in db has no value that time it will generate tic0001 and from next time it will generate autocode.

Remember tic or whatever character you have to write it will write in last method.

3. The last method i have sent you is calling the above two method.
In that you have to character what you want like TIC or ticket etc.

if second method returning null value than it will start from TIC0001
and second method returning TIC0002 than this method generate TIC0003.


I know It is tuff to understand and me also to understand you.
But I have try my best to understand you.

If you have any query than please let me know.

thanks,
kunalraj.

[yogita]

how can it will store in the database.
and in third method txtticno.text what is it ?

whenever the user can raise the incident at tht time it will directly store the ticketno into the database in tht way i have to do.

[kunalraj]

txtticno is your textbox name which i assume.
Sorry you have not any text box for that on page.
Sorry for that you have to remove that if condition and write that code without condition on your submit click event.


and for insert autogen number you have to write insert query in which you have to take the value of autocode.

e.g,
insert into tablename (ticketno) values ('"+autocode+"');--> autocode is in third method

thanks,
kunalraj

[yogita]

u have specify getlastid in a method bt what this method contain.?

[kunalraj]

that contains second method.

public String getlastId() throws ClassNotFoundException
{
String tmpID = null;

try {

connection code
then write query for check next val or new val

rsk=stmt.executeQuery("select ticno from tablename");--> rsk is object of datareader and stmt is object of sql command.
if(rsk!=null && rsk.next())
{
rsk.last();
tmpID = rsk.getString("ticno");
}
else
{
tmpID="";
}

cn.close();
stmt.close();
rsk.close();
}
catch (SQLException ex)
{
System.out.println("Error"+ex);
}

return(tmpID);
}

[kunalraj]

for connection and query use your .net syntax because i have provided is java.



thanks,
kunalraj.

[yogita]

many syntax is not working in C#.

[kunalraj]

1.)
public static String generateAutoCode(String code) {
String genCode=null;
try{
String leadingCode=code.substring(0,3);
String trailCode=code.substring(3,code.length());
int inc=Integer.parseInt(trailCode)+1;
String fin=String.valueOf(inc);
if(fin.length()==1)
genCode=leadingCode+"000"+fin;
else if(fin.length()==2)
genCode=leadingCode+"00"+fin;
else if(fin.length()==3)
genCode=leadingCode+"0"+fin;
else
genCode=leadingCode+fin;
}
catch(Exception e){
throw e;
}
return genCode;
}

2.)
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader dr = null;

public String getlastId()
{
String tmpID = null;

try {

String connectionString = "write your cnnstring";
con = new SqlConnection(connectionString);
con.Open();
String sql = "select max(ticno) from tablename";
cmd = new SqlCommand(sql, con);
dr = cmd.ExecuteReader();

if(dr.Read())
{
tmpID = dr.getString(1);
}
else
{
tmpID="";
}

con.close();
}
catch (SQLException ex)
{
Response.Write("Error"+ex);
}

return(tmpID);
}

3.)

userdl su= new userdl();--> userdl is class where your getlastId() method means 2. method which you have written
String ucode = su.getlastId();
if(ucode.equals(""))
{
autocode="tic0001";
}
else
{
autocode=CommonClass.generateAutoCode(ucode);--> CommonClass is a class where you have wriiten 1.method which is generateAutocode method.
}

-----> create two class 1.Commonclass
2.userdl
-----> Now write 1.generateAutoCode() method in Commonclass class
and write 2.getlastId() method in userdl class

------>Now write third method in your submit button`s click event.

Now understand...this is .net code

[kunalraj]

1.) method

public static String generateAutoCode(String code)
{
String genCode = null;
try
{
String leadingCode = code.Substring(0, 3);
String trailCode = code.Substring(3, code.Length());

int inc = Int32.Parse(trailCode) + 1;
String fin = inc.ToString();

if (fin.Length() == 1)
genCode = leadingCode + "000" + fin;
else if (fin.Length() == 2)
genCode = leadingCode + "00" + fin;
else if (fin.Length() == 3)
genCode = leadingCode + "0" + fin;
else
genCode = leadingCode + fin;
}
catch (Exception e)
{
Console.Write(e);
}
return genCode;
}

2.) method
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader dr = null;

public String getlastId()
{
String tmpID = null;

try {

String connectionString = "write your cnnstring";
con = new SqlConnection(connectionString);
con.Open();
String sql = "select max(ticno) from tablename";
cmd = new SqlCommand(sql, con);
dr = cmd.ExecuteReader();

if(dr.Read())
{
tmpID = dr.getString(1);
}
else
{
tmpID="";
}

con.close();
}
catch (SQLException ex)
{
Response.Write("Error"+ex);
}

return(tmpID);
}

[r937]

i m using oracle database

what's wrong with using an oracle sequence number for this?

[yogita]

no tht we dnt have to use v have to generate tic001,tic002 to n like this.its a requirement.
i m using .net 2005(C#) three tier architecture.

[kunalraj]

Ok

try this

1.) First you have to create sequence in database.
create sequence sequncename increment by 1 start with 1;


2.) In your code,when inserting records in that table you have to write it

insert into tablename (ticketno) values ('TIC'||TO_CHAR(sequncename.nextval,'0000'));


thanks,
kunalraj

[kunalraj]

hello,

Finally this is true and work in .net

1.) You have to create class temporary name "userdl"

write method which is given below.

SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader dr = null;

public String getlastId()
{
String tmpID = null;
try
{

String connectionString = "YOUR CONN STRING";
con = new SqlConnection(connectionString);
con.Open();
String sql = "select max(CONVERT(numeric(4,0),SUBSTRING(ticno,4,4))) from tablename";-->ticno is column name assuming and its datatype is varchar2
cmd = new SqlCommand(sql, con);
dr = cmd.ExecuteReader();
dr.Read();
if (dr.IsDBNull(0))
{
tmpID = "";
}
else
{
tmpID = dr.GetDecimal(0).ToString();
int sum = Int32.Parse(tmpID) + 1;
tmpID = sum.ToString();
}
}

catch (Exception ex)
{
Console.Write(ex);
}

return (tmpID);
}

2.) now call this method in your submit click event like this

protected void submit_Click(object sender, EventArgs e)
{

try
{
String autocode = null;
userdl su = new userdl();
String ucode = su.getlastId();

if (ucode.Equals(""))
{
autocode = "TIC0001";
}
else
{
if(ucode.Length == 1){
autocode = "TIC000" + ucode;
}
else if (ucode.Length == 2) {
autocode = "TIC00" + ucode;
}
else if (ucode.Length == 3)
{
autocode = "TIC0" + ucode;
}
else if (ucode.Length == 4)
{
autocode = "TIC" + ucode;
}
}

String connectionString = "YOUR CONN STRING";
con = new SqlConnection(connectionString);
con.Open();
cmd = new SqlCommand("insert into demo (ticno) values ('" + autocode + "')", con);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{

Response.Write(ex);

}
finally {
con.Close();
}

Note that this code only for TIC0001(upto four number)
means if you want to change char and number you have to modify code.

thanks,
kunalraj