SQL WHERE clause error in ASP.net

Question

I'm working on an asp.net page in C#. I have a table with 3 columns (FirstName, CLNum, LastName). I'm trying to use a simple SQL query to prevent users from entering duplicate data in the CLNum. Here's what I tried...

INSERT INTO tblClock ([FirstName],[CL],[LastName]) VALUES (@FirstName, @CL, @LastName) WHERE @CL NOT IN (SELECT CL FROM tblClock)

The page compiles fine but then when I try to submit data, I get this error:

Incorrect syntax near the keyword 'WHERE'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.

Source Error:

Line 67: cmd.Connection = sqlCon;

Line 68: sqlCon.Open();

Line 69: IsAdded = cmd.ExecuteNonQuery() > 0;

Line 70: sqlCon.Close();

Line 71: }

Below is the section of the code i'm working on:

 //Insert click event to insert new record to database
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        bool IsAdded = false;
        string FirstName = txtFirstName.Text.Trim();
        int CL = Convert.ToInt32(txtCL.Text);
        string LastName = txtLastName.Text;
        using (SqlConnection sqlCon = new SqlConnection(conn))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "INSERT INTO tblClock ([FirstName],[CL],[LastName]) VALUES (@FirstName, @CL, @LastName) WHERE @CL NOT IN (SELECT CL FROM tblClock)";
                cmd.Parameters.AddWithValue("@FirstName", FirstName);
                cmd.Parameters.AddWithValue("@CL", CL);
                cmd.Parameters.AddWithValue("@LastName", LastName);
                cmd.Connection = sqlCon;
                sqlCon.Open();
                IsAdded = cmd.ExecuteNonQuery() > 0;
                sqlCon.Close();
            }
        }
        if (IsAdded)
        {
            lblMsg.Text = "'" + FirstName + " " + LastName + "' has been successfully added!";
            lblMsg.ForeColor = System.Drawing.Color.Green;

            BindSubjectData();
        }
        else
        {
            lblMsg.Text = "Error while adding '" + FirstName + " " + LastName + "' subject details";
            lblMsg.ForeColor = System.Drawing.Color.Red;
        }
        ResetAll();//to reset all form controls
    }

Any idea?

Possible duplicate of http://stackoverflow.com/questions/913841/mysql-conditional-insert — TheFallenOne, Oct 27 '16 at 18:24

score 0 · Accepted Answer · answered Oct 27 '16 at 17:57

0

Try this way

INSERT INTO tblClock
            ([FirstName],[CL],[LastName])
SELECT @FirstName,
       @CL,
       @LastName
WHERE  NOT EXISTS (SELECT 1
                   FROM   tblClock
                   WHERE  CL = @CL)

answered Oct 27 '16 at 17:57

Pரதீப்

91,748
19
131
172

SQL WHERE clause error in ASP.net

1 Answers1