CODEWITHSUNDEEP
c#sql-server
Glen
Glen

Reputation: 99

Query works without parameters but not with parameters

Now this is by far the weirdest thing I've encountered while working with MSSQL.

I've got a table called User, it has one row in it that has "admin" in the username field, when I run the following query, it doesn't give me any results:

DECLARE @uname varchar;

SET @uname = 'admin'

SELECT * FROM [User] WHERE UserName = @uname

But when I run this, I get expected results:

SELECT * FROM [User] WHERE UserName = 'admin'

And this is the table definition:

CREATE TABLE [dbo].[User](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [FirstName] [nvarchar](256) NULL,
    [LastName] [nvarchar](256) NULL,
    [Email] [nvarchar](512) NULL,
    [Password] [binary](64) NULL,
    [GroupID] [int] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ__Users__C9F284563A4F773F] UNIQUE NONCLUSTERED 
(
    [UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And I access this database in C# application, so when I pass the username in a SqlParameter, it simply gives me no results.

And here is a sample C# code:

string query = "SELECT COUNT(*) FROM [User] WHERE UserName = @uname";

using (var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    using (var cmd = new SqlCommand(query, connection))
    {
        cmd.Parameters.Add(new SqlParameter("uname", "admin"));

        int rowCount = (int)cmd.ExecuteScalar();
    }
}

I get no exceptions.

Any ideas?

Upvotes: 3

Views: 721

Answers (2)

Mohd Salman
Mohd Salman

Reputation: 11

Write 

DECLARE @uname nvarchar(256);

SET @uname = 'admin'

SELECT * FROM [User] WHERE UserName = @uname

and C# code

string query = "SELECT COUNT(*) FROM [User] WHERE UserName = @uname";

using (var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    using (var cmd = new SqlCommand(query, connection))
    {
        cmd.Parameters.Add(new SqlParameter("uname", "@admin"));

        int rowCount = (int)cmd.ExecuteScalar();
    }
}

You have used UserName nvarchar(256) during table creation . So It's better to use same data type & size

Upvotes: 1

connectedsoftware
connectedsoftware

Reputation: 7087

When you define your string variable you haven't specified a size so it defaults to length 1.

Run this to illustrate: 

 DECLARE @uname varchar;

 SET @uname = 'admin'

 SELECT @uname

You will see the result is 'a' and not 'admin'

Change your definition to:

 DECLARE @uname varchar(100)

Also note, as per the comment by Nikola, there is an @ missing from your C# code:

 cmd.Parameters.Add(new SqlParameter("@uname", "admin"));

Upvotes: 4

Related Questions