.net mysql参数化查询,在MySQL ASP.NET参数化查询

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";

解决方案

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);