'This source code has been developed by Cuong Pham (cuong@nmsu.edu) & Mary Lucero
'Any use of this software MUST contain this message.
Public Class FrInventory
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents TxOrderNum As System.Windows.Forms.TextBox
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents CbPrimerID As System.Windows.Forms.ComboBox
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents TxCompany As System.Windows.Forms.TextBox
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents CbMonth As System.Windows.Forms.ComboBox
    Friend WithEvents Label5 As System.Windows.Forms.Label
    Friend WithEvents Label6 As System.Windows.Forms.Label
    Friend WithEvents TxPurification As System.Windows.Forms.TextBox
    Friend WithEvents Label7 As System.Windows.Forms.Label
    Friend WithEvents TxLength As System.Windows.Forms.TextBox
    Friend WithEvents Label8 As System.Windows.Forms.Label
    Friend WithEvents TxTemperature As System.Windows.Forms.TextBox
    Friend WithEvents ButAdd As System.Windows.Forms.Button
    Friend WithEvents ButUpdate As System.Windows.Forms.Button
    Friend WithEvents ButDelete As System.Windows.Forms.Button
    Friend WithEvents ButQuit As System.Windows.Forms.Button
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents TxYear As System.Windows.Forms.TextBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Label1 = New System.Windows.Forms.Label
        Me.TxOrderNum = New System.Windows.Forms.TextBox
        Me.Label2 = New System.Windows.Forms.Label
        Me.CbPrimerID = New System.Windows.Forms.ComboBox
        Me.Label3 = New System.Windows.Forms.Label
        Me.TxCompany = New System.Windows.Forms.TextBox
        Me.Label4 = New System.Windows.Forms.Label
        Me.CbMonth = New System.Windows.Forms.ComboBox
        Me.Label5 = New System.Windows.Forms.Label
        Me.TxYear = New System.Windows.Forms.TextBox
        Me.Label6 = New System.Windows.Forms.Label
        Me.TxPurification = New System.Windows.Forms.TextBox
        Me.Label7 = New System.Windows.Forms.Label
        Me.TxLength = New System.Windows.Forms.TextBox
        Me.Label8 = New System.Windows.Forms.Label
        Me.TxTemperature = New System.Windows.Forms.TextBox
        Me.ButAdd = New System.Windows.Forms.Button
        Me.ButUpdate = New System.Windows.Forms.Button
        Me.ButDelete = New System.Windows.Forms.Button
        Me.ButQuit = New System.Windows.Forms.Button
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(16, 176)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(144, 32)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "Order Number"
        '
        'TxOrderNum
        '
        Me.TxOrderNum.Location = New System.Drawing.Point(160, 176)
        Me.TxOrderNum.Name = "TxOrderNum"
        Me.TxOrderNum.Size = New System.Drawing.Size(232, 20)
        Me.TxOrderNum.TabIndex = 1
        Me.TxOrderNum.Text = "TextBox1"
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(16, 208)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(128, 24)
        Me.Label2.TabIndex = 2
        Me.Label2.Text = "Primer ID"
        '
        'CbPrimerID
        '
        Me.CbPrimerID.DisplayMember = "Primer_Sequences.PrimerID"
        Me.CbPrimerID.Location = New System.Drawing.Point(160, 208)
        Me.CbPrimerID.Name = "CbPrimerID"
        Me.CbPrimerID.Size = New System.Drawing.Size(232, 21)
        Me.CbPrimerID.TabIndex = 3
        Me.CbPrimerID.Text = "Combobox1"
        '
        'Label3
        '
        Me.Label3.Location = New System.Drawing.Point(16, 240)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(112, 24)
        Me.Label3.TabIndex = 4
        Me.Label3.Text = "Company"
        '
        'TxCompany
        '
        Me.TxCompany.Location = New System.Drawing.Point(160, 240)
        Me.TxCompany.Name = "TxCompany"
        Me.TxCompany.Size = New System.Drawing.Size(232, 20)
        Me.TxCompany.TabIndex = 5
        Me.TxCompany.Text = "TextBox1"
        '
        'Label4
        '
        Me.Label4.Location = New System.Drawing.Point(16, 272)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(136, 24)
        Me.Label4.TabIndex = 6
        Me.Label4.Text = "Month Purchase"
        '
        'CbMonth
        '
        Me.CbMonth.Location = New System.Drawing.Point(160, 272)
        Me.CbMonth.Name = "CbMonth"
        Me.CbMonth.Size = New System.Drawing.Size(72, 21)
        Me.CbMonth.TabIndex = 7
        Me.CbMonth.Text = "ComboBox1"
        '
        'Label5
        '
        Me.Label5.Location = New System.Drawing.Point(264, 272)
        Me.Label5.Name = "Label5"
        Me.Label5.Size = New System.Drawing.Size(58, 24)
        Me.Label5.TabIndex = 8
        Me.Label5.Text = "Year"
        '
        'TxYear
        '
        Me.TxYear.Location = New System.Drawing.Point(328, 272)
        Me.TxYear.Name = "TxYear"
        Me.TxYear.Size = New System.Drawing.Size(64, 20)
        Me.TxYear.TabIndex = 9
        Me.TxYear.Text = "TextBox1"
        '
        'Label6
        '
        Me.Label6.Location = New System.Drawing.Point(16, 303)
        Me.Label6.Name = "Label6"
        Me.Label6.Size = New System.Drawing.Size(136, 24)
        Me.Label6.TabIndex = 10
        Me.Label6.Text = "Purification Method"
        '
        'TxPurification
        '
        Me.TxPurification.Location = New System.Drawing.Point(160, 304)
        Me.TxPurification.Name = "TxPurification"
        Me.TxPurification.Size = New System.Drawing.Size(232, 20)
        Me.TxPurification.TabIndex = 11
        Me.TxPurification.Text = "TextBox1"
        '
        'Label7
        '
        Me.Label7.Location = New System.Drawing.Point(16, 335)
        Me.Label7.Name = "Label7"
        Me.Label7.Size = New System.Drawing.Size(136, 24)
        Me.Label7.TabIndex = 12
        Me.Label7.Text = "Length"
        '
        'TxLength
        '
        Me.TxLength.Location = New System.Drawing.Point(160, 336)
        Me.TxLength.Name = "TxLength"
        Me.TxLength.Size = New System.Drawing.Size(232, 20)
        Me.TxLength.TabIndex = 13
        Me.TxLength.Text = "TextBox1"
        '
        'Label8
        '
        Me.Label8.Location = New System.Drawing.Point(16, 365)
        Me.Label8.Name = "Label8"
        Me.Label8.Size = New System.Drawing.Size(136, 24)
        Me.Label8.TabIndex = 14
        Me.Label8.Text = "Temperature"
        '
        'TxTemperature
        '
        Me.TxTemperature.Location = New System.Drawing.Point(160, 368)
        Me.TxTemperature.Name = "TxTemperature"
        Me.TxTemperature.Size = New System.Drawing.Size(232, 20)
        Me.TxTemperature.TabIndex = 15
        Me.TxTemperature.Text = "TextBox2"
        '
        'ButAdd
        '
        Me.ButAdd.Location = New System.Drawing.Point(408, 176)
        Me.ButAdd.Name = "ButAdd"
        Me.ButAdd.Size = New System.Drawing.Size(144, 40)
        Me.ButAdd.TabIndex = 16
        Me.ButAdd.Text = "Add  Inventory"
        '
        'ButUpdate
        '
        Me.ButUpdate.Location = New System.Drawing.Point(408, 232)
        Me.ButUpdate.Name = "ButUpdate"
        Me.ButUpdate.Size = New System.Drawing.Size(144, 40)
        Me.ButUpdate.TabIndex = 17
        Me.ButUpdate.Text = "Update"
        '
        'ButDelete
        '
        Me.ButDelete.Location = New System.Drawing.Point(408, 288)
        Me.ButDelete.Name = "ButDelete"
        Me.ButDelete.Size = New System.Drawing.Size(144, 40)
        Me.ButDelete.TabIndex = 18
        Me.ButDelete.Text = "Delete"
        '
        'ButQuit
        '
        Me.ButQuit.Location = New System.Drawing.Point(408, 344)
        Me.ButQuit.Name = "ButQuit"
        Me.ButQuit.Size = New System.Drawing.Size(144, 40)
        Me.ButQuit.TabIndex = 19
        Me.ButQuit.Text = "Quit"
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = "Primer_Inventory"
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(8, 0)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(544, 168)
        Me.DataGrid1.TabIndex = 20
        '
        'FrInventory
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(560, 406)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.ButQuit)
        Me.Controls.Add(Me.ButDelete)
        Me.Controls.Add(Me.ButUpdate)
        Me.Controls.Add(Me.ButAdd)
        Me.Controls.Add(Me.TxTemperature)
        Me.Controls.Add(Me.TxLength)
        Me.Controls.Add(Me.TxPurification)
        Me.Controls.Add(Me.TxYear)
        Me.Controls.Add(Me.TxCompany)
        Me.Controls.Add(Me.TxOrderNum)
        Me.Controls.Add(Me.Label8)
        Me.Controls.Add(Me.Label7)
        Me.Controls.Add(Me.Label6)
        Me.Controls.Add(Me.Label5)
        Me.Controls.Add(Me.CbMonth)
        Me.Controls.Add(Me.Label4)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.CbPrimerID)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Name = "FrInventory"
        Me.Text = "Primer Inventory"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
    Private OrderNum As String
    Private PrimerID As String
    Private Mode As Integer
    Dim strSQL As String

    Private Sub FrInventory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Load_FrInventory()
    End Sub
    Private Sub Load_FrInventory()
        On Error Resume Next
        Dim i As Integer
        Init()
        Dim connString As String = strConn
        Dim myConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection
        myConnection.ConnectionString = connString
        ' create a data adapter 
        Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Select OrderNum,PrimerID,Company, MonthPurchased, YearPurchased, Purification, Length,TM FROM Primer_Inventory", myConnection)

        ' create a new dataset 
        Dim ds As DataSet = New DataSet
        ' fill dataset 
        da.Fill(ds, "Primer_Inventory")


        ' write dataset contents to an xml file by calling WriteXml method 
        ' Attach DataSet to DataGrid

        DataGrid1.DataSource = ds.DefaultViewManager
        DataGrid1.ColumnHeadersVisible = True
        DataGrid1.Refresh()
        i = 1
        While i < 13
            CbMonth.Items.Add(i)
            i = i + 1
        End While



        ' populate combo
        Init()
        Dim ds1 As DataSet = New DataSet
        Dim da1 As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT DISTINCT PrimerID FROM Primer_Sequences", myConnection)
        da1.Fill(ds1, "Primer_Sequences")
        CbPrimerID.DataSource = ds1
        CbPrimerID.DisplayMember = "Primer_Sequences.PrimerID"
        CbPrimerID.Refresh()
        ' make all texts blank
        TxOrderNum.Text() = ""
        CbPrimerID.Text() = ""
        TxCompany.Text() = "QIAGEN"
        CbMonth.Text() = ""
        TxYear.Text() = ""
        TxPurification.Text() = "Salt-free"
        TxLength.Text() = ""
        TxTemperature.Text() = ""

    End Sub
    Private Sub TxOrderNum_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxOrderNum.TextChanged

    End Sub
    Private Sub CbPrimerID_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CbPrimerID.SelectedIndexChanged

    End Sub
    Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click

    End Sub
    Private Sub TxCompany_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxCompany.TextChanged

    End Sub
    Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click

    End Sub
    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

    End Sub

    Private Sub Label6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label6.Click

    End Sub

    Private Sub Label7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label7.Click

    End Sub

    Private Sub Label4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label4.Click

    End Sub

    Private Sub ButQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButQuit.Click
        Me.Hide()
        'CloseConn()
    End Sub

    Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate

    End Sub
    Private Sub DataGrid1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGrid1.Click
        On Error Resume Next
        Dim i As Integer
        Dim t As Boolean
        t = False
        Flush()
        Mode = 1
        i = 0
        On Error Resume Next
        While (t = False)
            If DataGrid1.IsSelected(i) Then
                OrderNum = DataGrid1.Item(i, 0)
                PrimerID = DataGrid1.Item(i, 1)
                TxOrderNum.Text() = DataGrid1.Item(i, 0)
                CbPrimerID.Text() = DataGrid1.Item(i, 1)
                TxCompany.Text() = DataGrid1.Item(i, 2)
                CbMonth.Text() = DataGrid1.Item(i, 3)
                TxYear.Text() = DataGrid1.Item(i, 4)
                TxPurification.Text() = DataGrid1.Item(i, 5)
                TxLength.Text() = DataGrid1.Item(i, 6)
                TxTemperature.Text() = DataGrid1.Item(i, 7)
                ButUpdate.Enabled = True
                ButDelete.Enabled = True
                t = True
            End If
            i = i + 1
        End While
    End Sub

    Private Sub ButAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButAdd.Click

        TxOrderNum.Text() = ""
        CbPrimerID.Text() = ""
        TxCompany.Text() = "QIAGEN"
        CbMonth.Text() = ""
        TxYear.Text() = ""
        TxPurification.Text() = "Salt-free"
        TxLength.Text() = ""
        TxTemperature.Text() = ""
        ' some restrictions
        Mode = 0
        ButUpdate.Enabled = True
        ButDelete.Enabled = False
        TxOrderNum.Focus()
    End Sub
    Private Sub Flush()

        TxOrderNum.Text() = ""
        CbPrimerID.Text() = ""
        TxCompany.Text() = ""
        CbMonth.Text() = ""
        TxYear.Text() = ""
        TxPurification.Text() = ""
        TxLength.Text() = ""
        TxTemperature.Text() = ""
        ' some restrictions

    End Sub

    Private Sub ButUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButUpdate.Click
        On Error Resume Next

        OpenConn()

        If Mode = 0 Then
            If TxOrderNum.Text = "" Then
                MsgBox("Order Number can not be left blank, pls enter Order Number")
                TxOrderNum.Focus()
                Exit Sub
            End If

            strSQL = "INSERT INTO Primer_Inventory (PrimerID,OrderNum,Company,MonthPurchased,YearPurchased,Purification,Length,TM) VALUES ('" & CbPrimerID.Text & _
             "','" & TxOrderNum.Text & _
             "','" & TxCompany.Text & _
             "','" & CbMonth.Text & _
             "','" & TxYear.Text & _
             "','" & TxPurification.Text & _
             "'," & CDbl(TxLength.Text) & _
             ",'" & TxTemperature.Text & "')"


            Dim myCommand As New OleDb.OleDbCommand(strSQL, myConnection)
            myCommand.Connection = myConnection
            'myCommand.Connection.Open()
            myCommand.ExecuteNonQuery()

            myCommand.Dispose()

            'Redraw
            Init()

            Dim myCon As OleDb.OleDbConnection = New OleDb.OleDbConnection
            myCon.ConnectionString = strConn
            ' create a data adapter 
            Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Select OrderNum,PrimerID,Company, MonthPurchased, YearPurchased, Purification, Length,TM FROM Primer_Inventory", myConnection)

            ' create a new dataset 
            Dim ds As DataSet = New DataSet
            ' fill dataset 
            da.Fill(ds, "Primer_Inventory")

            ' write dataset contents to an xml file by calling WriteXml method 
            ' Attach DataSet to DataGrid

            DataGrid1.DataSource = ds.DefaultViewManager
            DataGrid1.ColumnHeadersVisible = True
            DataGrid1.Refresh()

            da.Dispose()
            ds.Dispose()
            myCon.Close()

        End If

        If Mode = 1 Then
            If TxOrderNum.Text = "" Then
                MsgBox("Order Number can not be left blank, pls enter OrderNum")
                TxOrderNum.Focus()
                Exit Sub
            End If
            strSQL = "UPDATE Primer_Inventory SET OrderNum= '" & TxOrderNum.Text & "'," & _
             " PrimerID= '" & CbPrimerID.Text & "'," & _
             " Company= '" & TxCompany.Text & "'," & _
             " MonthPurchased= '" & CbMonth.Text & "'," & _
             " YearPurchased= '" & TxYear.Text & "'," & _
             " Purification= '" & TxPurification.Text & "'," & _
             " length= " & CDbl(TxLength.Text) & "," & _
             " TM= '" & TxTemperature.Text & _
             "' WHERE OrderNum='" & OrderNum & "' AND PrimerID='" & PrimerID & "'"

            Dim myCommand As New OleDb.OleDbCommand(strSQL)
            myCommand.Connection = myConnection
            myCommand.ExecuteNonQuery()

            DataGrid1.Item(DataGrid1.CurrentRowIndex, 0) = TxOrderNum.Text
            DataGrid1.Item(DataGrid1.CurrentRowIndex, 1) = CbPrimerID.Text
            DataGrid1.Item(DataGrid1.CurrentRowIndex, 2) = TxCompany.Text
            DataGrid1.Item(DataGrid1.CurrentRowIndex, 3) = CbMonth.Text
            DataGrid1.Item(DataGrid1.CurrentRowIndex, 4) = TxYear.Text
            DataGrid1.Item(DataGrid1.CurrentRowIndex, 5) = TxPurification.Text
            DataGrid1.Item(DataGrid1.CurrentRowIndex, 6) = CDbl(TxLength.Text)
            DataGrid1.Item(DataGrid1.CurrentRowIndex, 7) = TxTemperature.Text
            myCommand.Dispose()

        End If

        myConnection.Close()


    End Sub

    Private Sub ButDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButDelete.Click
        On Error Resume Next
        Mode = 2

        Dim msg As String
        Dim title As String
        Dim style As MsgBoxStyle
        Dim response As MsgBoxResult
        msg = "Do you  really want to delete the Order :" & OrderNum & " ?"  ' Define message.
        style = MsgBoxStyle.DefaultButton2 Or _
           MsgBoxStyle.Critical Or MsgBoxStyle.YesNo
        title = "Delete Comfirmation"   ' Define title.
        ' Display message.
        response = MsgBox(msg, style, title)
        If response = MsgBoxResult.Yes Then   ' User chose Yes.
            strSQL = "DELETE FROM Primer_Inventory  WHERE OrderNum='" & OrderNum & "' AND PrimerID='" & PrimerID & "'"
            OpenConn()
            Dim myCommand As New OleDb.OleDbCommand(strSQL)
            myCommand.Connection = myConnection
            myCommand.ExecuteNonQuery()
            DataGrid1.Refresh()
            myCommand.Dispose()
            myConnection.Close()
            Load_FrInventory()
            ' Perform some other action.
        End If


    End Sub
End Class
