Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > SQL Query

Reply
Thread Tools

SQL Query

 
 
Uriah Piddle
Guest
Posts: n/a
 
      07-07-2007
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 With Quote
 
 
 
 
mark4asp
Guest
Posts: n/a
 
      07-07-2007
On Fri, 6 Jul 2007 19:56:22 -0500, "Uriah Piddle"
<(E-Mail Removed)> wrote:

>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


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 With Quote
 
 
 
 
Riki
Guest
Posts: n/a
 
      07-07-2007
Uriah Piddle wrote:
> 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;

--

Riki


 
Reply With Quote
 
Uriah Piddle
Guest
Posts: n/a
 
      07-07-2007

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.







"mark4asp" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Fri, 6 Jul 2007 19:56:22 -0500, "Uriah Piddle"
> <(E-Mail Removed)> wrote:
>
>>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

>
> 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 With Quote
 
Uriah Piddle
Guest
Posts: n/a
 
      07-07-2007
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 With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
What Happened to Sql Enterprise Manager and Sql Query Analyser in Visual Studio 2005 Edward ASP .Net 4 04-10-2006 04:15 PM
Trying to query the Address table data of AdventureWorks database from Query Analyzer - need help! Learner ASP .Net 1 01-30-2006 08:58 PM
Build dynamic sql query for JSTL <sql:query> Anonymous Java 0 10-13-2005 10:01 PM



Advertisments