OleDbDataReader/OleDbCommand missing req param

[fizzled]

The following code produces an error on the line:

 

rdrLogin = cmdLogin.ExecuteReader(CommandBehavior.CloseConnection);

 

The error description is 'No value given for one or more required parameters.' I don't understand what more these objects need to perform their duties. Does anyone know what I am missing?

 

public void cmdSubmitLogin_Click (Object Sender, EventArgs e) {
 int iLoginOk = 0;
 String strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("fizzdaoc.mdb");
 OleDbConnection cnLogin = new OleDbConnection(strConnString);
 OleDbCommand cmdLogin = new OleDbCommand();
 OleDbDataReader rdrLogin;
 OleDbParameter paramUsername = new OleDbParameter("Username", OleDbType.VarChar);
 OleDbParameter paramUserpass = new OleDbParameter("Userpass", OleDbType.VarChar);

 if ((txtLoginUN.Text.Length > 0) && (txtLoginPW.Text.Length > 0)) {
   cnLogin.Open();

   paramUsername.Value = txtLoginUN.Text;
   paramUserpass.Value = txtLoginPW.Text;

   cmdLogin.Connection = cnLogin;
   cmdLogin.CommandText = "SELECT UserID, PrivEmail, PubEmail, UserLevel FROM Users WHERE Username = ? AND Userpass = ?";
   cmdLogin.Parameters.Add(paramUsername);
   cmdLogin.Parameters.Add(paramUserpass);
   cmdLogin.CommandType = CommandType.Text;
   [color=red]rdrLogin = cmdLogin.ExecuteReader(CommandBehavior.CloseConnection);[/color]

   if (rdrLogin.HasRows) {
     // Login successful
     rdrLogin.Read();

     Session["UserID"] = rdrLogin.GetValue(0);
     Session["LoginUN"] = txtLoginUN.Text;
     Session["PrivateEmail"] = rdrLogin.GetValue(3);
     Session["PublicEmail"] = rdrLogin.GetValue(4);
     Session["UserLevel"] = rdrLogin.GetValue(5);

     iLoginOk = 1;
   } else {
     // No record found for username/password combination
     lblLoginError.Text = "Login failed. Incorrect Username and/or Password.";
   }

   rdrLogin.Close();
   // Connection implicitly closed by CommandBehavior.CloseConnection
 } else {
   // No username or password entered
   lblLoginError.Text = "Login failed. You must supply both a Username and Password.";
 }

 if (iLoginOk > 0) {
   Server.Transfer("LogoutForm.ascx");
 }
}

[Derek Stone]

You add the parameters, but you don't assign the parameter values.

command.Parameters.Add(@"socialsecuritynumber", OleDbType.VarWChar).Value = socialSecurityNumber;

[fizzled]

You add the parameters, but you don't assign the parameter values.

command.Parameters.Add(@"socialsecuritynumber", OleDbType.VarWChar).Value = socialSecurityNumber;

 

So:

 

 paramUsername.Value = txtLoginUN.Text;
 paramUserpass.Value = txtLoginPW.Text;

 ...

 cmdLogin.Parameters.Add(paramUsername);
 cmdLogin.Parameters.Add(paramUserpass);

 

does not achieve the same thing? Can I just delete the first two lines there?

[Derek Stone]

While I misread your code when I replied the last time, the code I posted still applies. What your code does now is create parameters that aren't attached to the command. You need to use the OleDbCommand.Parameters.Add() method to create the parameters. You can then assign the parameter values (or use the code I posted which does the same thing, but on one line).

 

On a personal note, I fell victim to this very same problem when I first started using the System.Data.OleDb namespace. So don't feel bad. :)