SQL Query

[Uriah Piddle]

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

 

[mark4asp]

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

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.

 

[Riki]

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.

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

 

[Uriah Piddle]

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.

 

[Uriah Piddle]

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.