How to increment number with current year + character string?
up vote
0
down vote
favorite
Public Sub incrPR()
Dim curValue As Integer
Dim result As String
Dim yr As String = Now.Year.ToString()
Dim txt As String = "PR"
Using con As SqlConnection = New SqlConnection(ConString)
con.Open()
Dim cmd = New SqlCommand("Select MAX(SpecialOrderNo) FROM SpecialOrder", con)
result = cmd.ExecuteScalar().ToString
If String.IsNullOrEmpty(result) Then
result = "000"
End If
Int32.TryParse(result, curValue)
curValue += 1
result = curValue.ToString("D3") + "-" + yr + "-" + txt
txtno.Text = result
End Using
End Sub
Expected output:
001-2018-PR
002-2018-PR
003-2018-PR
vb.net
edited Nov 9 at 2:33
Joel Coehoorn
304k94489718
asked Nov 9 at 2:22
Ohubes Rente
31
|
show 1 more comment
up vote
0
down vote
favorite
Public Sub incrPR()
Dim curValue As Integer
Dim result As String
Dim yr As String = Now.Year.ToString()
Dim txt As String = "PR"
Using con As SqlConnection = New SqlConnection(ConString)
con.Open()
Dim cmd = New SqlCommand("Select MAX(SpecialOrderNo) FROM SpecialOrder", con)
result = cmd.ExecuteScalar().ToString
If String.IsNullOrEmpty(result) Then
result = "000"
End If
Int32.TryParse(result, curValue)
curValue += 1
result = curValue.ToString("D3") + "-" + yr + "-" + txt
txtno.Text = result
End Using
End Sub
Expected output:
001-2018-PR
002-2018-PR
003-2018-PR
vb.net
edited Nov 9 at 2:33
Joel Coehoorn
304k94489718
asked Nov 9 at 2:22
Ohubes Rente
31
what is your current result?
– Mukyuu
Nov 9 at 2:31
It's not clear from the question, but at a guess, the problem is you're not saving the change back to the database table.
– Joel Coehoorn
Nov 9 at 2:34
001-2018-PR will save after that the 001 is not increasing. :(
– Ohubes Rente
Nov 9 at 2:38
1
That would be because you are saving the text value but you're not saving the number back toSpecialOrderNo. If you expectMAX(SpecialOrderNo)to return a different value then you have to save a new, larger value to that column with each new record. If you save "001-2018-PR" to some other column but don't save 1 toSpecialOrderNothen of course the max value won't change.
– jmcilhinney
Nov 9 at 2:39
1
What columns do you have in yourSpecialOrdertable? Is one storing the numerical value and another the text value? Personally, I'd suggest that you should be storing the sequential numerical value in one column and the year in another and let the text value be built from those on demand. You can do that in the application and/or in the database.
– jmcilhinney
Nov 9 at 2:43
|
show 1 more comment
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Public Sub incrPR()
Dim curValue As Integer
Dim result As String
Dim yr As String = Now.Year.ToString()
Dim txt As String = "PR"
Using con As SqlConnection = New SqlConnection(ConString)
con.Open()
Dim cmd = New SqlCommand("Select MAX(SpecialOrderNo) FROM SpecialOrder", con)
result = cmd.ExecuteScalar().ToString
If String.IsNullOrEmpty(result) Then
result = "000"
End If
Int32.TryParse(result, curValue)
curValue += 1
result = curValue.ToString("D3") + "-" + yr + "-" + txt
txtno.Text = result
End Using
End Sub
Expected output:
001-2018-PR
002-2018-PR
003-2018-PR
vb.net
edited Nov 9 at 2:33
Joel Coehoorn
304k94489718
asked Nov 9 at 2:22
Ohubes Rente
31
Public Sub incrPR()
Dim curValue As Integer
Dim result As String
Dim yr As String = Now.Year.ToString()
Dim txt As String = "PR"
Using con As SqlConnection = New SqlConnection(ConString)
con.Open()
Dim cmd = New SqlCommand("Select MAX(SpecialOrderNo) FROM SpecialOrder", con)
result = cmd.ExecuteScalar().ToString
If String.IsNullOrEmpty(result) Then
result = "000"
End If
Int32.TryParse(result, curValue)
curValue += 1
result = curValue.ToString("D3") + "-" + yr + "-" + txt
txtno.Text = result
End Using
End Sub
Expected output:
001-2018-PR
002-2018-PR
003-2018-PR
vb.net
vb.net
edited Nov 9 at 2:33
Joel Coehoorn
304k94489718
asked Nov 9 at 2:22
Ohubes Rente
31
edited Nov 9 at 2:33
Joel Coehoorn
304k94489718
asked Nov 9 at 2:22
Ohubes Rente
31
edited Nov 9 at 2:33
Joel Coehoorn
304k94489718
edited Nov 9 at 2:33
Joel Coehoorn
304k94489718
edited Nov 9 at 2:33
Joel Coehoorn
304k94489718
304k94489718
asked Nov 9 at 2:22
Ohubes Rente
31
asked Nov 9 at 2:22
Ohubes Rente
31
asked Nov 9 at 2:22
Ohubes Rente
31
31
what is your current result?
– Mukyuu
Nov 9 at 2:31
It's not clear from the question, but at a guess, the problem is you're not saving the change back to the database table.
– Joel Coehoorn
Nov 9 at 2:34
001-2018-PR will save after that the 001 is not increasing. :(
– Ohubes Rente
Nov 9 at 2:38
1
That would be because you are saving the text value but you're not saving the number back toSpecialOrderNo. If you expectMAX(SpecialOrderNo)to return a different value then you have to save a new, larger value to that column with each new record. If you save "001-2018-PR" to some other column but don't save 1 toSpecialOrderNothen of course the max value won't change.
– jmcilhinney
Nov 9 at 2:39
1
What columns do you have in yourSpecialOrdertable? Is one storing the numerical value and another the text value? Personally, I'd suggest that you should be storing the sequential numerical value in one column and the year in another and let the text value be built from those on demand. You can do that in the application and/or in the database.
– jmcilhinney
Nov 9 at 2:43
|
show 1 more comment
what is your current result?
– Mukyuu
Nov 9 at 2:31
It's not clear from the question, but at a guess, the problem is you're not saving the change back to the database table.
– Joel Coehoorn
Nov 9 at 2:34
001-2018-PR will save after that the 001 is not increasing. :(
– Ohubes Rente
Nov 9 at 2:38
1
That would be because you are saving the text value but you're not saving the number back toSpecialOrderNo. If you expectMAX(SpecialOrderNo)to return a different value then you have to save a new, larger value to that column with each new record. If you save "001-2018-PR" to some other column but don't save 1 toSpecialOrderNothen of course the max value won't change.
– jmcilhinney
Nov 9 at 2:39
1
What columns do you have in yourSpecialOrdertable? Is one storing the numerical value and another the text value? Personally, I'd suggest that you should be storing the sequential numerical value in one column and the year in another and let the text value be built from those on demand. You can do that in the application and/or in the database.
– jmcilhinney
Nov 9 at 2:43
what is your current result?
– Mukyuu
Nov 9 at 2:31
what is your current result?
– Mukyuu
Nov 9 at 2:31
It's not clear from the question, but at a guess, the problem is you're not saving the change back to the database table.
– Joel Coehoorn
Nov 9 at 2:34
It's not clear from the question, but at a guess, the problem is you're not saving the change back to the database table.
– Joel Coehoorn
Nov 9 at 2:34
001-2018-PR will save after that the 001 is not increasing. :(
– Ohubes Rente
Nov 9 at 2:38
001-2018-PR will save after that the 001 is not increasing. :(
– Ohubes Rente
Nov 9 at 2:38
1
1
That would be because you are saving the text value but you're not saving the number back to
SpecialOrderNo. If you expect MAX(SpecialOrderNo) to return a different value then you have to save a new, larger value to that column with each new record. If you save "001-2018-PR" to some other column but don't save 1 to SpecialOrderNo then of course the max value won't change.– jmcilhinney
Nov 9 at 2:39
That would be because you are saving the text value but you're not saving the number back to
SpecialOrderNo. If you expect MAX(SpecialOrderNo) to return a different value then you have to save a new, larger value to that column with each new record. If you save "001-2018-PR" to some other column but don't save 1 to SpecialOrderNo then of course the max value won't change.– jmcilhinney
Nov 9 at 2:39
1
1
What columns do you have in your
SpecialOrder table? Is one storing the numerical value and another the text value? Personally, I'd suggest that you should be storing the sequential numerical value in one column and the year in another and let the text value be built from those on demand. You can do that in the application and/or in the database.– jmcilhinney
Nov 9 at 2:43
What columns do you have in your
SpecialOrder table? Is one storing the numerical value and another the text value? Personally, I'd suggest that you should be storing the sequential numerical value in one column and the year in another and let the text value be built from those on demand. You can do that in the application and/or in the database.– jmcilhinney
Nov 9 at 2:43
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
The specific reason that you're not seeing the behaviour you want is here:
Int32.TryParse(result, curValue)
If result is "001-2018-PR" then TryParse will fail, i.e. return False, and that means that curValue will be zero. That means that you are going to get zero EVERY time. If you want to parse the first part of the text then do that rather than parsing the whole thing, e.g.
Int32.TryParse(result.Split("-"c)(0), curValue)
I would change things significantly but that will fix your immediate issue.
answered Nov 9 at 2:33
jmcilhinney
24.7k21932
IT WORKS THANK YOU!!!
– Ohubes Rente
Nov 9 at 3:46
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
The specific reason that you're not seeing the behaviour you want is here:
Int32.TryParse(result, curValue)
If result is "001-2018-PR" then TryParse will fail, i.e. return False, and that means that curValue will be zero. That means that you are going to get zero EVERY time. If you want to parse the first part of the text then do that rather than parsing the whole thing, e.g.
Int32.TryParse(result.Split("-"c)(0), curValue)
I would change things significantly but that will fix your immediate issue.
answered Nov 9 at 2:33
jmcilhinney
24.7k21932
IT WORKS THANK YOU!!!
– Ohubes Rente
Nov 9 at 3:46
add a comment |
up vote
0
down vote
accepted
The specific reason that you're not seeing the behaviour you want is here:
Int32.TryParse(result, curValue)
If result is "001-2018-PR" then TryParse will fail, i.e. return False, and that means that curValue will be zero. That means that you are going to get zero EVERY time. If you want to parse the first part of the text then do that rather than parsing the whole thing, e.g.
Int32.TryParse(result.Split("-"c)(0), curValue)
I would change things significantly but that will fix your immediate issue.
answered Nov 9 at 2:33
jmcilhinney
24.7k21932
IT WORKS THANK YOU!!!
– Ohubes Rente
Nov 9 at 3:46
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
The specific reason that you're not seeing the behaviour you want is here:
Int32.TryParse(result, curValue)
If result is "001-2018-PR" then TryParse will fail, i.e. return False, and that means that curValue will be zero. That means that you are going to get zero EVERY time. If you want to parse the first part of the text then do that rather than parsing the whole thing, e.g.
Int32.TryParse(result.Split("-"c)(0), curValue)
I would change things significantly but that will fix your immediate issue.
answered Nov 9 at 2:33
jmcilhinney
24.7k21932
The specific reason that you're not seeing the behaviour you want is here:
Int32.TryParse(result, curValue)
If result is "001-2018-PR" then TryParse will fail, i.e. return False, and that means that curValue will be zero. That means that you are going to get zero EVERY time. If you want to parse the first part of the text then do that rather than parsing the whole thing, e.g.
Int32.TryParse(result.Split("-"c)(0), curValue)
I would change things significantly but that will fix your immediate issue.
answered Nov 9 at 2:33
jmcilhinney
24.7k21932
edited Nov 9 at 3:35
answered Nov 9 at 2:33
jmcilhinney
24.7k21932
answered Nov 9 at 2:33
jmcilhinney
24.7k21932
answered Nov 9 at 2:33
jmcilhinney
24.7k21932
24.7k21932
IT WORKS THANK YOU!!!
– Ohubes Rente
Nov 9 at 3:46
add a comment |
IT WORKS THANK YOU!!!
– Ohubes Rente
Nov 9 at 3:46
IT WORKS THANK YOU!!!
– Ohubes Rente
Nov 9 at 3:46
IT WORKS THANK YOU!!!
– Ohubes Rente
Nov 9 at 3:46
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53218956%2fhow-to-increment-number-with-current-year-character-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
what is your current result?
– Mukyuu
Nov 9 at 2:31
It's not clear from the question, but at a guess, the problem is you're not saving the change back to the database table.
– Joel Coehoorn
Nov 9 at 2:34
001-2018-PR will save after that the 001 is not increasing. :(
– Ohubes Rente
Nov 9 at 2:38
1
That would be because you are saving the text value but you're not saving the number back to
SpecialOrderNo. If you expectMAX(SpecialOrderNo)to return a different value then you have to save a new, larger value to that column with each new record. If you save "001-2018-PR" to some other column but don't save 1 toSpecialOrderNothen of course the max value won't change.– jmcilhinney
Nov 9 at 2:39
1
What columns do you have in your
SpecialOrdertable? Is one storing the numerical value and another the text value? Personally, I'd suggest that you should be storing the sequential numerical value in one column and the year in another and let the text value be built from those on demand. You can do that in the application and/or in the database.– jmcilhinney
Nov 9 at 2:43