MySQL Procedure works in php but not in asp.net (C#)

I have built a website with a MySQL back-end with php as the server language, now i shall build the same site in asp.net

The following procedure is define in the MySQL database:

DELIMITER //
CREATE PROCEDURE Login(
    IN Username VARCHAR(16),
    IN UserPassword VARCHAR(16),
    OUT UID int,
    OUT SL INT,
    OUT SP VARCHAR(8),
    OUT MA BOOL)    -- missing admin entry
BEGIN
    DECLARE adminID INT DEFAULT -1;
    DECLARE lastTimeout DATETIME DEFAULT NULL;
    SET UID = -1;

    SELECT ID, SecurityLevel, LoginTimeout INTO UID, SL, lastTimeout
    FROM User
    WHERE User.UserName = Username and User.Password = UserPassword;

    IF NOW() > lastTimeout OR lastTimeOut IS NULL THEN
        IF lastTimeOut IS NOT NULL THEN
            INSERT INTO UserLog (UserID, Date, Action) VALUES (UID, NOW(), 'TIMEOUT');
        END IF;

        SELECT ID, Password INTO SL, SP
        FROM SecurityLevels
        WHERE ID = SL;

        SELECT UserID INTO adminID
        FROM Admin
        WHERE UserID = UID;

        IF adminID = -1 AND SL = 4 THEN SET MA = TRUE;
        ELSE SET MA = FALSE;
        END IF;

        IF UID != -1 THEN
            INSERT INTO UserLog (UserID, Date, Action) VALUES (UID, NOW(), 'LOGIN');
            UPDATE User SET User.LoginTimeOut = DATE_ADD(NOW(), INTERVAL 1 HOUR) WHERE User.ID = UID;
        END IF;
    ELSE
        SET UID = -1;
    END IF;

END //
DELIMITER ;

The user I use is:

CREATE USER 'LOGIN'@'%' IDENTIFIED BY '6Jd8kKi0';
GRANT execute ON procedure b09xxxxx.Login TO 'LOGIN'@'%';

Now, in php I do it like this, and it works like a charm:

$pdo = new PDO('mysql:dbname=b09xxxxx;host=wwwlab.xxx.xxx.se', 'LOGIN', '6Jd8kKi0');
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );

$userID = 0;
$securityLevel = 0;
$securityPassword = "";

//$sql = "SELECT UserName, ID, SecurityLevel FROM User WHERE UserName = '" . $_POST['username'] . "' and Password = '" . $_POST['password'] . "';";
$sql = "CALL Login(:USERNAME, :P ASSWORD, @UID, @SL, @SP, @MA);";

$stmt = $pdo->prepare($sql);
$stmt->bindParam(':USERNAME', $_POST['username']);
$stmt->bindParam(':PASSWORD', $_POST['password']);
$stmt->execute();

//$q     = $pdo->query($sql) or die("ERROR:DB");

$sql = "SELECT @UID, @SL, @SP, @MA;";

$q   = $pdo->query($sql) or die("ERROR:DB");

$r = $q->fetch(PDO::FETCH_ASSOC);

But this asp.net (C#) version does not work:

string connectionString = "Server=wwwlab.xxx.xxx.se; Database=b09xxxxx; User ID=LOGIN; Password=6Jd8kKi0; Pooling=false;";
MySqlConnection dbcon = new MySqlConnection(connectionString);
dbcon.Open();

//string query = "CALL Login(:USERNAME, :P ASSWORD, @UID, @SL, @SP, @MA);";
//Now changed to this thanks to John Woo
string query = "Login";

MySqlCommand sqlCmd = new MySqlCommand(query, dbcon);
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

sqlCmd.Parameters.AddWithValue(":USERNAME", loginUsername.Text);
sqlCmd.Parameters[":USERNAME"].Direction = ParameterDirection.Input;
sqlCmd.Parameters.AddWithValue(":PASSWORD", loginPassword.Text);
sqlCmd.Parameters[":PASSWORD"].Direction = ParameterDirection.Input;

sqlCmd.Parameters.Add(new MySqlParameter("@UID", MySqlDbType.Int64));
sqlCmd.Parameters["@UID"].Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(new MySqlParameter("@SL", MySqlDbType.Int64));
sqlCmd.Parameters["@SL"].Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(new MySqlParameter("@SP", MySqlDbType.String));
sqlCmd.Parameters["@SP"].Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add(new MySqlParameter("@MA", MySqlDbType.Byte));
sqlCmd.Parameters["@MA"].Direction = ParameterDirection.Output;

sqlCmd.ExecuteNonQuery();

MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT @UID, @SL, @SP, @MA;", dbcon);
DataSet ds = new DataSet();
adapter.Fill(ds, "result");

CustomerGrid.DataSource = ds.Tables["result"];
CustomerGrid.DataBind();               

dbcon.Close();http://stackoverflow.com/editing-help

I get the following error:

Procedure or function ‘CALL Login(:USERNAME, :P ASSWORD, @UID, @SL, @SP, @MA)‘ cannot be found in database ‘b09xxxxx‘.

Edit: now this is the new problem: SELECT command denied to user ‘LOGIN’@’193.11.99.23′ for table ‘proc’

Leave a Reply

*

Hire Me
Follow Me!
Search
Most Popular Articles & Pages
Because your vote is Important
Sorry, there are no polls available at the moment.
Categories