select data from database, using c# parameter for it

Question

Please can you help me why my command for select dont select any data. I try change select from name to ID and it works but i need that this select work for name select not nly for ID.

Database data:

id      user      password      level
1       Rad3k       .....         0

Script for select:

public bool loginVerification(string name, string password)
{
    test = name;
    bool access = false;
    string checkLogin, checkPassword;
    int level;

    commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user like @name", conn);
    try
    {
        conn.Open();
        commandS.Parameters.Clear();

        // Parameters
        SqlParameter nameParam = new SqlParameter("@name", SqlDbType.VarChar, 30);
        nameParam.Value = name;
        commandS.Parameters.Add(nameParam);
        commandS.Prepare();
        SqlDataReader reader = commandS.ExecuteReader();

        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
                if (reader.IsDBNull(i))
                    return access = false;

            checkLogin = reader.GetString(1);
            checkPassword = reader.GetString(2);
            level = reader.GetInt32(3);

            if (name == checkLogin && hashPassword.ValidatePassword(password, checkPassword) == true)
                access = true;
        }

        return access;
    }
    finally
    {
        conn.Close();
    }
}

If i use this select so it works:

commandS = new SqlCommand("SELECT * FROM dbo.users WHERE id = 1", conn);

But when i use this so it dont work:

commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user like @name", conn);
I try use this without parameter, but it dont work too.
commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user like 'Rad3k'", conn);
commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user like '%Rad3k%'", conn);
commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user like 'Rad3k%'", conn);
commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user = 'Rad3k'", conn);
// and same with paramter still dont work

Thanks for all people which can help me.

Hans Keﬆing · Accepted Answer · 2014-03-27 09:57:08Z

1

If you use a plain SQL statement, you would use

SELECT * FROM dbo.users WHERE user like '%Rad3k%'

You need the % to match a substring, without them it would mean a full match (only user 'Rad3k' would be found). Note that you could also use like 'Rad3k%' for a "starts with" match.

From C# with parameters, it would be

commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user like '%' + @name + '%'", conn);

answered Mar 27, 2014 at 9:57

Hans Keﬆing

39.5k10 gold badges84 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raphaël Althaus · Accepted Answer · 2014-03-27 09:46:40Z

1

your command should be like this

SELECT * FROM dbo.users WHERE user like '%" + @name + "%'"

edited Mar 27, 2014 at 9:46

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

answered Mar 27, 2014 at 9:44

Ehsan

32.8k6 gold badges61 silver badges66 bronze badges

4 Comments

Raphaël Althaus Over a year ago

like '%" + @name + "%'" no ?

Raphaël Althaus Over a year ago

@Ehsan not at start of like ... ;) Anyway, just a detail !

Ehsan Over a year ago

@RaphaëlAlthaus i initially posted this thing. Then i edited incorrectly. And then i reverted.... Thanks.

Radek Tarant Over a year ago

Syntax and code is correctly and it works in DB but not from C# still find nothing.

Sai Avinash · Accepted Answer · 2014-03-27 09:49:55Z

0

You need use like operator when you are filtering against strings:

 SELECT * FROM dbo.users WHERE user like '%" + @name + "%'"

answered Mar 27, 2014 at 9:49

Sai Avinash

4,75117 gold badges63 silver badges100 bronze badges

Comments

Roshane Kekuluthotuwage · Accepted Answer · 2014-03-27 10:07:04Z

0

Try to set value to passing parameter using AddWidthValue :

SqlParameter nameParam = new SqlParameter("@name", SqlDbType.VarChar, 30);
commandS.Parameters.AddWidthValue("name", name);`

answered Mar 27, 2014 at 10:07

Roshane Kekuluthotuwage

6782 gold badges7 silver badges20 bronze badges

2 Comments

Radek Tarant Over a year ago

Problem is that, I try use select with parameter and without parameter and from C# it find nothing. only when i select id it work. I dont knwo why. I try this directly. select * from dbo.user where user like '%Rad3k%' - it find nothing from C# and when i try this so too nothing: commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user like '%' + @name + '%'", conn);

Roshane Kekuluthotuwage Over a year ago

edit your query and try commandS = new SqlCommand("SELECT * FROM dbo.users WHERE user like %@name", conn);

mh__ · Accepted Answer · 2014-03-27 10:08:47Z

0

Your query should work. You can use like operator without %. If you want an excact match on user you can just use user = @name.

Are you sure that you dont have any additional column in that row that are null? This code dismisses all rows with an null column:

            for (int i = 0; i < reader.FieldCount; i++)
            if (reader.IsDBNull(i))
                return access = false;

Try to comment out these lines and test your code.

answered Mar 27, 2014 at 10:08

mh__

3762 silver badges10 bronze badges

4 Comments

Radek Tarant Over a year ago

i think that this is not problem because now i have in table only one row with name Rad3k as i say. This code only for future for more datas. But i trying commented it.

mh__ Over a year ago

Yes, but the code above checks for columns with null. So of there are any columns in the 'Rad3k' row that are NULL, it will return false. I tested your code on my demo data and it worked fine.

Radek Tarant Over a year ago

i commented it, but i have still same problem. I dont know where i make fail

mh__ Over a year ago

Ok, try to run the query in SQL Server Management Studio and see if the query works there. Also try to debug your code to see where it fails?

Collectives™ on Stack Overflow

select data from database, using c# parameter for it

5 Answers 5

Comments

4 Comments

Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

4 Comments

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related