Databinding - How does it work, and where do I start?

[Infog]

I have a database in Access 2007, and a GUI in VB.NET 3.5. Where should I go to start learning about databinding, and how to have the front end manipulate the database?

I would love to find some learning resources for coding and concepts that is more concise/condensed than random forums, Google, and the MSDN developer references.

Most of these questions will be about databinding, datasets, querying, etc. The several general questions I have to start the learning process are:

1. Is there a way to move the Access file without needing to manually make a new connection string? (ie, how to change the connection string, or reload the database to allow communication with new tables and queries.)
   
2. How do I go about querying the database dataset adapter to get a sub-set of data?
   
3. How do I send changes back to the database, to update a set of tables based on foriegn keys and unique IDs?
   
4. How do I find the ID of a selected data grid view row?
   
5. The list view looks nicer than the data grid view. Would it have the same functionality as the data grid view, or is that too hard for a newbie?

Thanks, please don't hesitate to direct me elsewhere for answers if something has already been covered.

Chris

[MrShadow]

G'Day,
I don't use data binding anymore. I find it more flexible to manually control the data being displayed. This is more tricky as you need to manually keep track of position in the data rows and handle end of records etc. However, to answer some of your questions:

1. Not sure. I don't think so. Maybe have an options form where the user can browse for the file if they move it (otherwise, have a default value).
2. You could try running an SQL query on the database and either loading this into another dataset or clearing the existing dataset and reloading it.
3. If you're using databinding, any changes you make should be automatically updated in the database (not sure if this is still the case with later versions of VB.NET).
4. You could add another column with a width of 0 and place the row ID in this. Then you just need to get the selected column and read the data from it.This is how I do it.
5. With the list view, you'll need to handle everything manually. This can be tricky but once again it probably provides you with the greatest flexibility. It would be rather difficult for a newbie.

I hope this helps.

Cheers

[Infog]

What I've learned so far:
Databinding using the automatic binding in VB.NET is a mess. It is simpler, in a way, to just query the database yourself, save that to a dataset/table somewhere, and referance the stored table whenever you need data.

1. To move the Access database and still have the .NET program connect:
Use My.Settings.whatever in order to store the filepath.

    'Change database location
    Private Sub btnDbChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDbChange.Click
        'I have no idea... Where did I even find this code online??
        Dim fdlg As OpenFileDialog = New OpenFileDialog()
        fdlg.Title = "Select the database"
        fdlg.InitialDirectory = "c:\"
        fdlg.Filter = "Access 2007 database files (*.accdb)|*.accdb"
        fdlg.FilterIndex = 1
        fdlg.RestoreDirectory = True
        If fdlg.ShowDialog() = DialogResult.OK Then
            Me.txtDbLoca.Text = fdlg.FileName
        End If
    End Sub

2. How to query an Access database to get a table?

    Public Function FillFromAccess(ByVal QueryString As String, ByVal FillTable As String) As DataSet
        'Get data from a local Access database
        Dim OLEConnString As String
        OLEConnString = "Provider=" & My.Settings.Connection_AccessProvider & ";" _
            & "Data Source=" & My.Settings.Connection_Location & ";"
 
        Dim DBConn As New OleDbConnection(OLEConnString)
        Dim DBCommand As OleDbDataAdapter
        Dim dataset_Access As New DataSet
 
        'Pull a table out of the database.
        DBCommand = New OleDb.OleDbDataAdapter(QueryString, DBConn)
        Try 'Put that into an object
            DBCommand.Fill(dataset_Access, FillTable)
        Catch ex As Exception
            FillFromAccess = Nothing
            Exit Function
        End Try
 
        'Send the dataset back to the sub that called this function!
        Return dataset_Access
    End Function

3. Update the database when I need to make changes?

Public Sub SendToAccess(ByVal QueryString As String)
        'Insert, Update, or Delete data in a local Access Database table
 
        'http://www.eggheadcafe.com/community/aspnet/14/70996/insert-into-statement-ol.aspx
        Dim DBCommand As New OleDb.OleDbCommand(QueryString, New OleDb.OleDbConnection("Provider=" & My.Settings.Connection_AccessProvider & ";" & "Data Source=" & My.Settings.Connection_Location & ";"))
 
        DBCommand.Connection.Open()
        Try
            DBCommand.ExecuteNonQuery()
        Catch ex As Exception
        End Try
        DBCommand.Connection.Close()
        DBCommand.Dispose()
        DBCommand = Nothing
    End Sub

4. How do I find the ID of a record when it is in a DataGridView?

'* Find if a row is selected
        'http://msdn.microsoft.com/en-us/library/x8x9zk5a.aspx
        Dim selectedRowCount As Integer = Me.dgvTasks.Rows.GetRowCount(DataGridViewElementStates.Selected)
        If selectedRowCount <= 0 Then Exit Sub
 
        ' Task ID
        Try
            intTaskID = Me.dgvTasks.SelectedRows(0).Cells.Item("ID").Value
        Catch ex As Exception
        End Try

5. Why not use the ListView instead of the DataGridView?
Well... a ListView requires you to do more coding to make it look and behave correctly. AND - you can change a dgv to look like a ListView. Look for the properties AutoSizeColumnsMode, GridColor, and SelectionMode. If you put this code in a public module, you will be able to change all DataGridViews to look the same, with one line of code for each, when it's form loads:

    Public Sub dgvRowColors(ByVal dgv As DataGridView)
        'http://www.dotnetspider.com/resources/23026-GridView-FAQ-s.aspx
        With dgv
            .RowsDefaultCellStyle.BackColor = Color.White
            .AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray
            .GridColor = Color.Gray
        End With
    End Sub

That's all! I hope that someone finds this and puts it to use. Please leave a comment here if this helped you.