Dear friends

I am using vb 2008 with access 2007 at the backend. I want to make custome invoice id like Inv0001. when make next invoice the id should be Inv002 and so on. add 1 numeric value in the previous one. Please help me

any help will greatly appriciated

get the max value of your ID in your database then add one

like this

SELECT MAX(ID) as MAXID from tblID

to add the leading zeroes you can pad the number out by

nextid.ToString("0000")

once you have maxid. You can generate the next id in the select by

select max(ID)+1 as nextid from tblID

Edited 4 Years Ago by Reverend Jim: n/a

my following code will only show 1 numeric value in TransIDTextBox however i want to showing this format like 00001 or 00002

Private Sub transIDload()
ds.Clear()
da = New OleDbDataAdapter("Select * FROM Transactions", dbcon)
da.Fill(ds)
da.Dispose()
'dbcon.Close()
If ds.Tables(0).Rows.Count > 0 Then
txtTransID.Text = ds.Tables(0).Rows.Count + 1
txtTransID.ReadOnly = True
txtTransDate.Text = Format(Now, "short date")
txtTransNo.Text = txtTransID.Text
txtDep.Text = "0.00"
txtWda.Text = "0.00"
Else
txtTransID.Text = 1
txtTransID.ReadOnly = True

End If
'DataGridView1.DataSource = ds.Tables(0)

End Sub

Get the MAX(ID) from table and then append 0000 to it and show.

Forgot the one thing to find the length of ID there only easy when you add 10,11...,100,101,....,1000,1001...

temp_id=string.Empty;
for(int i=0;i<(5-id.lenth);i++)
{
 temp_id+="0";
}
temp_id+=id;

Forgot the one thing to find the length of ID there only easy when you add 10,11...,100,101,....,1000,1001...

temp_id=string.Empty;
for(int i=0;i<(5-id.lenth);i++)
{
 temp_id+="0";
}
temp_id+=id;

I have done the following to get the custom ids like DOC1, DOC2, DOC3 and so on....

I have create a table which will store the number in it....

and the master table will just save the value in database with string

eg.

I have a table as IDvalues with one field that is DOCValue and datatype number with value 1

in the DOctormaster table I have the ID field as the primary key and datatype as text....

when I click on save I first take the max value from IDvalues table and the value is appended with "DOC" string and saved in Doctormaster table as DOC1.....

Soon after saving I have incremented the value in IDvalue by 1.....

I have done it this way since when I had created a field as ID in doctormaster table with datatype as Text it used to get appended correctly till DOC10 but after that it showed problem....

So I opted for this long but safe method....

U can give it a try....even if u dont get it....just post again....

Edited 4 Years Ago by poojavb: n/a

But i prefer to have primary key as integer not as text or varchar. indexing will be done on primary key if it is text performance will go down. U can have one more column in whci u can save the data Doc1,Doc2 etc..

But i prefer to have primary key as integer not as text or varchar. indexing will be done on primary key if it is text performance will go down. U can have one more column in whci u can save the data Doc1,Doc2 etc..

I have posted for getting the string Doc1 and Doc2......and so on....

Edited 4 Years Ago by poojavb: n/a

in the DOctormaster table I have the ID field as the primary key and datatype as text....

Data type u mentioned is of varchar which i said not preferable.

I have done the following to get the custom ids like DOC1, DOC2, DOC3 and so on....

I have create a table which will store the number in it....

and the master table will just save the value in database with string

eg.

I have a table as IDvalues with one field that is DOCValue and datatype number with value 1

in the DOctormaster table I have the ID field as the primary key and datatype as text....

when I click on save I first take the max value from IDvalues table and the value is appended with "DOC" string and saved in Doctormaster table as DOC1.....

Soon after saving I have incremented the value in IDvalue by 1.....

I have done it this way since when I had created a field as ID in doctormaster table with datatype as Text it used to get appended correctly till DOC10 but after that it showed problem....

So I opted for this long but safe method....

U can give it a try....even if u dont get it....just post again....

You can try manually increment the numeric value for example get the string after first 3 letter (ie., Doc10 => "10") convert to integer then increment one then concatenate (ie., "doc"+"11")

But that doesn't get you leading zeroes. For that you need to apply formatting such as

nextid.ToString("0000")

By the way, while it is true that there is a performance hit when you use a varchar as a primary key, it will likely be negligible unless the database is very large.

Edited 4 Years Ago by Reverend Jim: n/a

This article has been dead for over six months. Start a new discussion instead.