CODEWITHSUNDEEP
c#ms-accessodbc
Wennie Valles Sasotona
Wennie Valles Sasotona

Reputation: 89

How to pass parameters to an object query?

I have an Object Query in MS Access (getUserLogin) that will execute the following:

PARAMETERS prmUsername Text, prmPassword Text;

SELECT ID, LastName, FirstName, MiddleName
FROM tblUsers
WHERE Username = [prmUsername] AND Password = [prmPassword];

I have a method in my C# to execute the Object Query in MS Access (getUserLogin).

public bool login(string username, string password)
{
    com = new OdbcCommand("EXEC getUserLogin", con);

    com.CommandType = CommandType.StoredProcedure;

    com.Parameters.Add("prmUsername", OdbcType.Text).Value = username;
    com.Parameters.Add("prmPassword", OdbcType.Text).Value = password;

    con.Open();

    rea = com.ExecuteReader(); //OdbcException goes here

    if (rea.HasRows == true)
        return true;
    else
        return false;
}

I'm getting this OdbcException:

ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

Upvotes: 2

Views: 1205

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123419

Your first problem is that your query uses parameter names that are the same as the corresponding field names. If you try to run your query in Access it will prompt you for the "Username" and "Password" parameters and then return all rows in tblUsers regardless of the parameter values you enter. That is because when the Access SQL parser processes

... WHERE Username = [Username] AND Password = [Password]

... it interprets [Username] and [Password] as field names, not parameter names, and the result is the same as

... WHERE True AND True

So, the first thing you need to do is change your parameter names. One relatively common convention is to use a "prm" prefix for parameter names, so your query would be

PARAMETERS prmUsername Text, prmPassword Text;
SELECT [ID], [LastName], [FirstName], [MiddleName]
FROM [tblUsers]
WHERE [Username] = [prmUsername] AND [Password] = [prmPassword];

Now, to pass your parameter values in your C# application you need to use System.Data.OleDb (not .Odbc) with code something like this

using (var con = new OleDbConnection(myConnectionString))
{
    con.Open();
    using (var cmd = new OleDbCommand())
    {
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "loginQuery";
        cmd.Parameters.Add("prmUsername", OleDbType.VarWChar).Value = "eric";
        cmd.Parameters.Add("prmPassword", OleDbType.VarWChar).Value = "abcdefg";
        using (OleDbDataReader rdr = cmd.ExecuteReader())
        {
            if (rdr.Read())
            {
                Console.WriteLine("Row found: ID = {0}", rdr["ID"]);
            }
            else
            {
                Console.WriteLine("Row not found.");
            }
        }
    }
}

Upvotes: 2

Related Questions