Imports System.Data
Imports System.Data.SqlClient
Public Class frmEvents
    Inherits System.Windows.Forms.Form

' Create connection to local SQL Server
    Dim EventsCon As SqlConnection = New SqlConnection("server=(local);database=RES;integrated security=TRUE;")

' Create SQLDataAdapters using SQL statements against views.
    Dim EventAdapt As SqlDataAdapter = New SqlDataAdapter("select * from vwEvents", EventsCon)

    Dim SiteAdapt As SqlDataAdapter = New SqlDataAdapter("SELECT SiteID, SiteName FROM luSite ORDER BY SiteName", EventsCon)

    Dim FuelAdapt As SqlDataAdapter = New SqlDataAdapter("SELECT PartID, PartName FROM luPart WHERE PartLocation = (SELECT PartTypeLocationID FROM vwPartTypeLocationRef WHERE PartTypeName = 'Fuel' AND PartTypeLocationName = 'N/A') ORDER BY PartName", EventsCon)

'Create datasets
    Dim EventDS As DataSet
    Dim SiteDS As DataSet
    Dim FuelDS As DataSet

'Create Dataviews
    Dim EventDV As DataView
    Dim SiteDV As DataView
    Dim FuelDV As DataView

'Create currency manager for form records
    Dim EventCM As CurrencyManager

'Fill Datasets
   Private Sub FillDataSetAndView()
        EventDS = New DataSet()
        SiteDS = New DataSet()
        FuelDS = New DataSet()

        Try
            SiteAdapt.Fill(SiteDS, "luSite")
            EventAdapt.Fill(EventDS, "vwevents")
            FuelAdapt.Fill(FuelDS, "luPart")
        Catch err As SqlException
            MessageBox.Show(err.Message)
        End Try

        EventDV = New DataView(EventDS.Tables("vwevents"))
        SiteDV = New DataView(SiteDS.Tables("luSite"))
        FuelDV = New DataView(FuelDS.Tables("luPart"))
        EventCM = CType(Me.BindingContext(EventDV), CurrencyManager)

    End Sub

' Bind Fields
    Private Sub BindFields()

'Clear all bindings
        EventName.DataBindings.Clear()
        DTEventStartDate.DataBindings.Clear()
        DTEventEndDate.DataBindings.Clear()
        cboSite.DataBindings.Clear()
        EventType.DataBindings.Clear()
        EventLength.DataBindings.Clear()
        Fuel.DataBindings.Clear()
        EventCode.DataBindings.Clear()
        EventNotes.DataBindings.Clear()

'Add data binding for cboSite combobox; declare value and display members
        cboSite.DataBindings.Add("Text", EventDV, "Site")
        Try
            cboSite.DataSource = SiteDS.Tables("luSite")
            cboSite.ValueMember = "SiteID"
            cboSite.DisplayMember = "SiteName"
        Catch err As Exception
            MessageBox.Show(err.Message + err.Source)
        End Try

' Add databindings for Fuel combobox; declare value and display members
        Fuel.DataBindings.Add("Text", EventDV, "Fuel")
        Try
            Fuel.ValueMember = "PartID"
            Fuel.DisplayMember = "PartName"
            Fuel.DataSource = FuelDS.Tables(0)
        Catch err As Exception
            MessageBox.Show(err.Message + err.Source)
        End Try

'Add rest of data bindings
        EventName.DataBindings.Add("Text", EventDV, "eventname")
        DTEventStartDate.DataBindings.Add("Text", EventDV, "EventStartDate")
        DTEventEndDate.DataBindings.Add("Text", EventDV, "EventStartDate")
        EventType.DataBindings.Add("Text", EventDV, "EventType")
        EventLength.DataBindings.Add("Text", EventDV, "EventLength")
        EventCode.DataBindings.Add("Text", EventDV, "EventCode")
        EventNotes.DataBindings.Add("Text", EventDV, "EventNotes")

End Sub

' On load call functions to fill data sets and bind fields
    Private Sub frmEvents_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillDataSetAndView()
        BindFields()

    End Sub

'Move to first record
    Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
        EventCM.Position = 0
    End Sub

'Move to previous record
    Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
        EventCM.Position -= 1
    End Sub

'Move to next record
    Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
        EventCM.Position += 1
    End Sub

'Move to last record
    Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        EventCM.Position = EventCM.Count - 1
    End Sub
End Class