using this code we can do it simply
public string CJ()
{
string Id = GenerateId("cust", "cust_id", 6, "ELG", true);
return Id;
}
public string GenerateId(string TableName, string ColumnName, int ColumnLength, string Prefix, bool Padding)
{
string Query, con, Id;
con = "Data Source=CJ\\SQLEXPRESS;Initial Catalog=seq;Persist Security Info=True;User ID=sa;Password=123";
SqlConnection cn = new SqlConnection(con);
int preLength,padLength;
preLength = Convert.ToInt32(Prefix.Length);
padLength = ColumnLength - preLength;
if (Padding == true )
{
Query = "SELECT '" + Prefix + "' + REPLACE(STR(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(preLength + 1) + "," + padLength + ") AS INTEGER))+1," + padLength + "),' ',0) FROM " + TableName;
}
else
{
Query = "SELECT '" + Prefix + "' + CAST(MAX(CAST(SUBSTRING(" + ColumnName + "," + Convert.ToString(preLength + 1) + "," + padLength + ") AS INTEGER))+1 AS VARCHAR) FROM " + TableName;
}
SqlCommand com = new SqlCommand(Query, cn);
cn.Open();
if (com.ExecuteScalar().ToString() == "")
{
Id = Prefix;
if (Padding == true)
{
for (int i = 1; i padLength - 1; i++)
{
Id += "0";
}
}
Id += "1";
}
else
{
Id = Convert.ToString(com.ExecuteScalar());
}
cn.Close();
return Id;
}
thanxx for the help
just add the method CJ()
as i have done here
protected void Button1_Click(object sender, EventArgs e)
{
string con;
con = "Data Source=CJ\\SQLEXPRESS;Initial Catalog=seq;Persist Security Info=True;User ID=sa;Password=123";
using (SqlConnection cn = new SqlConnection(con))
{
cn.Open();
using(SqlTransaction trans = cn.BeginTransaction())
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO cust([cust_id],[cust_name],[cust_add]) VALUES(@cust_id,@cust_name,@cust_add)";
cmd.Parameters.Add("@cust_id",CJ());
cmd.Parameters.Add("@cust_name",TextBox1.Text);
cmd.Parameters.Add("@cust_add",TextBox2.Text);
cmd.ExecuteNonQuery();
trans.COmmit();
}
cn.Close();
Response.Write("alert('DATA SAVED')");
TextBox1.Text = "";
TextBox2.Text = "";
}
}