DataGrid(view) populate problem

[johncassell]

Hi There,

I have a DataGrid(view) which is not behaving the way I would like and hope someone can help..

Background: 2 tables in DB (WS_Jobs & FittersJobHours).
I have a relationship between the field 'Job_Number' in the parent table 'WS_Jobs' and the field 'Job_Number' in the child table 'FittersJobHours' (Relation Only)

On my form I have dragged the FittersJobsHours datagrid(view) from the 'FittersJobHours' table which is attached to the WS_Jobs table (Not the one on it's own).

As you can see from the image below, when the form loads, my textboxes are automatically filled with the first job in the DB.

The datagrid(view) doesn't populate though. I need it to populate and also allow new lines to be added...

Any ideas would be really appreciated..

Thanks

John

 

[Stonkie]

I understand that you bound the datagridview to the datatable of your typed dataset (since your columns appeared), but that the table contains no data. To fill the datatable with the data, you need to add the line :

adapter_FittersJobHours.Fill(dataset.FittersJobHoursDataTable)

Now, you would be forced to set a client side filter to the datagridview everytime the BindingSource that is binding your data from WS_Jobs (say binder_Job) to the textboxes changes position. That is pretty inefficient, so unless network usage is a problem, you would access the database and load only the records from FittersJobHours that are linked to the current job.

To do that, use the designer to create a query such as this one :

SELECT Id, col1, col2 FROM FittersJobHours WHERE JobId = @JobId

This will add a method to your adapter_FittersJobHours, say FillByJobId. You would call it in the binder_job.PositionChanged event handler like this :

adapter_FittersJobHours.FillByJobId(table_FittersJobHours, Directcast(Directcast(binder_Job.Current, DataRowView).Row("Id"), integer)

That way, the content of the table and effectively that of the datagridview will reflect only the record that relate to the current job.

The code may contain typos, I do not have Visual Studio ready...

 

[cjard]

As you can see from the image below, when the form loads, my textboxes are automatically filled with the first job in the DB.

The datagrid(view) doesn't populate though. I need it to populate and also allow new lines to be added...

I made the same mistake when I was starting out with datarelations. I thought that if I filled the parent, then the child would fill automatically, with all related records.. Fraid not

You have to fill the child yourself, manuall, because all that the Datarelation does is a simple filter to ensure the known (i.e. you downlaoded them yourself) children on show relate to the parent.

Perhaps some code would help.

First off, its a dumb idea to downlaod EVERY job in the database. Read the DW2 link about "Creating a form to search data"
That will let you search for a parent job to fill

SELECT * FROM parent WHERE job_id = @jobID

  parentTA.FillByJobID(parentDT, SOME_JOB_NUMBER)

Then, however the child table is connected, we fill the child:
If the Child table has the parent Job ID as the foreign key, we create another query on the child table that fills all children by that job id:

SELECT * FROM child WHERE parent_job_id = @parentJobID

childDT.FillByParentJobID(childDT, SOME_JOB_NUMBER)

-

Suppose your search is a bit different. We want to load all data for Mr Smith:


SELECT * FROM parent WHERE cust_name = @custName

parentTA.FillByCustName(parentDT, "Mr Smith")

10 rows are returned, who collectively have 57 children. We reuse the child filling code from before

childTA.ClearBeforeFill = False 'must turn this off otherwise each fill clears the data!
childDT.BeginLoadData() 'turn off events on the datatable
For Each p_row as ParentRow in parentDT
  childTA.FillByParentJobID(childDT, p_row.job_id)
Next p_row
childDT.EndLoadData()

Hopefully this now makes sense!

 

[johncassell]

thanks very much both of you for your replies. much appreciated.

I am kicking myself now because this was the problem...

My job number was '1' and the job number in my child table was '1 ' because of my bad type selection in the SQL Server, the number was padded out.

What I was doing beforehand was correct just it couldn't find '1 ' in the jobs table.

Thanks again for your replies.

John