Re: Retrieving and Saving Data in Databases

[lithium1976]

This is what I have so far, it just displays the data in 3 datagridviews. I am not too sure what to do next with regards to making command object to select a customer from a DGV and stock item from a DGV to create a booking.

Public Class frm_bookings
    Inherits System.Windows.Forms.Form

    Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=False;" & "Initial Catalog=bookingsdb;Data Source=MY-PC\SQLEXPRESS"
    Dim conn As New SqlConnection(connectionString) 'define the connection as an sql connection
    Dim ds As New DataSet 'give the dataset the name of "ds"
    Dim dataTable As DataTable 'give the datatable the name of datatable

    Dim CustomersDataAdapter As New SqlDataAdapter("SELECT * from tblcustomers", conn)
    Dim CustomersBindingSource As New BindingSource() 'for tblcustomers

    Dim StockDataAdapter As New SqlDataAdapter("SELECT * from tblstock", conn)
    Dim StockBindingSource As New BindingSource() 'for tblstock

    Dim BookingsDataAdapter As New SqlDataAdapter("SELECT * from tblbookings", conn)
    Dim BookingsBindingSource As New BindingSource() 'for tblbookings

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        CustomersDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        CustomersDataAdapter.Fill(ds, "tblcustomers")
        dataTable = ds.Tables("tblcustomers") 'When the form loads, the datatable uses the "tblcustomers" table.
        CustomersBindingSource.DataSource = ds
        CustomersBindingSource.DataMember = "tblCustomers"
        dgvCustomers.DataSource = CustomersBindingSource
        dgvCustomers.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        StockDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        StockDataAdapter.Fill(ds, "tblstock")
        dataTable = ds.Tables("tblstock") 'When the form loads, the datatable uses the "tblcustomers" table.
        StockBindingSource.DataSource = ds
        StockBindingSource.DataMember = "tblstock"
        dgvStock.DataSource = StockBindingSource
        dgvStock.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        BookingsDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        BookingsDataAdapter.Fill(ds, "tblbookings")
        dataTable = ds.Tables("tblbookings") 'When the form loads, the datatable uses the "tblcustomers" table.
        BookingsBindingSource.DataSource = ds
        BookingsBindingSource.DataMember = "tblbookings"
        dgvBookings.DataSource = BookingsBindingSource
        dgvBookings.SelectionMode = DataGridViewSelectionMode.FullRowSelect
    End Sub

[si_the_geek]

Split from this CodeBank thread.

[mendhak]

You can use DataGridView.SelectedIndex to get the selected row in the dgv. You can therefore use DataGridView1.Rows[DataGridView1.SelectedIndex-1] to read the primary key of the row of data that has been selected.

[jmcilhinney]

As you've bound your grid to a BindingSource you'd use its Current property to get the selected DataRowView. You can then get any field value from that row. If you need to add a row to a grid you call AddNew on the bound BindingSource to create a new DataRowView, set its field values and then call EndEdit on the BindingSource to add it to the underlying DataTable.