datagrid/details on a form, linking 2 tables - VB.NET 2005 & SQL 2000

[adrianjohnson]

I have 2 tables in a SQL Server 2000 database - Users and Department. The link between them is an integer field, ID.

In my form in VB.NET 2005, I have a datagrid, and some details fields, showing the data from the Users table. So when a user selects a cell in the datagrid, the details section is filled with the relevant information (from the Users table).

However, as the link to the Department table is an integer, I don't want to display a combo box with the integer value in - I'd rather populate the combo box with all the departments, but the correct one is displayed when the user selects the cell in the datagrid. E.g. if the user selects Bob the User, his details appear in the correct section including the name of the department (read from the Department) table, not the id value.

At the moment, I have a DataAdpater, DataView and DataSet for each table. I'm not sure that's the best way of doing things - I found it better to code everything, rather than use the wizards.

Any help would be appreciated.

Thanks,

Adrian Johnson

Let me know if you need to see my code. I wondered whether a description would be sufficient (as it's not a problem with code, as such).

 

[onpnt]

not with you...if you are filling the ID fine why not point to the other field instead sense your query is working properly.

can you post the grid code and the event that loads the ID


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[djj55]

I just finished doing something similar to this.
I have a form based on a data set table of users, controled by a combo box (User name), also on this form is a combo box (Group) that is related to the user by an ID. When the user changes I change the related Group combo box by using a datarow find (on primary key) of the group table and set the group combo box to that value.

I hope this makes sense.
djj

 

[adrianjohnson]

Thanks for the help so far.

Here is the code:

Private Sub FillDataSetAndView()

        ' Declarations.
        objDataAdapterUsers = New SqlDataAdapter
        objDataAdapterDept = New SqlDataAdapter
        objDataSet = New DataSet
        objDataSetDept = New DataSet

        ' Set the database connection and open it.
        objConn = New SqlConnection("<connection string here>")
        objConn.Open()

        ' Assign a new SqlCommand to the Departments data adapter.
        objDataAdapterDept.SelectCommand = New SqlCommand
        objDataAdapterDept.SelectCommand.Connection = objConn
        objDataAdapterDept.SelectCommand.CommandText = "SELECT * FROM Departments ORDER BY ID ASC"

        ' Fill the data set with data from Departments.
        objDataAdapterDept.Fill(objDataSetDept, "Departments")

        ' Create a DataView object to store the data.
        objDataViewDept = New DataView(objDataSetDept.Tables("Departments"))

        ' Assign a new SqlCommand to the Departments data adapter.
        objDataAdapterUsers.SelectCommand = New SqlCommand
        objDataAdapterUsers.SelectCommand.Connection = objConn
        objDataAdapterUsers.SelectCommand.CommandText = "SELECT * FROM Users ORDER BY ID ASC"

        ' Fill the data set with data from Departments.
        objDataAdapterUsers.Fill(objDataSet, "Users")

        ' Create a DataView object to store the data.
        objDataView = New DataView(objDataSet.Tables("Users"))
        
        ' Set our CurrencyManager object to the DataView object.
        objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)

        ' Bind the data grid to the data set and do some formatting.
        dgUsers.AutoGenerateColumns = True
        dgUsers.DataSource = objDataSet
        dgUsers.DataMember = "Users"
        dgUsers.Columns(0).Width = "30"
        'dgUsers.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

    End Sub

Private Sub BindFields()

        ' Clear any previous bindings.
        txtID.DataBindings.Clear()
        txtName.DataBindings.Clear()
        cmbDept.DataBindings.Clear()
        txtExt.DataBindings.Clear()
        txtNotes.DataBindings.Clear()

        ' Add new bindings to the DataView object.
        txtID.DataBindings.Add("Text", objDataView, "ID")
        txtName.DataBindings.Add("Text", objDataView, "Name")
        txtExt.DataBindings.Add("Text", objDataView, "Ext")
        txtNotes.DataBindings.Add("Text", objDataView, "Notes")

    End Sub

I've not bound the combo box to a field, because I want to bind it to the Name field in the Departments table. So, when the user selects a cell in the datagrid (which is reading the Users table) the combo box needs to display the Department Name (and not the ID) from the Department table.

djj: I didn't understand your post as it didn't mention 2 tables - unless I'm not understanding something.

Thanks,

Adrian

 

[djj55]

Hello, sorry about the confussion.
The data tables are the user information (table 1) and the group information (table 2) tables.

After some thought my solution may not be the best as I do not use the relationship between the two tables but use code.

Hope someone can impart words of wisdom as I too will be interested in the answer.
djj

 

[adrianjohnson]

It's ok, I've got it now.

Add the following code to the end of the FillDataSetAndView method:

cmbDept.DataSource = objDataViewDept
cmbDept.ValueMember = "ID"
cmbDept.DisplayMember = "Name"

Then, in the CellClick event for the datagrid, add:

Dim lstest2 As Integer = dgUsers.Item(2, e.RowIndex).Value
cmbDept.SelectedValue = lstest2

(assuming the Department column is in position 2 in the datagrid.

Hey presto.

Adrian Johnson