help: auto generate number in date format

[sw8revenge]

please check my code: it use to generate number in date format my problem is that it doesnt show any output in the textbox nor in the database
the last num in my database is 2012010001

Dim i As Integer
        Dim strSQL As String
        Dim strMonth As String
        Dim strYear As String
        Dim strTheID As String
        Dim today As Date
        Dim strNewID As String = ""
        Dim cmd As SqlCommand
        Dim myreader As SqlDataReader

        today = Now

        strMonth = Format(today, "MM")
        strYear = Format(today, "yy")

        strTheID = strMonth & strYear
        strSQL = "SELECT clearanceno FROM app_information WHERE LEFT(clearanceno,4)='"
        strSQL = strSQL & strTheID & "' ORDER BY clearanceno DESC;"

        cmd = New SqlCommand(strSQL, Con)
        myreader = cmd.ExecuteReader

        If myreader.HasRows Then
            myreader.Read()
            strSQL = myreader.Item("clearanceno")
            strSQL = Mid(strSQL, 5)

            i = CInt(strSQL) + 1

            Select Case i
                Case i > 999
                    strNewID = strTheID & i.ToString
                Case i > 99
                    strNewID = strTheID & "0" & i.ToString
                Case i > 9
                    strNewID = strTheID & "00" & i.ToString
                Case Else
                    strNewID = strTheID & "000" & i.ToString
            End Select
        End If

        myreader.Close()
        strClearID = strNewID
        txtClearanceNo.Text = strNewID
        strClearID = ""

[Smith5646]

yy returns only the last two digits of the year (12). The variable strTheId in your program = 0112. Try extracting yyyy instead of yy to get 2012 and then concatenate them in the reverse sequence...strYear & strMonth to get 201201.

[sw8revenge]

yy returns only the last two digits of the year (12). The variable strTheId in your program = 0112. Try extracting yyyy instead of yy to get 2012 and then concatenate them in the reverse sequence...strYear & strMonth to get 201201.

thank i got it right now :D but i got another problem it already shows result the problem is when data in the database does not start in the date today it does not show any result in the textbox but when i changed the data in the date today it shows result what should i do sir?
heres the code

Dim i As Integer
Dim strSQL As String
Dim strMonth As String
Dim strYear As String
Dim strTheID As String
Dim today As Date
Dim strNewID As String = ""
Dim cmd As SqlCommand
Dim myreader As SqlDataReader

today = Now

strMonth = Format(today, "MM")
strYear = Format(today, "yyyy")

strTheID = strYear & strMonth
strSQL = "SELECT clearanceno FROM app_information WHERE LEFT(clearanceno,6)='"
strSQL = strSQL & strTheID & "'ORDER BY clearanceno DESC;"

cmd = New SqlCommand(strSQL, Con)

' MessageBox.Show(strSQL)
myreader = cmd.ExecuteReader

If myreader.HasRows Then
myreader.Read()

strSQL = myreader.Item("clearanceno")
strSQL = Mid(strSQL, 7)

i = CInt(strSQL) + 1

Select Case i
Case i > 999
strNewID = strTheID & i.ToString
Case i > 99
strNewID = strTheID & "0" & i.ToString
Case i > 9
strNewID = strTheID & "00" & i.ToString
Case Else
strNewID = strTheID & "000" & i.ToString
End Select

End If
myreader.Close()
strClearID = strNewID
txtClearanceNo.Text = strNewID

[Smith5646]

I'm not sure what you are asking. Are you saying that you are having a problem where there aren't any records beginning with 201201? If that is the case, I would add an else to the If myreader.HasRows to set strNewID to whatever you want when you don't have records. For example:

If myreader.HasRows Then
    ...current code
Else
    strNewID = strTheID & "0001"
End If

[sw8revenge]

I'm not sure what you are asking. Are you saying that you are having a problem where there aren't any records beginning with 201201? If that is the case, I would add an else to the If myreader.HasRows to set strNewID to whatever you want when you don't have records. For example:

If myreader.HasRows Then
    ...current code
Else
    strNewID = strTheID & "0001"
End If

what i observe is when data start with the previous date like 2011120001 it does not generate data unless i change the data into the present date like 201201001 thats the time it show output in the textbox. actually the code works the problem is that when data starts with the previous or older months it wont generate date unless i changed the data

[Smith5646]

I'm still not understanding the issue. Your logic retrieves current year and current month (currently 201201) and then retrieves rows where clearanceno begins with that value. I don't understand what 2011120001 has to do with this as it is in the prior month (201112) and would not be found by your SQL statement. Do you expect to see 2011120001 if there are no rows beginning with 201201? If so, you need to change your SQL to include <=, not just = as in LEFT(clearanceno,6)<=.

[sw8revenge]

I'm still not understanding the issue. Your logic retrieves current year and current month (currently 201201) and then retrieves rows where clearanceno begins with that value. I don't understand what 2011120001 has to do with this as it is in the prior month (201112) and would not be found by your SQL statement. Do you expect to see 2011120001 if there are no rows beginning with 201201? If so, you need to change your SQL to include <=, not just = as in LEFT(clearanceno,6)<=.

sir, what i mean is that when the record, in the database does not match the date today it wont generate record, unless i'll edit the record in the database into the date today.
ex. the old record in the database is 201101 when i edit it into the present date into 201201 thats the time it will generate the data. sorry i've put 0001 in the previous comment ^_^

[Smith5646]

I still don't understand the issue so let me break down what I think you are telling me and tell me where the issue is.

• The last row in your database starts with 201112.
• Current search value is 201201 (YYYYMM) which does not exist in the database.
• Because there isn't a 201201 record, the If myreader.HasRows is false.
• When myreader.HasRows is false, strNewID is never assigned a value.
• Because strNewID is never assigned a value, txtClearanceNo.Text is blank when the form is displayed.

If there are no records for 201201, what do you expect to see in txtClearanceNo? Do you expect the program to display the record from 201112?