private void GetData(string filmTitle)
{
try
{
SQL = "SELECT actor.`first_name`,actor.`last_name`" + Environment.NewLine +
"FROM actor" + Environment.NewLine +
"Left JOIN film_actor ON actor.`actor_id`= film_actor.`actor_id`" + Environment.NewLine +
"Left JOIN film ON film_actor.`film_id`= film.`film_id`" + Environment.NewLine +
"WHERE film.`title`=" + My.Quote(filmTitle);
cmd.CommandText = SQL;
reader = cmd.ExecuteReader();
dtActors.Rows.Clear();
dtActors.Load(reader);
dgvActors.DataSource = dtActors;
lblActors.Text = "Actors Found: " + dtActors.Rows.Count.ToString();
if (reader != null)
{
reader.Close();
}
}
catch (Exception exc)
{
MessageBox.Show(exc.ToString());
}
}
private void ShowFilms()
{
int categoryID;
string sWHERE = string.Empty;
try
{
SQL = "SELECT" + Environment.NewLine +
"`film`.`film_id` AS `FID`," + Environment.NewLine +
"`film`.`title` AS `title`," + Environment.NewLine +
"`category`.`name` AS `category`," + Environment.NewLine +
"`film`.`length` AS `length`," + Environment.NewLine +
"`film`.`rating` AS `rating`," + Environment.NewLine +
"`film`.`rental_rate` AS `price`," + Environment.NewLine +
"film.`special_features` AS special_features," + Environment.NewLine +
"film.`rental_duration` AS rental_duration," + Environment.NewLine +
"film.`replacement_cost` AS replacement_cost," + Environment.NewLine +
"`film`.`description` AS `description`" + Environment.NewLine +
"FROM category" + Environment.NewLine +
"LEFT JOIN film_category ON category.`category_id`= film_category.`category_id`" + Environment.NewLine +
"LEFT JOIN film ON film_category.`film_id`= film.`film_id`" + Environment.NewLine;
sWHERE = " Where film.`title` LIKE " + My.Quote(txtMovieName.Text + "%");
if (cbxCategories.SelectedIndex != -1)
{
CategoryID a = (CategoryID)cbxCategories.SelectedItem;
categoryID = Convert.ToInt32(a.id);
sWHERE += " AND category.`category_id`=" + categoryID;
}
if (txtMovieName.Text != string.Empty || cbxCategories.SelectedIndex != -1)
{
SQL += Environment.NewLine + sWHERE;
}
cmd.CommandText = SQL;
reader = cmd.ExecuteReader();
tblMovies.Rows.Clear();
tblMovies.Load(reader);
dvgMovies.DataSource = tblMovies;
lblMovies.Text = "Movies Found: " + tblMovies.Rows.Count.ToString();
if (reader != null)
{
reader.Close();
}
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
finally
{
//reader?.Close();
}
}