/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Monday, December 17, 2018

Validation to Check Duplicate Records

Prevent from inserting duplicate records into the table

You need to check if a duplicate entry exists in table before inserting the data. I suggest you could refer to the solution given below:

Approach 1=>

using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultCSRConnection"].ConnectionString))
            {
                //Open your connection
                conn.Open();

                //Your Select Query
                //Change this as per your design and provide the primary key field here
                string selectString = "SELECT COUNT(*) FROM tbl_students WHERE YourIDField = @idfield";

                //Create Command object
                SqlCommand myCommand = new SqlCommand(selectString, conn);

                //Pass your parameter here
                myCommand.Parameters.AddWithValue("@id", yourexistingidvalue);

                // Get the Result query
                var idExists = (Int32)myCommand.ExecuteScalar() > 0;

                //Check if record exists in table
                if (!idExists)
                {
                    //Insert the Record
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        SqlCommand cmd1 = new SqlCommand("Insert into tbl_students (first_name,last_name,sex,dob,active) values(@first_name, @last_name,@sex,@dob,@active)", conn);
                        cmd1.Parameters.Add("@first_name", SqlDbType.NVarChar).Value = txtFname.Text;
                        cmd1.Parameters.Add("@last_name", SqlDbType.NVarChar).Value = txtLname.Text;
                        cmd1.Parameters.Add("@sex", SqlDbType.NVarChar).Value = ddlgender.SelectedValue;
                        cmd1.Parameters.Add("@dob", SqlDbType.DateTime).Value = txtDateofBirth.Text;
                        cmd1.Parameters.Add("@active", SqlDbType.Bit).Value = Convert.ToInt32(ddlActiveInactive.SelectedValue);
                        cmd1.ExecuteNonQuery();
                        conn.Close();
                        ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('page added sucessfully');window.location ='csrstudentprofile.aspx';", true);
                    }
                }
            }
Approach 2=>

CREATE PROC ValidateAndInsertStudent
(
@first_name VARCHAR(50),
@last_name VARCHAR(50),
@sex VARCHAR(50),
@dob DateTime,
@active BIT,
@Done BIT,
@Msg VARCHAR(200)
)
AS 
BEGIN
 SET @Done = 1,
 SET @Msg = ''
 IF (SELECT COUNT(*) FROM tbl_students WHERE first_name=@first_name AND last_name=@last_name AND sex=@sex AND dob=@dob AND active=@active)>0
 BEGIN
  SET @Done = 0
  SET @Msg = 'Record exists'
  RETURN;
 END
 Insert into tbl_students (first_name,last_name,sex,dob,active) values(@first_name, @last_name,@sex,@dob,@active)
END
Approach 3=> Using Output Parameter

CREATE PROCEDURE [dbo].[InsertsFruitName]
      @FruitId INT,
      @Exists VARCHAR(30OUTPUT,
      @Details VARCHAR(300) 
AS
BEGIN
      SET NOCOUNT ON;
      IF EXISTS (SELECT TOP 1 FROM Fruits WHERE FruitId = @FruitId)
      BEGIN
      SELECT @Exists = 'EXISTS' 
     END
     ELSE
     BEGIN
         SELECT @Exists = '' 
         INSERT INTO Fruits (Details) VALUES                                              (@Details) WHERE FruitId = @FruitId
     END
END

Return Output parameter from Stored Procedure in ASP.Net

protected void Submit(object sender, EventArgs e)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {insert
        using (SqlCommand cmd = new SqlCommand("InsertsFruitName", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@FruitId"int.Parse(txtFruitId.Text.Trim()));
cmd.Parameters.AddWithValue("@Details"int.Parse(txtFruitDetails.Text.Trim()));
            cmd.Parameters.Add("@Exists"SqlDbType.VarChar, 30);
            cmd.Parameters["@Exists"].Direction = ParameterDirection.Output;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
IF(cmd.Parameters["@Exists"].Value.ToString() == "")
{
            lblFruitName.Text = "Fruit Details Added Successfully."';
}
else
{
            lblFruitName.Text = "Fruits Details Already Exists";
}
        }
    }
}



Read More »

Monday, October 29, 2018

Converting String in to CSV files in C Sharp







Function For Creating CSV files in C#

public static string FormatCSV(string input)
    {
        try
        {
            if (input == null)
                return string.Empty;

            bool containsQuote = false;
            bool containsComma = false;
            int len = input.Length;
            for (int i = 0; i < len && (containsComma == false || containsQuote == false); i++)
            {
                char ch = input[i];
                if (ch == '"')
                    containsQuote = true;
                else if (ch == ',')
                    containsComma = true;
            }

            if (containsQuote && containsComma)
                input = input.Replace("\"", "\"\"");

            if (containsComma)
                return "\"" + input + "\"";
            else
                return input;
        }
        catch
        {
            throw;
        }
    }
Read More »

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago