% wildcart problem

  • Thread starter Thread starter Globetrotter
  • Start date Start date
G

Globetrotter

I am new to C# and using VS2008 and SQL 2005. For doing some exercises I
have made the following code:

string name = tstxtName.Text + "%";
this.taCountry.FillByName(this.dsTest.Country, name);

Problem I have is that the % character behaves like there is one character
at that position while it need to behave like there are zero or more
characters in that position.

For example when I have a country with name USA, US% will show the record
with name USA while U% doesn't show anything. When I preview the data
directly within the Query Builder of VS2008, it works perfect.

Has anybody an idea what can be the problem?
 
Let's see the TableAdapter FillBy query.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Here it is:

SELECT PkCountryId, Flag, CodeA2, CodeA3, Name, NameNL, RecCreatedAt,
RecUpdatedAt, RecCreatedBy, RecUpdatedBy
FROM Country
WHERE (Name LIKE @Name)

When I execute this query via the query builder, it works correctly.
 
I have seen somewhere else in this newsgroup that you can use the SQL
profiler. This is what is the query:
exec sp_executesql N'SELECT PkCountryId, Flag, CodeA2, CodeA3, Name, NameNL,
RecCreatedAt, RecUpdatedAt, RecCreatedBy, RecUpdatedBy
FROM Country
WHERE (Name LIKE @Name)',N'@Name nchar(50)',@Name=N'ne%
 
I have used SQL profiler with a query direct from the Query Builder. The
result was:
exec sp_executesql N'SELECT PkCountryId, Flag, CodeA2, CodeA3, Name, NameNL,
RecCreatedAt, RecUpdatedAt, RecCreatedBy, RecUpdatedBy
FROM Country
WHERE (Name LIKE @Name)',N'@Name nvarchar(50)',@Name=N'ne%

The difference is the nchar(50) and nvarchar(50) type in both queries. I
have now changed the type to nvarchar(50) in the SQL table and it works now
correctly. However I still don't understand why it didn't work with the
nchar(50) type. Does anybody know that?
 
Ah, yes. When a comparison is made to a Char type and the length of the
right-hand side of the expression does not match the char length, the
comparison always fails. I rarely use Char for this reason.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Thanks for your answers William!

William Vaughn said:
Ah, yes. When a comparison is made to a Char type and the length of the
right-hand side of the expression does not match the char length, the
comparison always fails. I rarely use Char for this reason.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
Click to expand...
 
Back
Top