Hi hemma123,
I have created sample as per your requirement.
HTML
<asp:DropDownList ID="ddlAppCTG" runat="server" OnSelectedIndexChanged="ddlAppCTG_SelectedIndexChanged"
AutoPostBack="true">
<asp:ListItem Text="Select" Value="" />
<asp:ListItem Text="NA" Value="NA" />
<asp:ListItem Text="TA" Value="TA" />
</asp:DropDownList>
<br />
<asp:TextBox runat="server" ID="txtRefNO" />
VB.Net
Protected Sub ddlAppCTG_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim vsno As String = ""
Dim dateAndTime As System.DateTime
Dim constr As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
Using con As New SqlConnection(constr)
con.Open()
Try
Dim sql As String = "SELECT MAX(RIGHT(RefNo, 2)) AS RefNo, MAX(SUBSTRING(RefNo,5,2)) AS Mon FROM KPILogMaster WHERE AppCTG = @AppCTG"
Dim comm As New SqlCommand(sql, con)
comm.Parameters.AddWithValue("@AppCTG", ddlAppCTG.SelectedItem.Value)
dateAndTime = DateTime.Now
Dim rdr1 As SqlDataReader = comm.ExecuteReader()
rdr1.Read()
If rdr1.HasRows Then
If TypeName(rdr1!RefNo) <> "DBNull" Then
Dim tableMonth As String = rdr1("Mon").ToString()
Dim CurrentMonth As String = DateTime.Now.ToString("MM")
If tableMonth = CurrentMonth Then
vsno = (Convert.ToInt32(rdr1("RefNo")) + 1).ToString()
txtRefNO.Text = Convert.ToString(dateAndTime.ToString("yyyyMM") + "-" + ddlAppCTG.SelectedItem.Value + "-" + "R0") & vsno
Else
txtRefNO.Text = dateAndTime.ToString("yyyyMM") + "-" + ddlAppCTG.SelectedItem.Value + "-" + "R01"
End If
Else
txtRefNO.Text = dateAndTime.ToString("yyyyMM") + "-" + ddlAppCTG.SelectedItem.Value + "-" + "R01"
End If
End If
rdr1.Close()
Catch ex As Exception
Finally
con.Close()
End Try
End Using
End Sub
C#
protected void ddlAppCTG_SelectedIndexChanged(object sender, EventArgs e)
{
string vsno = "";
System.DateTime dateAndTime;
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
try
{
string sql = "SELECT MAX(RIGHT(RefNo, 2)) AS RefNo, MAX(SUBSTRING(RefNo,5,2)) AS Mon FROM KPILogMaster WHERE AppCTG = @AppCTG";
SqlCommand comm = new SqlCommand(sql, con);
comm.Parameters.AddWithValue("@AppCTG", ddlAppCTG.SelectedItem.Value);
dateAndTime = DateTime.Now;
SqlDataReader rdr1 = comm.ExecuteReader();
rdr1.Read();
if (rdr1.HasRows)
{
if (rdr1["RefNo"] != DBNull.Value)
{
string tableMonth = rdr1["Mon"].ToString();
string CurrentMonth = DateTime.Now.ToString("MM");
if (tableMonth == CurrentMonth)
{
vsno = (Convert.ToInt32(rdr1["RefNo"]) + 1).ToString();
txtRefNO.Text = dateAndTime.ToString("yyyyMM") + "-" + ddlAppCTG.SelectedItem.Value + "-" + "R0" + vsno;
}
else
{
txtRefNO.Text = dateAndTime.ToString("yyyyMM") + "-" + ddlAppCTG.SelectedItem.Value + "-" + "R01";
}
}
else
{
txtRefNO.Text = dateAndTime.ToString("yyyyMM") + "-" + ddlAppCTG.SelectedItem.Value + "-" + "R01";
}
}
rdr1.Close();
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
}
}