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

Guest User

4y3.2kC#

private DataTable Search()
{
    string query1 = "SELECT WorkerID ,FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation,Checkin,Checkout FROM Member";
    query1 += "where WorkerID Like '%' +@parm1+ '%'";
    query1 += "OR FirstName Like'%' +@parm1+ '%'";
    query1 += "OR Worker Like '%' +@parm1+ '%'";
    query1 += "OR Cnic Like '%' +@parm1+ '%'";
    query1 += "OR CardNumber Like '%' +@parm1+ '%'";
    query1 += "OR FlatNumber Like '%' +@parm1+ '%'";
    query1 += "OR Designation Like '%' +@parm1+ '%'";
    SqlConnection connection = new SqlConnection(myconn);
    SqlCommand cmd = new SqlCommand(query1, connection);
    cmd.Parameters.AddWithValue("@parm1", textBox1.Text);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    dgvEmp.DataSource = dt;
    return (dt);
}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
better create an SP to avoid errors and SqlInjection attack
Create procedure spGetDetails
@searchText NVARCHAR(1000)
as
begin
SELECT WorkerID ,FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation,Checkin,Checkout FROM Member where
1=(
CASE
WHEN (
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(FirstName AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Worker AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Cnic AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(CardNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(FlatNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Designation AS VARCHAR(50)) LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
end
end
private DataTable Search()
{
SqlCommand cmd = new SqlCommand(spGetDetails, connection);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@searchText", textBox1.Text);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
dgvEmp.DataSource = dt;
return (dt);
}
Sachin Singh4y ago
Ohh, my bad
OR is missing too
Create procedure spGetDetails
@searchText NVARCHAR(1000)
as
begin
SELECT WorkerID ,FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation,Checkin,Checkout FROM Member where
1=(
CASE
WHEN (
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(FirstName AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(Worker AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(Cnic AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(CardNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(FlatNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(Designation AS VARCHAR(50)) LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
end
)
end
Sachin Singh4y ago
One '(' is missing
Create procedure spGetDetails
@searchText NVARCHAR(1000)
as
begin
SELECT WorkerID ,FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation,Checkin,Checkout FROM Member where
1=(
CASE
WHEN (
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(FirstName AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Worker AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Cnic AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(CardNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(FlatNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Designation AS VARCHAR(50)) LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
end
)
end
Guest User4y ago
Msg 102, Level 15, State 1, Procedure spGetDetails, Line 10 [Batch Start Line 0]
Incorrect syntax near '('.
Abhishek Chadha4y ago
Could you please confirm what you are getting in query1 variable through debugging
Also write the full exception

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

Guest User

4y3.2kC#

private DataTable Search()
{
    string query1 = "SELECT WorkerID ,FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation,Checkin,Checkout FROM Member";
    query1 += "where WorkerID Like '%' +@parm1+ '%'";
    query1 += "OR FirstName Like'%' +@parm1+ '%'";
    query1 += "OR Worker Like '%' +@parm1+ '%'";
    query1 += "OR Cnic Like '%' +@parm1+ '%'";
    query1 += "OR CardNumber Like '%' +@parm1+ '%'";
    query1 += "OR FlatNumber Like '%' +@parm1+ '%'";
    query1 += "OR Designation Like '%' +@parm1+ '%'";
    SqlConnection connection = new SqlConnection(myconn);
    SqlCommand cmd = new SqlCommand(query1, connection);
    cmd.Parameters.AddWithValue("@parm1", textBox1.Text);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    dgvEmp.DataSource = dt;
    return (dt);
}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
better create an SP to avoid errors and SqlInjection attack
Create procedure spGetDetails
@searchText NVARCHAR(1000)
as
begin
SELECT WorkerID ,FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation,Checkin,Checkout FROM Member where
1=(
CASE
WHEN (
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(FirstName AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Worker AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Cnic AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(CardNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(FlatNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Designation AS VARCHAR(50)) LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
end
end
private DataTable Search()
{
SqlCommand cmd = new SqlCommand(spGetDetails, connection);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@searchText", textBox1.Text);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
dgvEmp.DataSource = dt;
return (dt);
}
Sachin Singh4y ago
Ohh, my bad
OR is missing too
Create procedure spGetDetails
@searchText NVARCHAR(1000)
as
begin
SELECT WorkerID ,FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation,Checkin,Checkout FROM Member where
1=(
CASE
WHEN (
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(FirstName AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(Worker AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(Cnic AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(CardNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(FlatNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')  OR
(CAST(Designation AS VARCHAR(50)) LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
end
)
end
Sachin Singh4y ago
One '(' is missing
Create procedure spGetDetails
@searchText NVARCHAR(1000)
as
begin
SELECT WorkerID ,FirstName,Worker,Cnic,CardNumber,FlatNumber,Designation,Checkin,Checkout FROM Member where
1=(
CASE
WHEN (
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(WorkerID AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(FirstName AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Worker AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Cnic AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(CardNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(FlatNumber AS VARCHAR(50)) LIKE '%' + @searchText + '%')
(CAST(Designation AS VARCHAR(50)) LIKE '%' + @searchText + '%')
)
THEN 1
ELSE 0
end
)
end
Guest User4y ago
Msg 102, Level 15, State 1, Procedure spGetDetails, Line 10 [Batch Start Line 0]
Incorrect syntax near '('.
Abhishek Chadha4y ago
Could you please confirm what you are getting in query1 variable through debugging
Also write the full exception