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
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.
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....
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....
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.