SQL Query

Discussion in 'ASP .Net' started by Uriah Piddle, Jul 7, 2007.

  1. Uriah Piddle

    Uriah Piddle Guest

    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
     
    Uriah Piddle, Jul 7, 2007
    #1
    1. Advertising

  2. Uriah Piddle

    mark4asp Guest

    On Fri, 6 Jul 2007 19:56:22 -0500, "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.
    >
    >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.
     
    mark4asp, Jul 7, 2007
    #2
    1. Advertising

  3. Uriah Piddle

    Riki Guest

    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
     
    Riki, Jul 7, 2007
    #3
  4. Uriah Piddle

    Uriah Piddle Guest

    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" <> wrote in message
    news:...
    > On Fri, 6 Jul 2007 19:56:22 -0500, "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.
    >>
    >>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.
     
    Uriah Piddle, Jul 7, 2007
    #4
  5. Uriah Piddle

    Uriah Piddle Guest

    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.
     
    Uriah Piddle, Jul 7, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Learner
    Replies:
    1
    Views:
    1,004
    Marina Levit [MVP]
    Jan 30, 2006
  2. Edward
    Replies:
    4
    Views:
    4,626
    William \(Bill\) Vaughn
    Apr 10, 2006
  3. Anonymous
    Replies:
    0
    Views:
    1,516
    Anonymous
    Oct 13, 2005
  4. David Gordon

    xpath query query

    David Gordon, May 18, 2005, in forum: XML
    Replies:
    2
    Views:
    806
    David Gordon
    May 18, 2005
  5. ecoolone
    Replies:
    0
    Views:
    783
    ecoolone
    Jan 3, 2008
Loading...

Share This Page