Can someone please tell me what is wrong with the following sql query that im trying to execute?

Question

 string query = "SELECT e.employeeId, e.firstName, e.lastName, e.position, e.contactNumber, el.email, el.password FROM employee e JOIN employeeLogin el ON e.employeeId = el.employeeId Where e.firstName LIKE @searchName% OR e.lastName LIKE @searchName% ";
            cmd = new SqlCommand(query, _IsqlDataFunctions.GetConnection());
            cmd.Parameters.AddWithValue("@searchName", txtAdminSearchName.Text);

I'm getting the error - "Incorrect syntax near the keyword OR"

i tried putting apostrophes around '@searchName%' but that returned nothing

Answer 1

Try something like this:

  "select * from scoretable where name like @name + '%'"  

Add a single quotation mark for the % sign, and add a + sign in front of it.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Replying in an answer as this is too long for using a comment

Visual Studio does not link to SSMS, instead as mentioned already, write the SQL in SSMS and copy it to your code. For instance this method using this property for it's SQL copied from SSMS. The code was done with .NET Core 5, C#9 but with minor changes will work with older frameworks. What is important is how the SQL was written and copied to code. you can also place the SQL in a file e.g. someFile.sql or someFile.txt then use File.ReadAllText and assign to the CommandText of the Command object.

Any ways, the code above mentioned is called inside a form here.

See also

Microsoft TechNet article I wrote for this topic Writing SQL for your application Part 1 and note in the article I also discuss writing unit test as an added pre-caution which test the SQL and the code using the SQL.

Answer 3

A tip for writing SQL, write your statements in SSMS (SQL-Server Management Studio), most of what's shown below is mouse clicking using design query in editor

--- These become parameters in code  
DECLARE @FirstName NVARCHAR(MAX)= 'm%';  
DECLARE @LastName NVARCHAR(MAX)= 's%';  
  
SELECT E.EmployeeID,   
       E.LastName,   
       E.FirstName,   
       E.Address AS Street,   
       E.City,   
       E.PostalCode,   
       C.Name AS Country  
FROM Employees AS E  
     INNER JOIN Countries AS C ON E.CountryIdentifier = C.CountryIdentifier  
WHERE E.LastName LIKE @LastName OR E.FirstName LIKE @FirstName;  

Never write queries in code, write them in SSMS then paste into a string variable