mysql parameterized query in ASP.NET

Question

i am working on parameterized queries but i am not getting proper query in result

here is my code

   public MySqlCommand Get_Login(string clinetID, string loginID, string password, string branchID)
    {
        MySqlCommand objCommand = new MySqlCommand(this.Query);


        objCommand.Parameters.AddWithValue("@ClientID", clinetID);
        objCommand.Parameters.AddWithValue("@LoginID", loginID);
        objCommand.Parameters.AddWithValue("@Password", password);
        objCommand.Parameters.AddWithValue("@BranchID", branchID);

        objCommand.CommandType = CommandType.Text;
        return objCommand;
    }

and when debugging this is what i am getting in "objCommand"

 Select u.groupid,p.PersonId, p.designationid,concat(p.salutation,p.FName,'
',p.MName,' ',p.LName) as PersonName,tb.Type
 BrType,p.OrgId,p.subdepartmentid,ifnull(crossdept,'N') as
 crossdept,p.departmentid,u.defaultpage,p.orgid,ifnull(p.crosslab,'N') as crosslab,
 (select indoor_services from dc_Tp_organization where orgid='@ClientID') as
 indoor_services,(select name from dc_Tp_organization where orgid='@ClientID') as 
 orgname,
 (select default_route from dc_Tp_organization where orgid='@ClientID') as
 default_route,p.BranchID BranchID,tb.Name BRName from dc_tp_personnel p left outer
  join
 dc_tu_userright u on u.personid=p.personid left outer join dc_tp_branch tb on
 tb.BranchID=p.BranchID Where p.Active='Y' and p.LoginId = '@LoginID' and p.Pasword
  ='@Password' and p.BranchID='@BranchID'

i am not getting values in parameters

Here is the Query

objdbhims.Query = "Select u.groupid,p.PersonId,
p.designationid,concat(p.salutation,p.FName,' ',p.MName,' ',p.LName) as 
PersonName,tb.Type BrType,p.OrgId,p.subdepartmentid,ifnull(crossdept,'N') as 
crossdept,p.departmentid,u.defaultpage,p.orgid,ifnull(p.crosslab,'N') as crosslab,
(select indoor_services from dc_Tp_organization where orgid=@ClientID) as
indoor_services,(select name from dc_Tp_organization where orgid=@ClientID) as
orgname,(select default_route from dc_Tp_organization where orgid=@ClientID) as
 default_route,p.BranchID BranchID,tb.Name BRName from dc_tp_personnel p left outer
join dc_tu_userright u on u.personid=p.personid left outer join dc_tp_branch tb on
tb.BranchID=p.BranchID Where p.Active='Y' and p.LoginId = @LoginID and p.Pasword
=@Password and p.BranchID=@BranchID";

public string Query { get { return StrQuery; } set { StrQuery = value; } } — AddyProg
– AddyProg, Commented Dec 11, 2013 at 13:32
its basically getting Query from another class and than adding parameters to the command Object in this class — AddyProg
– AddyProg, Commented Dec 11, 2013 at 13:33
ok, check this link the reason is because you have used the wrong identifier for MySQL should be ? not @ — Squirrel5853
– Squirrel5853, Commented Dec 11, 2013 at 13:41

DRapp · Accepted Answer · 2013-12-11 14:19:18Z

5

Secret Squirrel was correct on using the "?" for parameterized variables. MySQL uses "@" for inline sql variables for queries and thus expecting them to be declared such as from a script or part of an inline (select subquery) declaration.

You need to change BOTH instances of the parameters... both in the query, and as the command.Parameters.Add... instances.

Also, I noticed, and not sure if its it or not, but in your WHERE clause you have "pasword" (only one 's') vs password (two 's') Don't know if intentional or not.

One LAST thing that MAY help. Since some of the parameters match the column names, I would suggest changing the parameters SLIGHTLY by just adding something like "x" to FORCE differentiation between the column name and the actual parameters...

where... p.LoginID = ?xLoginID ...

and in the command parameters

objCommand.Parameters.AddWithValue("?xLoginID", loginID);

edited Dec 11, 2013 at 14:19

answered Dec 11, 2013 at 13:59

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

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

Comments

John Woo · Accepted Answer · 2013-12-11 13:24:27Z

2

The problem is because the parameters were wrap with single quotes converting them into string literals.

To make it work, remove the single quotes around them. eg.

Where p.Active = 'Y' 
      and p.LoginId = @LoginID 
      and p.Pasword = @Password
      and p.BranchID = @BranchID

answered Dec 11, 2013 at 13:24

John Woo

265k70 gold badges509 silver badges500 bronze badges

4 Comments

Squirrel5853 Over a year ago

how does this help him?

AddyProg Over a year ago

p.Active='Y' and p.LoginId = @LoginID and p.Pasword =@Password and p.BranchID=@BranchID

John Woo Over a year ago

@SecretSquirrel what do you mean how does it help him? try replicating the problem and try quoting the parameters. let's if the Command object can find your parameters.

Squirrel5853 Over a year ago

@今草顿웃 The issue must be with the input, changing the output is neither helpful or useful. Yes it is obvious that the variables within SQL should not be contained within quotes. But the issue is how the query was generated.

Collectives™ on Stack Overflow

mysql parameterized query in ASP.NET

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related