OleDbDataReader/OleDbCommand missing req param

F

fizzled

Well-known member
Joined
Aug 9, 2004
Messages
52
Location
Atlanta, GA, USA
The following code produces an error on the line:

Code: 
rdrLogin = cmdLogin.ExecuteReader(CommandBehavior.CloseConnection);

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

Code: 
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");
  }
}
 
You add the parameters, but you dont assign the parameter values.
C#: 
command.Parameters.Add(@"socialsecuritynumber", OleDbType.VarWChar).Value = socialSecurityNumber;
 
Derek Stone said:
You add the parameters, but you dont assign the parameter values.
C#: 
command.Parameters.Add(@"socialsecuritynumber", OleDbType.VarWChar).Value = socialSecurityNumber;
Click to expand...

So:

C#: 
  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?
 
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 arent 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 dont feel bad. :)
 
Back
Top