My DataSet Doesn't Work :S

dtsn

Hi,

In my project I have one DataSet ('Step') from this I have two tables ('Students' and 'PostCode'). I have set them up in both the same way. I can successfully call Step.Students.FindBy(PK) and the function will return either null or not null depending on the results it finds, which is what it should do. However my second table I call Step.PostCode.FindBy(PK) and it always returns null no matter what is in the database. It actually returns null for everything, select, count etc. It looks to me like the dataset isn't actually connecting, even though my GridView successfully shows the content of the table I originally thought the changes weren't being committed but even when I use AcceptChanges() it still doesn't work.

One thought I have had though is that I am still accessing the first table when trying to access the second.

Any thoughts anyone has on this would be great, i have tried looking for an answer for 2 days without any success.

Thanks

Daniel

----------------------------------------------------------------------------------------------

class ExcelHandling
{
public void getData(string Location, StepDataSet Step)
{
// Create connection to the JET Datasource
string dummy = "";
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + Location + @";
Extended Properties=""Excel 8.0;HDR=YES;""";

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
int count = 0;
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT ApplicationID, StudentID, AppEligibleCurrentYear, AppForename, AppSurname, AppAddress1, AppAddress2, AppAddress3, AppCity, Email, Phone, AppPostCode, AppTermAddress1, AppTermAddress2, AppTermAddress3, AppTermCity, AppTermPostCode, AppDOB, AppUniversityName, AppUniversityNameNotListed, AppCourseName, AppQualSubject, AppQualResult, AppLanguageSkills, AppPublicTransport, AppDrivingLicense, AppVehicleAccess, AppAttitude, AppLeisure, AppComputerSkills, AppWorkExperience, AppMainSkills, AppStudentNotes, AppliedDate FROM [Student_Applications$]";
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{

while (dr.Read())
{
if (Step.Students.FindByApplicationID(Int32.Parse(dr["ApplicationID"].ToString())) == null)
{
Step.Students.AddStudentsRow(
Int32.Parse(dr["ApplicationID"].ToString()),
Int32.Parse(dr["StudentID"].ToString()),
0,
Int32.Parse(dr["AppEligibleCurrentYear"].ToString()),
(dr["AppForename"].ToString()), (dr["AppSurname"].ToString()),
(dr["AppAddress1"].ToString()), (dr["AppAddress2"].ToString()),
(dr["AppAddress3"].ToString()), (dr["AppCity"].ToString()),
(dr["Email"].ToString()), (dr["Phone"].ToString()),
(dr["AppPostCode"].ToString()), (dr["AppTermAddress1"].ToString()),
(dr["AppTermAddress2"].ToString()), (dr["AppTermAddress3"].ToString()),
(dr["AppTermCity"].ToString()), (dr["AppTermPostCode"].ToString()),
(dr["AppDOB"].ToString()), (dr["AppUniversityName"].ToString()),
(dr["AppUniversityNameNotListed"].ToString()), (dr["AppCourseName"].ToString()),
(dr["AppQualSubject"].ToString()), (dr["AppQualResult"].ToString()),
(dr["AppLanguageSkills"].ToString()), Int32.Parse(dr["AppPublicTransport"].ToString()),
Int32.Parse(dr["AppDrivingLicense"].ToString()), Int32.Parse(dr["AppVehicleAccess"].ToString()),
(dr["AppAttitude"].ToString()), (dr["AppLeisure"].ToString()),
(dr["AppComputerSkills"].ToString()), (dr["AppWorkExperience"].ToString()),
(dr["AppMainSkills"].ToString()), (dr["AppStudentNotes"].ToString()),
DateTime.Parse(dr["AppliedDate"].ToString()), DateTime.Now);
count++;

Mail Msg = new Mail();
string preface = dr["AppPostCode"].ToString().Substring(0, 2).ToUpper();
if (Step.PostCode.FindByPostCode(preface) == null)
{
PostCode PostCodeCheck = new PostCode(preface);
if (PostCodeCheck.ShowDialog() == DialogResult.OK)
{
Msg.sendSuccess(dr["Email"].ToString());
}
else
{
Msg.sendFailure(dr["Email"].ToString());
}
}
else
{
Msg.sendSuccess(dr["Email"].ToString());
}



}
dr.NextResult();
}
}
}
}

MessageBox.Show(count + " students were successfully added to the database.", "Update Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

GavH

Hi Daniel,

Can you provide the code you use to fill the PostCode table please,

Regards

dtsn

Hi,

Thanks for responding!

I just used the Database Explorer to create the table, I then manually added a value using the Database Explorer so I know it's in there. However I then thought that the Database might write over itself on load. However the data still appears in my GridView control.

Daniel

GavH

Hi Daniel,

I am a little confused as I dont belive you have any data in the dataset before you start the above procedure.

A designed DataSet (strongly typed DataSet) is not automatically filled with data, you must still provide the code to fill the data. If you used the designer to bind a DataSource to a DataGridView Visual Studio takes care of these details for you using a table adapter for each table in the DataSet in your form load event you should have two tableAdapters one for each of your dataset tables, it is these lines of code that are responsible for populating the DataTables in the DataSet, are these lines present

Consider what is happening in your posted procedure in simple terms :

1) START

2) OPEN Database Connection

3) FOREACH result FROM Database

4) IF result[StudentID] NOT EXIST IN DataSet[Students]

5) ADD result TO DataSet[Students]

6) IF result[Postcode] NOT EXIST IN DataSet[PostCode]

7)

8) END IF

9) END IF

10) END FOR

11) CLOSE Database Connection

12) FINISH

Line 4, if the student does not exists in the DataSet, you are adding the student to the DataSet, students table, that means that if you have an empty DataSet to start with, the students table

is going to fill up, DataRow by DataRow, but I suspect that before that point it could be empty.

Line 6, You are checking for the postcode in the postcode table, but you have not entered any data into it, if the postcode table was empty at the start of the procedure, it will most certainly still be empty now and remain that way on each and every iteration through the loop.

Does that make sense That could be why it seems the student table is populated, whilst the postcode table is not, because you are filling the student table in the loop.

At the very top of your method place the following code and let me know what the result is,

string sDataCount = Step.Students.Rows.Count.ToString() + " " + Step.PostCode.Rows.Count.ToString()
System.Windows.Forms.MessageBox.Show(sDataCount);

Regards

dtsn

Hi,

Yep, you are right it just wasn't adding any data into the dataset. Would you know why when i manually added data through the Database Explorer the data was lost when i complied it

Thanks

Daniel

GavH

Hi Daniel.

I think you are misunderstanding how the database explorer works. When you add a database to the database explorer, you have only added a reference to it, so that the database explorer can access that database.

When you browse through the database using the database explorer, and open one of its tables, you have opened the table that is in the orginal database, if you then add data to that table, the data is added to the original database, not to your application.

There are several methods of pulling that data into your application, but we will consider how it works with DataGridView (The principles remain the same across ADO.NET).

When you add a DataGridView to a form, you have the opportunity to set a DataSource, you can also set this DataSource by using the .DataSource property in the property explorer. When you add your DataSource Visual Studio creates a BindingSource, a DataSet and a TableAdapter, each named with a prefix representing the name of your selected DataSource.

Visual Studio also adds some code to the Load event handler of your form. The code should look a little like this :

this.myTableAdapter.Fill(this.myDataSet.PubSummary);

What this code does is pull the data from your database (as selected in the datasource) and fills that data into the matching Table in the DataSet, until that time the Tables in your DataSet are empty.

Note that if you selected multiple tables in your .DataSource then there will be a line like the above for each Table required.

One TableAdapater fills one table, so will have one for each Table, you will have one DataSet which can house any number of tables and one BindingSource which binds the DataSet to the DataGridView.

If you do not have any code like : this.myTableAdapter.Fill(this.myDataSet.PubSummary); anywhere in your project (Visual Studio places it in the Load Event Handler, but you may have deleted it) then when you run your application, it never gets the data from the Database.