The data isn't being loaded into datagrid view. What am i missing?

Question

I think this is one of those times where I'm looking at the code and it all seems fine because my eye's think it will be. I need a fresh set of eyes to look at this code and tell me way it's not loading into the first datagrid view. Thank you for any help your able to provide.

private DataSet DataSetRentals { get; set; }

public DataRelationForm()
{
   InitializeComponent();
}    

private void DataRelationForm_Load(object sender, EventArgs e)
{
   DataSet relationship = new DataSet("relationship");

   /////
   SqlConnection conn = Database.GetConnection();

   SqlDataAdapter adapter = new SqlDataAdapter("Select * From Car", conn);
   DataSet DataSetRentals = new DataSet("Relationship");

   adapter.FillSchema(DataSetRentals, SchemaType.Source, "Car");
   adapter.Fill(DataSetRentals, "Car");

   adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   adapter.Fill(DataSetRentals, "Car");

   DataTable Car;
   Car = DataSetRentals.Tables["Car"];

   foreach (DataRow drCurrent in Car.Rows)
   {
      Console.WriteLine("{0} {1}",
      drCurrent["au_fname"].ToString(),
      drCurrent["au_lname"].ToString());
   }

   ////////////////////////////////////
   SqlDataAdapter adapter2 = new SqlDataAdapter("Select * From CarRental", conn);

   adapter.FillSchema(DataSetRentals, SchemaType.Source, "Rentals");
   adapter.Fill(DataSetRentals, "Rentals");

   adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   adapter.Fill(DataSetRentals, "Rentals");

   DataTable CarRental;
   CarRental = DataSetRentals.Tables["Rentals"];

   foreach (DataRow drCurrent in CarRental.Rows)
   {
      Console.WriteLine("{0} {1}",
      drCurrent["au_fname"].ToString(),
      drCurrent["au_lname"].ToString());
   }

   /////
   SqlDataAdapter adapter3 = new SqlDataAdapter("Select * From Customer", conn);

   adapter.FillSchema(DataSetRentals, SchemaType.Source, "Customer");
   adapter.Fill(DataSetRentals, "Customer");

   adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   adapter.Fill(DataSetRentals, "Customer");

   DataTable Customer;
   Customer = DataSetRentals.Tables["Customer"];

   foreach (DataRow drCurrent in Customer.Rows)
   {
      Console.WriteLine("{0} {1}",
      drCurrent["au_fname"].ToString(),
      drCurrent["au_lname"].ToString());
   }

   ////////////////////////

   DataSetRentals.Tables.Add(Customer);
   DataSetRentals.Tables.Add(CarRental);
   DataSetRentals.Tables.Add(Car);

   DataRelation Step1 = new DataRelation("Customer2CarR",
   Customer.Columns["CustomerNo"], CarRental.Columns["CustomerNo"]);
   DataSetRentals.Relations.Add(Step1);

   DataRelation Step2 = new DataRelation("CarR2Car",
Car.Columns["CarID"], CarRental.Columns["CarID"]);
   DataSetRentals.Relations.Add(Step2);

   ////////////////////////
   CustomerGrid.DataSource= DataSetRentals.Tables["Customer"];
   CarRGrid.DataSource = DataSetRentals.Tables["CarRental"];
   CarGrid.DataSource = DataSetRentals.Tables["Car"];

   CustomerGrid.SelectionChanged += new EventHandler(Customer_SelectionChanged);
   CarRGrid.SelectionChanged += new EventHandler(CarR_SelectionChanged);
}

private void Customer_SelectionChanged(Object sender, EventArgs e)
{
   if (CustomerGrid.SelectedRows.Count > 0)
   {
      DataRowView selectedRow =
         (DataRowView)CustomerGrid.SelectedRows[0].DataBoundItem;
      DataSetRentals.Tables["CarRental"].DefaultView.RowFilter =
                "CustomerNo = " + selectedRow.Row["CustomerNo"].ToString();
        }

   else
   {

   }
}

private void CarR_SelectionChanged(Object sender, EventArgs e)
{
   if (CarRGrid.SelectedRows.Count > 0)
   {
      DataRowView selectedRow =
         (DataRowView)CarRGrid.SelectedRows[0].DataBoundItem;
      DataSetRentals.Tables["Car"].DefaultView.RowFilter =
         "CarID = " + selectedRow.Row["CarID"].ToString();
   }
}

And this is the code for the Database.GetConnection() method:

SqlConnectionStringBuilder stringBuilder = new SqlConnectionStringBuilder();
bool OnUni;

OnUni = (System.Environment.UserDomainName == "SOAC") ? true : false;

stringBuilder.DataSource = (OnUni) ? @"SOACSQLSERVER\SHOLESQLBSC" : "(local)";
stringBuilder.InitialCatalog = "CarRental_P117365";
stringBuilder.IntegratedSecurity = true;

return new SqlConnection(stringBuilder.ConnectionString);

Does `conn.Open()` get called in the `Database.GetConnection()` method? — valverij, Aug 21 '13 at 14:01
@valverij this is that method. SqlConnectionStringBuilder stringBuilder = new SqlConnectionStringBuilder(); bool OnUni; OnUni = (System.Environment.UserDomainName == "SOAC") ? true : false; stringBuilder.DataSource = (OnUni) ? @"SOACSQLSERVER\SHOLESQLBSC" : "(local)"; stringBuilder.InitialCatalog = "CarRental_P117365"; stringBuilder.IntegratedSecurity = true; return new SqlConnection(stringBuilder.ConnectionString); — George Ian Guy Marsden, Aug 21 '13 at 14:05
Have you stepped through the code with the debugger to verify that `DataSetRentals` is being populated by the adapters? — valverij, Aug 21 '13 at 14:26

score 2 · Answer 1 · edited May 23 '17 at 12:28

2

It looks like you may have forgotten to call SqlConnection.Open() to actually open the connection. I would also recommend wrapping your connection in a using and explicitly calling SqlConnection.Close() at the end of it, so you don't accidentally leave it open:

using(SqlConnection conn = Database.GetConnection())
{
    conn.Open();

    /*
        rest of code here
    */

    conn.Close();
}

For some other good information/examples of properly opening/disposing of SqlConnections, you can also take a look at these SO questions:

edited May 23 '17 at 12:28

Community

1
1

answered Aug 21 '13 at 14:12

valverij

4,871
1
22
35

nope. that didn't work. :( any other suggestions of where i might have missed something or did something slightly wrong (sufficiently wrong for it not to work, but not so wrong it gives exceptions or errors)? – George Ian Guy Marsden Aug 21 '13 at 14:20
I'll take another look at your code above. Even so, the connection definitely still needs to be opened before you use it. – valverij Aug 21 '13 at 14:22
well that might have been one thing. But it's still not working with it being open. I have no idea why it's not working. I did try to use the debugger but I have no idea what I'm looking for. – George Ian Guy Marsden Aug 21 '13 at 14:38
If you put a breakpoint on the line `CustomerGrid.DataSource= DataSetRentals.Tables["Customer"];`, then you can add something like `DataSetRentals.Tables["Customer"].Rows.Count` to the watch window to see how many records actually came back from your query – valverij Aug 21 '13 at 14:57
it seemed to exit the brake points thing before it even got to that stage. which is strange isn't it? does this mean that problem is in the first half of the code? – George Ian Guy Marsden Aug 21 '13 at 15:18
Quite possibly. Try putting a break point on the first line of `DataRelationForm_Load` and step through it line-by-line (F10 in Visual Studio). That should help you pinpoint the issue. You can also check the results of your queries as you go. – valverij Aug 21 '13 at 15:58
@valerij I'm sure it's a powerful tool if you know how to use it but i don't. Am i looking for red lines? or what? – George Ian Guy Marsden Aug 21 '13 at 16:09
1

Knowing how to use the debugger in Visual Studio will make pinpointing (and explaining) issues way easier. Here's a video I found outlining the basics (it's about 17 minutes): http://www.youtube.com/watch?v=C0vDKXIq_9A – valverij Aug 21 '13 at 16:44
+1 @valverij - merely for your tenacity in trying to help the OP with his question. – Brian Aug 21 '13 at 19:46

score 0 · Answer 2 · edited Jun 04 '14 at 17:59

youre not binding your data to any datasoruce.

try something like this

            using(SqlConnection conn = Database.GetConnection())
            try
            {
                {
                 ------your code here up to DataTable Car-----
                  DataTable Car;
                 Car = DataSetRentals.Tables["Car"];

                 gridview.Datasource = Car;


                }
            }
            catch (SqlException sqlex )
            {
                string msg = "Fetch Error:";
                msg += sqlex.Message;
                throw new Exception(msg);
            }

Theres no need to open or close the SQL connection as this is done using the using statement at the top where it opens, and when its finishes closes \ disposes of the connection

The data isn't being loaded into datagrid view. What am i missing?

2 Answers2