Hello,
Can guide me how to generate sequence Number for current Year/Month?
Here my code:
Protected Sub ddlAppCTG_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Dim rdr1 As SqlDataReader
Dim vsno As String
Dim dateAndTime As Date
Dim vyear As System.DateTime
Dim constr As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
Using con As New SqlConnection(constr)
con.Open()
Try
Dim sql As String = "IF EXISTS (select SUBSTRING(RefNo,8,2) from KPILogMaster) BEGIN select max(RIGHT(RefNo, 2)) AS RefNo from KPILogMaster WHERE AppCTG=@AppCTG END"
Dim comm As SqlCommand = New SqlCommand(sql, con)
comm.Parameters.AddWithValue("@AppCTG", ddlAppCTG.SelectedItem.Value)
dateAndTime = Now
rdr1 = comm.ExecuteReader
rdr1.Read()
If rdr1.HasRows Then
If TypeName(rdr1!RefNo) <> "DBNull" Then
vsno = rdr1!RefNo + 1
txtRefNO.Text = CStr(Format(dateAndTime, "yyyyMM").ToString + "-" + ddlAppCTG.SelectedItem.Value + "-" + "R0" + vsno).ToString()
Else
txtRefNO.Text = Format(dateAndTime, "yyyyMM").ToString + "-" + ddlAppCTG.SelectedItem.Value + "-" + "R0" + "1"
End If
End If
rdr1.Close()
Catch ex As Exception
End Try
con.Close()
End Using
End Sub
my code generating based on last number of selected dropdownlist.
201705-NA-001, 201706-NA-002
suppose refresh new sequence number based on new month.
201705-NA-001, 201706-NA-001
Please advice.