I am using C# with a data repeater tied to a table adapter to pull a few columns from a table. I have 3 FillBy clauses on my adapter, FillByFirstName, FillByLastName and FillByMRN (Medical Record Number). These all three pull data from a table in SQL 2000 called Clients.
The First Name QUERY looks like this and it works as desired:
SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB, RACE_Code FROM dbo.Clients WHERE CL_FName = ? ORDER BY CL_LName, CL_FName
The Last Name QUERY looks like this and it too works as desired:
SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB, RACE_Code FROM dbo.Clients WHERE CL_LName = ? ORDER BY CL_LName, CL_FName
The MRN QUERY is where it fails and it is different because it utilizes the SQL LIKE statement.
After tons of searching online I've found "solutions" that are touted as working but utterly fail for me.
Firstly solution I have failed with is:
Table Adapter Query:
SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB, RACE_Code FROM dbo.Clients WHERE (MED_REC_NO LIKE ?) ORDER BY CL_LName, CL_FName
C# code:
clientsTableAdapter.FillByMRN(clinicalSystemDataSet.Clients, "%" + searchStr + "%");
This returns no error and no results.
When I trace it in SQL it's executed like this (I've removed only the line breaks and some spacing).
exec sp_executesql N'SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB, RACE_Code FROM dbo.Clients WHERE (MED_REC_NO LIKE @P1) ORDER BY CL_LName, CL_FName', N'@P1 char(1024)', '%2222%'
The spacing I removed is... the %2222% in the last portion is padded out to 1024 characters. I don't think this is at issue because the working methods without the like statement are padded as well. IF this is at issue, I don't know how to change the behavior since the table adapter creates this query in cohort with SQL.
I CANNOT FIGURE OUT WHY THE ABOVE METHOD FAILS Executing it directly in sql in this format returns an empty recordset with our without padding spaces. There are 11 records in my table that return with this WORKING query:
SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB, RACE_Code FROM dbo.Clients WHERE (MED_REC_NO LIKE '%2222%') ORDER BY CL_LName, CL_FName
...which is the above sql trace transformed into the actual statement without params and with the identical selection criteria.
The second solution that fails for me is this:
Table Adapter Query:
SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB, RACE_Code FROM dbo.Clients WHERE (MED_REC_NO LIKE '%' + ? + '%') ORDER BY CL_LName, CL_FName
While this does NOT work when calling the method from code, when editing the statement via the DataSet Designer, if I click the QUERY BUILDER and test it (it prompts for the parameter) it will return the desired set of records.
C# portion:
clientsTableAdapter.FillByMRN(clinicalSystemDataSet.Clients, searchStr);
This returns NO results and a SQL trace shows:
exec sp_executesql N'SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB, RACE_Code FROM dbo.Clients WHERE (MED_REC_NO LIKE ''%'' + @P1 + ''%'') ORDER BY CL_LName, CL_FName', N'@P1 char(1024)', '2222'
Again the 2222 is padded out to 1024 characters. But notice that the '%' in the sql is changed to ''%''. The adapter/SQL is fixing single quotes to double single quotes FOR me.
Additionally I've tried passing % without the single quotes but it only results in syntax errors in the table adapter. I've tried passing double quotes, with syntax errors as well.. and a few other miscellaneous "this should fail, but why not" type things.
In almost every post one of these two ways is demonstrated to be a working way to do it, but I just can't make it work.
There appears to be a disconnect in how the tableadapter should be presenting sql and how it actually is.. especially when the query builder shows success using the exact same statement that fails during runtime.
Note: I haven't worked with TableAdapter.
However, I tried creating a temporary table in SQL Server with a field of char(1024) and used the like query.
My hunch is that, you will have to modify the TableAdapter query to
SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB, RACE_Code
FROM dbo.Clients WHERE (MED_REC_NO LIKE RTRIM(?))
ORDER BY CL_LName, CL_FName
The reason (my guess) is that the field is of data type char(1024). So, it could be padding the argument to make it the length of 1024 characters after 2222.
So, the query could become
exec sp_executesql N'SELECT CL_LName, CL_FName, MED_REC_NO, CL_SEX, CL_DOB,
RACE_Code FROM dbo.Clients WHERE (MED_REC_NO LIKE @P1) ORDER BY
CL_LName, CL_FName', N'@P1 char(1024)', '%2222........%'
Where ...... will be the padding of space. As a result, the query could be interpreted as, get me records with MRN that starts with 2222 and rest is followed by space, for which there might not be a matching record in your table.
In my guess, adding RTRIM to the query should work.