no value given for one or more required parameters/ SQL should be correct

Question

The program stops at the adapter.Fill(datatable)line giving the error: oleDbException was Unhandled: No value given for one or more required parameters.

When I copy the SQL into access no errors are given..

The SQL:

public static List<Movie> AllMoviesSameActor(string lastName)
{
   string command;
   command = "SELECT movieId, name, headActor, Director, ReleaseDate, lenghtMin"
           + "FROM Movies" +
            " WHERE headactor =  " + lastName ;
   OleDbDataAdapter adapter = new OleDbDataAdapter(command, connectionString);
   DataTable datatable = new DataTable();
   adapter.Fill(datatable);

   List<Movie> list= new List<Movie>();
   for (int i = 0; i < datatable.Rows.Count; i++)
   {
      Movie movie = new Movie();
      movie.movieId = datatable.Rows[i].Field<int>("movieId");
      movie.name = datatable.Rows[i].Field<string>("name");
      movie.headActor = datatable.Rows[i].Field<string>("headActor");
      movie.director = datatable.Rows[i].Field<string>("director");
      movie.releaseDate = datatable.Rows[i].Field<DateTime>("releaseDate");
      movie.lenghtMin = datatable.Rows[i].Field<int>("lenghtMin");
      list.Add(movie);
   }   
   return list;
}

The Code from the form:

public FormMoviesSameActor()
{
   InitializeComponent();
   List<Actor> actors = Actor.AllActors();
   comboBoxActeur.DataSource = actors;
   comboBoxActeur.DisplayMember = "LastName";
   comboBoxActeur.ValueMember = "LastName";
}

private void buttonSearchMovies_Click(object sender, EventArgs e)
{
   Actor dedicatedActor = new Actor();
   dedicatedActor.LastName = Convert.ToString(comboBoxActeur.SelectedValue);
   listBox1.DataSource = dedicatedActor.Movies;
   listBox1.DisplayMember = "AllData";
}

The Actor.Movies method is described below

public List<Movie> Movies
{
   get { return Movie.AllMoviesSameActor(this.lastName); 
}

Steve · Answer 1 · 2013-12-23T23:45:48.997

When you receive this error it means that the Access Engine cannot correctly find one or more of your columns names or the whole table name.

So, the first step, is to be sure that the names of the columns are correct.
In particular that minLenght seems wrong (minLength ???)

Also the string concatenation contains an error. Missing a space before the FROM clause, but this should throw a syntax error, not a missing parameters.

command = "SELECT movieId, name, headActor, Director, ReleaseDate, lenghtMin " +  // space added
          "FROM Movies WHERE headactor =  " + lastName ;

and, finally, the headactor field seems to be a text field and, if this is true, you need to use single quotes around the lastName variable. But I recommend to use a parameterized query instead

 command = "SELECT movieId, name, headActor, Director, ReleaseDate, lenghtMin " +
          "FROM Movies WHERE headactor =  ?";

 OleDbDataAdapter adapter = new OleDbDataAdapter(command, connectionString);
 adapter.SelectCommand.Parameters.AddWithValue("@p1", lastName);
 .....

score 5 · Accepted Answer · edited May 23 '17 at 11:53

You've got a couple of issues. Use this:

command = "SELECT movieId, name, headActor, Director, ReleaseDate, lenghtMin"
        + " FROM Movies" +
        " WHERE headActor =  '" + lastName + "'";

Basically you were missing a space between your FROM and last column. You were also missing the string qualifiers around your lastName.

You shouldn't do this though if you are doing something for production. You should look into using parameterized queries.

How do parameterized queries help against SQL injection?

As Brian pointed out, your column names also do not match what you are trying retrieve from the Data Row Fields. You will want to correct this with the correct names.

Adding the string qualifiers around lastName did the trick. This is for a school project for our first semester programming class, the professor said this was a bad method but we need to learn the basics first. — user3127227, Dec 23 '13 at 23:47

score 4 · Answer 3 · answered Dec 23 '13 at 23:37

Change:

command = "SELECT movieId, name, headActor, Director, ReleaseDate, lenghtMin"
          + "FROM Movies" + " WHERE headactor =  " + lastName ;

To:

command = "SELECT movieId, name, headActor, director, releaseDate, lenghtMin"
          + " FROM Movies" + " WHERE headactor =  " + lastName ;

Your column names don't match what is is your original query.

Movie movie = new Movie();
movie.movieId = datatable.Rows[i].Field<int>("movieId");
movie.name = datatable.Rows[i].Field<string>("name");
movie.headActor = datatable.Rows[i].Field<string>("headActor");
movie.director = datatable.Rows[i].Field<string>("director");  // Doesn't match the query
movie.releaseDate = datatable.Rows[i].Field<DateTime>("releaseDate");  // Doesn't match the query
movie.lenghtMin = datatable.Rows[i].Field<int>("lenghtMin");
list.Add(movie);

Good catch on the column names not matching the case. – TyCobb Dec 23 '13 at 23:42 — TyCobb, Dec 23 '13 at 23:42

score 0 · Answer 4 · edited Mar 04 '16 at 05:13

0

If lastName is a variable, write:

command = "SELECT movieId, name, headActor, Director, ReleaseDate, lenghtMin FROM Movies WHERE headactor = '" + LastName + '";

edited Mar 04 '16 at 05:13

answered Mar 04 '16 at 04:47

Rehman Khalid

1
1

AliMusa · Answer 5 · 2016-12-14T11:04:44.113

If you got a same error for "insert" query you can use this method for avoid exception

string sqlQuery = "INSERT into EndResultOfTestCases(IDsOfCases,TestCaseName,ResultCase,ResultLog) VALUES(@ids, @casename, @results, @logs)";

        connection = new OleDbConnection(connectionStringToDB);
        command = new OleDbCommand(sqlQuery, connection);
        command.Parameters.AddWithValue("@ids",IDs);
        command.Parameters.AddWithValue("@casename", CaseName);
        command.Parameters.AddWithValue("@results", resultOfCase);
        command.Parameters.AddWithValue("@logs", logs);
        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }

no value given for one or more required parameters/ SQL should be correct

5 Answers5