Reading and Writting from DataGrid

[jtrumb]

Hello All,

I have a question regarding reading a data grid, then writing to sql server from the data read. The data grid is populated with employee data that consists of a persons name, any job(s) that person worked on for the week, total hours worked on each job for the week, and the total hours worked by the person on each job for the week. The data in the grid populates onLoad from an xls file and is in the following format:

Column2 = Employee Names & Total Label i.e. John Smith or Total For John Smith

Column3 = Jobs a person worked on i.e. Database Development

columns 4,6,8,10,12,14 are headed by taking the month abbreviation, day of monday - friday, and 2 digit year i.e. for this week the headings would be
Jan 19, 08
Jan 20, 08
Jan 21, 08
Jan 22, 08
Jan 23, 08

they each contain a decimal value pertaining to the number of hours a person worked on a given job. 8.0, 6.5 etc...

what i need to do is read and loop through the data grid get a persons name, the job(s) they worked for that week and the hours they worked on said job and put it into a SQL database table called TimeSheet with the following columns:

EmpName
JobName
Weekending - this column must get the value of Sundays date of the current week.
Monday
Tuesday
Wednesday
Thursday
Friday

I do not have much vb.net expierence but i assume you can read the data grid and to some kind of EOF loop taking those values and inserting them into the database. Any help is greatly appreciated since im shooting completely in the dark here. Also a brief explanation on the code would be of great help since i would like to know why and how it works as well.

thanks,
John

 

[cjard]

take a read of the DW2 link in my signature (observing any 3.0 framework specific page notifications in the top right) reading the section Creating a Simple Data App.

It will help you get started with data access. Transfer your data from the datatable showing in the grid, into one that you designed in the dataset designer, then use a tableadapter to upload it to sql server. (see the tutorials for a better understanding)

 

[jtrumb]

I have read the tutorials, i think i have more understanding but there are still things im unsure of. The process as i understand it is as follows:

1. Create a data set using the data set designer - i have done this

2. Create a data table that will be used to port the data into SQL I have created this with the following columns:

EmpName
JobTitle
Monday
Tuesday
Wednesday
Thursday
Friday

Now it seems the goal would be to take the data which is snapped to the data grid, and map it to the data table columns i just created. this is where im a bit confused.. the way the data on the grid is displayed a person may have more jobs worked on then another person so if person 1 worked on 2 jobs the data in the grid would say:

Col2 = John Smith
Col3 = Development
Dec 01, 08 = 8
Dec 02, 08 = 8
Dec 03, 08 = 4
Dec 04, 08 = 4
Dec 05, 08 = 4
Col2 = ""
Col3 = Database Design
Dec 01, 08 = 0
Dec 02, 08 = 0
Dec 03, 08 = 4
Dec 04, 08 = 4
Dec 05, 08 = 4
Col2 = Total John Smith
Dec 01, 08 = 8
Dec 02, 08 = 8
Dec 03, 08 = 8
Dec 04, 08 = 8
Dec 05, 08 = 8

then the next person who worked on 1 job only after a the total line would read:
Col2 = Jane Dough
Col3 = Tech Writing
Dec 01, 08 = 8
Dec 02, 08 = 8
Dec 03, 08 = 8
Dec 04, 08 = 8
Dec 05, 08 = 8

So how can i map the columns of my data grid to the data table i created when the values in col3 vary depending on how many jobs a person worked for that week which will always be different?

If i am way off in my thinking or making a mountain out of a mole hill please let me know, as i need to finish this asap so i need to get on the right track.

thanks again for all your time and help,
John

 

[cjard]

What youre describing is a master/detail relationship

The parent is the Person, the child is their TaskList

Hence you need 2 datagridview, and your dataset has 2 datatables, and a relation exists. The simple data app demonstrates this principle but not all of it


In the parent datatable you would have a column whose .Expression property is something like SUM(Child.[HoursWorkedOn])

There is no need to map data from datatables to datagridview.. the mapping is done automatically by Visual Studio when you drag the data source to the form.

I will prepare an example project to demonstrate my concept as it will likely be quicker than writing it out

 

[cjard]

Here is the example

Notes:
Edit the child rows in the tasks (hours/person) to see the totals in the parent change
There is no DB for this but this doesnt mean that your program shouldnt have a DB. THere is nothing stopping you adding columns to, or removing columns from DataTables that were made by the designer, based on a database.
Note the way the relationship is used by looking at the datasource proeprty of the _personBindingSource and the _tasksBindingSource resepctively

 

[jtrumb]

The example was awesome! i think i have a better understanding of how data tables work. I do still have some questions for example how would i populate the table i create with data from an xls spread sheet, or from a csv file since that is what is populating my data grid on the form. Also how would i ensre that the writting from my xls ignores the rows which are totals of hours worked since i only care about hours worked on a peticular job. and finally how would i write from the data table to a sql db?

-John

p.s.
thanks again for the help.

 

[cjard]

The example was awesome! i think i have a better understanding of how data tables work. I do still have some questions for example how would i populate the table i create with data from an xls spread sheet

Connect the MS Access database driver to it using a connection string you find at ConnectionStrings.com - Forgot that connection string? Get it here!

or from a csv file since that is what is populating my data grid on the form.

If you already have this code, adapt your code to include my example

Also how would i ensre that the writting from my xls ignores the rows which are totals of hours worked since i only care about hours worked on a peticular job.

By only writing rows youre interested in, as you loop over them during the write..
..or by removing offending rows from the tables before you write and then writing everything

and finally how would i write from the data table to a sql db?

Read the DW2 link in my signature, start with Creating a Simple Data App


p.s.
thanks again for the help.[/QUOTE]