public static string GetAutoCode(string GivenValue, string TableName, string FieldName, string Conds)
{
string AutoCode = "1";
string MaxCode = "1";
if (isNumber(GivenValue) == false)
{
GivenValue = "0";
}
//SqlConnection sqlCon = new SqlConnection(ConfigurationSettings.AppSettings["dbConn"]);
//SqlCommand sqlCmd = new SqlCommand();
SqlConnection sqlCon = new SqlConnection(conSTR);
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlCon;
sqlCmd.Connection.Open();
//TableName = Table Name for get maximum code
//FieldName = Field Name for get maximum code
//Conds = Condision for get maximum code
//GivenValue = If you give value in text box then first check that value is exist or not in table.
//If not exist that value in table or that value is a maximum value of the autonum,
//then accept that number otherwise find max number.
try
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "SELECT ISNULL(MAX(CAST( " + FieldName + " AS NUMERIC)),0)+1 AS AutoCode from " + TableName + " " + Conds;
SqlDataAdapter adp1 = new SqlDataAdapter(sqlCmd);
DataSet SqlDs1 = new DataSet();
adp1.Fill(SqlDs1);
if (SqlDs1.Tables[0].Rows.Count > 0)
{
MaxCode = SqlDs1.Tables[0].Rows[0]["AutoCode"].ToString(); //Get maximum Code Number
}
if (GivenValue == "" || GivenValue == "0" || GivenValue == MaxCode)
{
AutoCode = MaxCode; //If Object(textbox) is empty or Given value ( or textbox value) is equal to maxvalue then assign max value
}
else
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "SELECT " + FieldName + " from " + TableName + " WHERE " + FieldName + " ='" + GivenValue + "'";
SqlDataAdapter adp = new SqlDataAdapter(sqlCmd);
DataSet SqlDs = new DataSet();
adp.Fill(SqlDs);
if (SqlDs.Tables[0].Rows.Count == 0)
{
if (Convert.ToInt16(GivenValue) > Convert.ToInt16(MaxCode))
{
AutoCode = MaxCode; //If GivenValue(textBox value) is greater than Max value then assign max value.
//because we want to maintain sequential Code Number
}
else
{
AutoCode = GivenValue; //If given value not found in DB then accept that Code
}
}
else
{
AutoCode = MaxCode; //If Given value is already exist in DB then assign max value
}
}
}
catch (SqlException Ex)
{
throw Ex;
}
finally
{
sqlCmd.Connection.Close();
}
return AutoCode;
}
}
}
You just write it as a class
Create an object like this
TextBox1.Text = CommonFunctions.GetAutoCode(TextBox1.Text, "UploadDocuments", "Doc_Code", "");
UploadDocuments is the name of the table
and Doc_Code is the column name
|
No responses found. Be the first to respond and make money from revenue sharing program.
|