How can I make a query to fill a dataset with a dynamic filter

[philiop]

Hi,

I have been playing about with the Create Query tool in Visual Studio 2010 and I am finding it useful, how ever I can only fill the filter function without static information.

Basaically I have two tables that are linked, I am wanting to display information from one table called Notes. Annoyingly the person who originally created these tables made a second table called File2Note to link that table to our main client database called File. To sum it up, each file has a FileID, in the File2Note there is a column called FileID and a column called NoteID, and in the Note table there is a column called NoteID. Dont see why they couldnt have just put them in the same table but never mind

So I am wanting something along the lines of

me.NoteTableAdapter.query(me.dataset.note)

where I have created a query with a dynamic filter on the FileID.

Sorry If I have explained badly.

 

[philiop]

Done a little bit more looking into it. The query I basaically want is this.

Dim FileID as Integer 'Used to Store the Current FileID
Dim iR as Integer 'Used to Store the Current Row Index

'Set iR to the value of CurrentRow index
ir = FileDataGridView.CurrentRow.Index 

'Set the currentcell value to the cell containing FileID
FileDataGridView.CurrentCell = FileDataGridView.Rows(iR).Cells(2) 

'Set FileID to the currently selected FileID
FileId = FileDataGridView.CurrentCell

I would use this code to get my current FileID, i know there is probably a much nicer way to do this but I am very new to this so working with what i know.

I then know that the query produces this

SELECT Note.id, Note.DateTime, Note.AuthorId, Note.Text
FROM Note INNER JOIN File2Note ON Note.Id = File2Note.NoteId
WHERE (File2Note.FileId = FileId) I currently have this set to = 0 as i dont know how to make this bit work.