How to pass parameters to an object query?

Question

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.

If you want to use your saved Access parameter query in your C# application you'll need to use System.Data.OleDb, not .Odbc (as per my answer). The Access ODBC driver does not treat those queries as Stored Procedures like the OLEDB provider does. — Gord Thompson
– Gord Thompson, Commented Apr 1, 2015 at 12:25

Gord Thompson · Accepted Answer · 2015-04-01 12:23:02Z

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.");
            }
        }
    }
}

I noticed that even if I change the parameters name in c# code and name them different that names in qryLogin it still accepts these parameters as the two inputs

Collectives™ on Stack Overflow

How to pass parameters to an object query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related