Fill DataGridView from Selecting Another DataGridView Cell

[daveofgv]

Hello All -

Simple / Quick Question - I am using VB.NET (2008)

I have a datagridview1 that is connected to a SQL database.... I want to click on a cell and have another datagridview2 fill with data in my select statement, however, it's not working correctly.

Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        con.Open()

        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter

      da = New OleDbDataAdapter("Select Field1 [NAME], Field2 [Count], Field3 [Doc Count], Field4 [Page Count], Field6 [Date Completed], Field7 [Status] from Files Inner Join Projects On Files.ProjectID = Projects.ProjectID WHERE Projects.ProjectID Like '%" & DataGridView1.SelectedCells.ToString & "%'", con)

        da.Fill(dt)

        DataGridView2.DataSource = dt.DefaultView

        con.Close()

         Label1.Text = DataGridView2.Rows.Count.ToString()
    End Sub

I can pull the data from SQL when I use a label and set it to the ProjectID number, however, I cannot select a cell in DataGridView1 and have that value pull the data from SQL to fill DataGridView2.

the Code I am having issues with is

da = New OleDbDataAdapter("Select Field1 [NAME], Field2 [Count], Field3 [Doc Count], Field4 [Page Count], Field6 [Date Completed], Field7 [Status] from Files Inner Join Projects On Files.ProjectID = Projects.ProjectID WHERE Projects.ProjectID Like '%" & DataGridView1.SelectedCells.ToString & "%'", con)

Any help will be greatly appreciated.

Thanks in advanced

daveofgv

 

[IanRyder]

Hi,

Since you do got give any indication of any errors that you may be getting I am assuming that the ProjectID Field in your Projects table is of type Int? If so then you can get away with saying something like this, even though I would not recommend this syntax:-

Select LastName [Name] From Employees where EmployeeID = '1'

This SQL Syntax will select a record of type Int in the where clause even though you have specified the field type in the above query as type string. This is due to SQL doing it's own conversion as necessary.

However, this causes the propagation of bad practice since you may now think that you can select Int fields based on any string syntax that you send to SQL Server. You Cannot do this. Integer fields need to be treated as Integers with NO string formatting and Wildcard Characters (the "%" character) can ONLY be used on String fields.

So, without further information, my guess is that you are getting a conversion error from type varchar to type int when using your query.

Hope that helps.

Cheers,

Ian

 

[IanRyder]

Just to add some additional info:-

You cannot use the DataGridiew.SelectedCells property like that since this is a collection object and does not return the individual value of the selected cells. If you need each value then you need to use a For loop to get the cell values. i.e:-

For Each currentCell As DataGridViewCell In DataGridView1.SelectedCells
  MsgBox(currentCell.Value.ToString)
Next

Cheers,

Ian

 

[daveofgv]

Thank you for the replies....

The problem I am having is that it is not pulling the data from the database that I am asking for.

Part of my program has this code - which works perfect....

 da = New OleDbDataAdapter("Select Field1 [NAME], Field2 [Original Page Count], Field3 [Doc Count], Field4 [Doc Page Count], Field6 [Date Completed], Field7 [Status] from Files where Field1 like '%" & TextBox1.Text & "%'", con)

Instead of using a text box to enter what I am searching for - and then clicking a button - I am pulling data from one table into a datagridview and wanting to click on a cell that will pull the other data up from another table.

Table 1 = files
Table 2 = Projects

Both tables have "ProjectID" as a relationship.

Am I way off here or am I missing something small?

Thanks in advanced

daveofgv

 

[IanRyder]

Hi,

Instead of using a text box to enter what I am searching for - and then clicking a button - I am pulling data from one table into a datagridview and wanting to click on a cell that will pull the other data up from another table.

Table 1 = files
Table 2 = Projects

Both tables have "ProjectID" as a relationship.

In that case you should be using Parent / Child (also know as Master / Detail) Data Binding on your DataGridViews. Have a look at this thread from jmcilhinney:-

Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)-VBForums

This should get you where you need to be.

Cheers,

Ian

 

[daveofgv]

Thanks for the reply... I decided to click on a cell - have it change a label to what the cell value is and then have the other datagrid pull all the info from my table that is related to the label.text.

The link you sent was informative, but not useful for this case

Thanks

daveofgv