SQL Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Uriah Piddle - 07 Jul 2007 01:56 GMT

Hi Gang,

In sql Server 2005, I am running a query with an nvarchar param in the WHERE
clause like this:
. . .
@Bar nvarchar
. . .
Select * From Foo WHERE Foo.Bar = @Bar

The problem: this query is returning nothing when I KNOW that there is a
record that matches.

I send the query in an SQLCommand object like this:

SQLCommand cmd = new SQLCommand("GetLicenses", cn)
cmd.CommandType = CommandType.StoredProcedure;
string bar = "Admin";
cmd.Parameters.Add("@Bar", SqlDBType.NVarChar).Value = bar;

This query runs correctly when I change it to query on an Int column so the
code itself works. But querying on the nvarchar column with the string just
does not work.

Thanks for any ideas.

Steve

Reply to this Message

mark4asp - 07 Jul 2007 09:07 GMT

>Hi Gang,
>
[quoted text clipped - 22 lines]
>
>Steve

news:microsoft.public.sqlserver.programming

What happens when you run the query from the SQL Server Management
utility? With something like:

exec GetLicenses 'Admin'

- does that work. Your explanation is not clear here. (You didn't say
whether that works or not) What happens when you replace = with a LIKE
operator:

Select * From Foo WHERE Foo.Bar LIKE '%Admin%'

Start with something that works in the SQL Server Management utility.
For instance it could be that the record has been entered with trailing
spaces in that field.

I always assume that when my SPROC isn't running it's my SQL code which
is at fault rather than my ASP.NET so I debug my SQL first. That's
always worked for me.

Reply to this Message

Uriah Piddle - 07 Jul 2007 15:30 GMT

Mark:

This works: *** Select * From Foo WHERE Foo.Bar LIKE '%Admin%' ***

This also works: *** Select * From Foo WHERE Foo.Bar = '%Admin%' ***

So the problem is in passing the param. I've been trying different things
but as of now nothing works. But I have the thing narrowed down pretty well.
Thanks.

>>Hi Gang,
>>
[quoted text clipped - 46 lines]
> is at fault rather than my ASP.NET so I debug my SQL first. That's
> always worked for me.

Reply to this Message

Uriah Piddle - 07 Jul 2007 15:48 GMT

Its fixed. I was not setting the ''Size' prop of the nvarchar param in the
C# code to agree with the column. If I do this it works:

cmd.Parameters.Add("UserName", SqlDbType.NVarChar, 256).Value = userName;

Thanks again to you and Riki.

Reply to this Message

Riki - 07 Jul 2007 09:59 GMT

> Hi Gang,
>
[quoted text clipped - 18 lines]
> so the code itself works. But querying on the nvarchar column with
> the string just does not work.

Try
cmd.Parameters.Add("@Bar", SqlDBType.NVarChar,bar);
instead of
cmd.Parameters.Add("@Bar", SqlDBType.NVarChar).Value = bar;

Signature

Riki

Reply to this Message

Rate this thread:

SQL Query

Free Magazines