Populating dataGridView with a Data Set

Question

I am attempting to create a recipe database which takes ingredients from a user and outputs recipes which contain said ingredients. I am attempting to fill a data grid view with results from an sql statement but I am not getting any results inside my grid. My SQL statement is correct. Here is my code:

resultsWindow:

        private void resultsWindow_Load(object sender, EventArgs e)
    {
        //get connection string
        string connectionString = Properties.Settings.Default.ConnectionString;

        DataSet recipeDataSet = new DataSet();
        conn = new DatabaseConnections(connectionString);

        //Get dataset
        recipeDataSet = conn.getRecipes(ingredientArray);

        //Display data in grid view
        recipesDataGrid.DataSource = recipeDataSet.Tables[0];
    }

Database Connection Window:

        public DataSet getRecipes(string[] ingArray)
    {
        string sqlString = "SELECT recipes.Name, Instructions, recipes.Preperation_Time, Author FROM RecipeIngredients" +
                           " INNER JOIN recipes ON recipes.Recipe_ID = RecipeIngredients.Recipe_ID" +
                           " INNER JOIN Ingredients ON Ingredients.Ingredient_ID = RecipeIngredients.Ingredient_ID" +
                           " WHERE ingredients.Name = 'Eggs'";

        DataSet recipeDataSet = new DataSet();
        DataTable recipeDataTable = new DataTable();

        openConnection();

        dataAdapter = new SqlDataAdapter(sqlString, connectionToDB);

        //Fill dataset
        dataAdapter.Fill(recipeDataTable);
        recipeDataSet.Tables.Add(recipeDataTable);
        dataAdapter.Fill(recipeDataSet);

        closeConnection();

        return recipeDataSet;

    }

This is the data grid once I run the program

Thanks in advance.

EDIT: I've realised that it was not my data table/set that is not working, but my SQL statement doesn't seem to be returning anything, even though when I put it as a separate query I get results.

Answer 1

just try .. i'm not sure about this

 private void resultsWindow_Load(object sender, EventArgs e)
    {
        //gets connection string
        string connectionString = Properties.Settings.Default.ConnectionString;

        DataSet recipeDataSet = new DataSet();
        conn = new DatabaseConnections(connectionString);

        //Gets dataset
        Datatable dt1 = conn.getRecipes(ingredientArray);

        //Displays data in grid view
    recipesDataGrid.DataSource = dt1.DefaultView;


    }


   public DataTable getRecipes(string[] ingArray)
{
    string sqlString = "SELECT recipes.Name, Instructions, recipes.Preperation_Time, Author FROM RecipeIngredients" +
                       " INNER JOIN recipes ON recipes.Recipe_ID = RecipeIngredients.Recipe_ID" +
                       " INNER JOIN Ingredients ON Ingredients.Ingredient_ID = RecipeIngredients.Ingredient_ID" +
                       " WHERE ingredients.Name = 'Eggs'";

    DataTable recipeDataTable = new DataTable();

    openConnection();

    dataAdapter = new SqlDataAdapter(sqlString, connectionToDB);

    //Fills dataset
    dataAdapter.Fill(recipeDataTable);
    closeConnection();

    return recipeDataTable;

Answer 2

The issue here is that you have filled a table, added it to an untyped dataset, then filled the dataset separately, which should actually result in multiple tables within your dataset.
Either fill the table, OR fill the dataset, not both. I usually just fill the dataset.

Summary from IDbAdapter.Fill(DataSet):
Adds or updates rows in the System.Data.DataSet to match those in the data source using the System.Data.DataSet name, and creates a System.Data.DataTable named "Table".

Try first to reduce your code to this:

public DataSet getRecipes(string[] ingArray)
{
    string sqlString = "SELECT recipes.Name, Instructions, recipes.Preperation_Time, Author FROM RecipeIngredients" +
                       " INNER JOIN recipes ON recipes.Recipe_ID = RecipeIngredients.Recipe_ID" +
                       " INNER JOIN Ingredients ON Ingredients.Ingredient_ID = RecipeIngredients.Ingredient_ID" +
                       " WHERE ingredients.Name = 'Eggs'";

    DataSet recipeDataSet = new DataSet();

    openConnection();

    dataAdapter = new SqlDataAdapter(sqlString, connectionToDB);

    //Fill dataset will create a table with the results
    // so you only need this one line:
    dataAdapter.Fill(recipeDataSet);

    closeConnection();

    return recipeDataSet;

}

You could also have just removed the line:
dataAdapter.Fill(recipeDataSet);

To further verify your logic, debug this and set a break point in ResultsWindow_Load, inspect the result of recipeDataSet.Tables[0] before setting it as the DataSource, you can use the inspection tools in VS to do this, set a watch on the variable or use the immediate console...

// Display data in grid view
// Inspect recipeDataSet.Tables[0], make sure there is only 1 table, and it has rows
if (recipeDataSet.Tables.Count() != 1)
    throw new ApplicationException("Expected only 1 table in the dataset");
if (recipeDataSet.Tables[0].Rows.Count() == 0)
    throw new ApplicationException("no rows found in the data table!");

recipesDataGrid.DataSource = recipeDataSet.Tables[0];

If there are rows, and the grid is still not showing and data, after setting the datasource, call recipesDataGrid.Refresh() to force it to repaint, this can be neccary when you are not using and binding context managers.

If there are no rows

If there are no rows returned the first thing to check is that your filter criteria is correct. I will assume you have hardcoded the WHERE clause here as an example and that ingArray is an array of the ingredients to filter by.

Please update your post to include an example of your implementation of ingredientArray in the _load method and I can update this code with a more complete response.

1. Remove the WHERE clause from the sqlString (just comment out the line for now:

   string sqlString = "SELECT recipes.Name, Instructions, recipes.Preperation_Time, Author FROM RecipeIngredients" +
                  " INNER JOIN recipes ON recipes.Recipe_ID = RecipeIngredients.Recipe_ID" +
                  " INNER JOIN Ingredients ON Ingredients.Ingredient_ID = RecipeIngredients.Ingredient_ID";
   

   If your grid now has data, then the issue was within the filter criteria.

2. Check your connection string, be sure that the database your code is accessing is the same database that you are testing in. Sounds simple but easy mistake to make.