user7031938 user7031938 - 1 year ago 98
SQL Question

SQL WHERE clause error in

I'm working on an 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;
IsAdded = cmd.ExecuteNonQuery() > 0;
if (IsAdded)
lblMsg.Text = "'" + FirstName + " " + LastName + "' has been successfully added!";
lblMsg.ForeColor = System.Drawing.Color.Green;

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

Any idea?

Answer Source

Try this way

SELECT @FirstName,
                   FROM   tblClock
                   WHERE  CL = @CL) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download