Parameter Problem while running sql query in C#

Question

Error :

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects

This is the error I'm getting when I run this code

MenuItem masterItem = new MenuItem((string)masterRow["Parentitem"]);
string mp =(string) masterItem.Value;

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@mp";

parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = mp;

string q = "select aspnet_PersonalizationPerUser.hasRights from Menu, aspnet_Users, 
            aspnet_Paths, aspnet_PersonalizationPerUser 
            where Menu.Parentitem = @mp 
            and Menu.Url = aspnet_Paths.Path 
            and aspnet_Paths.PathId = aspnet_PersonalizationPerUser.PathId 
            and aspnet_Users.UserName ='admin' 
            and aspnet_PersonalizationPerUser.UserId = aspnet_Users.userId ";

SqlCommand cm = new SqlCommand(q, conn);
cm.Parameters.Add(mp);
string b = (string)cm.ExecuteScalar();

I'm getting the exception when I'm adding parameter to the command.. Can u let me know the mistake..

Free tip of the day: I would try to avoid the legacy-style JOIN's by just having from Menu, aspnet_Users, aspnet_Paths, aspnet_PersonalizationPerUser - this is very dangerous, if you ever miss one JOIN condition in the WHERE clause, you end up with a cartesian product! Also: your actual WHERE clause gets cluttered up with unnecessary conditions that really only serve as JOIN conditions..... — marc_s
– marc_s, Commented Aug 15, 2011 at 20:32
Instead, use the ANSI standard JOIN syntax which clearly marks the type of JOIN and has the JOIN condition right there where it belongs - much easier to read! (also: use table aliases - they make your queries much easier to read, too!): FROM dbo.Menu m INNER JOIN dbo.aspnet_Paths p ON m.Url = p.Path INNER JOIN dbo.aspnet_PersonalizationPerUser ppu ON p.PathId = ppu.PathId INNER JOIN dbo.aspnet_Users usr ON ppu.UserId = u.userId WHERE Menu.Parentitem = @mp AND aspnet_Users.UserName = 'admin' — marc_s
– marc_s, Commented Aug 15, 2011 at 20:33

Rich Turner · Accepted Answer · 2011-08-15 19:33:17Z

3

Change this:

cm.Parameters.Add(mp);

to this:

cm.Parameters.Add(parameter);

answered Aug 15, 2011 at 19:33

Rich Turner

11.1k1 gold badge55 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

Parameter Problem while running sql query in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related