'This source code has been developed by Cuong Pham (cuong@nmsu.edu) & Mary Lucero
'Any use of this software MUST contain this message.

Module Module1
    Public strConn As String
    Public strSQL As String

    Public myConnection As OleDb.OleDbConnection
    Public Function Init()
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
                               & "C:\Jornada\Strainbook\strainbook.mdb"

    End Function
    Public Function OpenConn()
        On Error Resume Next
        Init()
        myConnection = New OleDb.OleDbConnection(strConn)
        myConnection.Open()
    End Function
    Public Function CloseConn()
        myConnection.Close()
    End Function
    Public Function getID(ByVal tableName As String, ByVal IDName As String, ByVal colName As String, ByVal keySearch As String) As String
        On Error Resume Next
        ' Retrieve all the Company Names in the table

        Init()
        Dim dbConn As System.Data.OleDb.OleDbConnection = _
        New System.Data.OleDb.OleDbConnection(strConn)

        dbConn.Open()
        Dim DSet As New DataSet, SQLStr As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter

        Dim tRow As DataRow, tTbl As DataTable
        With dbAdaptr
            .TableMappings.Add("Table", tableName)
            SQLStr = "SELECT " & IDName & "  FROM  " & tableName & " WHERE " & colName & "='" & keySearch & "'"
            cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
            cmd.CommandType = CommandType.Text
            .SelectCommand = cmd
            .Fill(DSet)
            .Dispose()
        End With

        DSet.AcceptChanges()
        tTbl = DSet.Tables.Item(0)
        DSet.Dispose()
        dbConn.Close()

        ' fill out array by Company Names for cboRightCombo combobox


        ' Load the Company Names into the ComboBox Control

        'getID = tRow(IDName).ToString
        For Each tRow In tTbl.Rows
            Return tRow(IDName).ToString
        Next
    End Function
    Public Function getName(ByVal tableName As String, ByVal IDName As String, ByVal colName As String, ByVal keySearch As String) As String

        ' Retrieve all the Company Names in the table
        On Error Resume Next
        Init()
        Dim dbConn As System.Data.OleDb.OleDbConnection = _
        New System.Data.OleDb.OleDbConnection(strConn)

        dbConn.Open()
        Dim DSet As New DataSet, SQLStr As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter

        Dim tRow As DataRow, tTbl As DataTable
        With dbAdaptr
            .TableMappings.Add("Table", tableName)
            SQLStr = "SELECT " & colName & "  FROM  " & tableName & " WHERE " & IDName & " = " & keySearch
            cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
            cmd.CommandType = CommandType.Text
            .SelectCommand = cmd
            .Fill(DSet)
            .Dispose()
        End With

        DSet.AcceptChanges()
        tTbl = DSet.Tables.Item(0)
        DSet.Dispose()
        dbConn.Close()

        ' fill out array by Company Names for cboRightCombo combobox


        ' Load the Company Names into the ComboBox Control

        'getID = tRow(IDName).ToString
        For Each tRow In tTbl.Rows
            Return tRow(colName).ToString
        Next
    End Function
    Public Function verifyPass(ByVal user As String, ByVal pass As String) As Boolean

        ' Retrieve all the Company Names in the table
        On Error Resume Next
        Init()

        Dim connString As String = strConn
        Dim myConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection
        myConnection.ConnectionString = connString
        Dim SQLStr As String
        SQLStr = "SELECT username,password,right FROM User WHERE username='" & user & "' AND password ='" & pass & "'"

        ' create a data adapter 
        Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT username,password,right FROM User WHERE username='" & user & "' AND password ='" & pass & "'", myConnection)
        ' create a new dataset 
        Dim ds As DataSet = New DataSet
        ' fill dataset 
        da.Fill(ds, "user")

        If (ds.Tables.Count() > 0) Then
            Return True
        Else
            Return False
        End If
        ds.Dispose()
        myConnection.Close()


    End Function
    Public Function FillComboBox(ByVal cb As ComboBox, ByVal tableName As String, ByVal colName As String)

        ' Retrieve all the Company Names in the table
        On Error Resume Next
        Init()
        Dim dbConn As System.Data.OleDb.OleDbConnection = _
        New System.Data.OleDb.OleDbConnection(strConn)

        dbConn.Open()
        Dim DSet As New DataSet, SQLStr As String
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim dbAdaptr As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter

        Dim tRow As DataRow, tTbl As DataTable
        With dbAdaptr
            .TableMappings.Add("Table", tableName)
            SQLStr = "SELECT DISTINCT " & colName & "  FROM " & tableName & " ORDER BY " & colName
            cmd = New System.Data.OleDb.OleDbCommand(SQLStr, dbConn)
            cmd.CommandType = CommandType.Text
            .SelectCommand = cmd
            .Fill(DSet)
            .Dispose()
        End With

        DSet.AcceptChanges()
        tTbl = DSet.Tables.Item(0)
        DSet.Dispose()
        dbConn.Close()

        ' fill out array by Company Names for cboRightCombo combobox
        'cb.Text = ""
        'cb.Items.Clear()
        cb.BeginUpdate()

        ' Load the Company Names into the ComboBox Control
        For Each tRow In tTbl.Rows
            cb.Items.Add(tRow(colName).ToString)
        Next
        cb.EndUpdate()



    End Function

    Function FindFiles(ByVal path As String, ByVal SearchStr As String, _
       ByVal FileCount As Integer, ByVal DirCount As Integer, ByVal List1 As ListBox)
        Dim FileName As String   ' Walking filename variable.
        Dim DirName As String    ' SubDirectory Name.
        Dim dirNames() As String ' Buffer for directory name entries.
        Dim nDir As Integer      ' Number of directories in this path.
        Dim i As Integer         ' For-loop counter.
        Dim n As Long
        Dim fileformat As String
        Dim myInsertQuery As String
        On Error Resume Next


        ' If the connection string is null, use a default.
        OpenConn()

        On Error GoTo sysFileERR
        If Right(path, 1) <> "\" Then path = path & "\"
        ' Search for subdirectories.
        nDir = 0
        ReDim dirNames(nDir)
        DirName = Dir(path, vbDirectory Or vbHidden Or vbArchive Or vbReadOnly _
  Or vbSystem)  ' Even if hidden, and so on.
        Do While Len(DirName) > 0
            ' Ignore the current and encompassing directories.
            If (DirName <> ".") And (DirName <> "..") Then
                ' Check for directory with bitwise comparison.
                If GetAttr(path & DirName) And vbDirectory Then
                    dirNames(nDir) = DirName
                    DirCount = DirCount + 1
                    nDir = nDir + 1
                    ReDim Preserve dirNames(nDir)
                    'List1.Items.Add(path & DirName)  
                End If                           ' directories.
sysFileERRCont:
            End If
            DirName = Dir()  ' Get next subdirectory.
        Loop

        ' Search through this directory and sum file sizes.
        FileName = Dir(path & SearchStr, vbNormal Or vbHidden Or vbSystem _
        Or vbReadOnly Or vbArchive)

        While Len(FileName) <> 0
            FindFiles = FindFiles + FileLen(path & FileName)
            n = FileLen(path & FileName)
            FileCount = FileCount + 1
            ' Load List box
            'List1.Items.Add(FileName & vbTab & _
            '  FileDateTime(path & FileName))  
            fileformat = ""


            myInsertQuery = "INSERT INTO Sequences(FileName,TissueID,FileFormat,DateCreated,FilePath) VALUES ('" & FileName & "',0,'" & fileformat & "','" & CStr(FileDateTime(path & FileName)) & "','" & path & "');"

            Dim myCommand As New OleDb.OleDbCommand(myInsertQuery)
            myCommand.Connection = myConnection
            On Error Resume Next
            myCommand.ExecuteNonQuery()
            MsgBox("Loading sequence files into the Database... done!")
            FileName = Dir()  ' Get next file.   
        End While

        ' If there are sub-directories..
        If nDir > 0 Then
            ' Recursively walk into them
            For i = 0 To nDir - 1
                FindFiles = FindFiles + FindFiles(path & dirNames(i) & "\", _
                SearchStr, FileCount, DirCount, List1)
            Next i
        End If

AbortFunction:
        Exit Function
sysFileERR:
        If Right(DirName, 4) = ".sys" Then
            Resume sysFileERRCont ' Known issue with pagefile.sys
        Else
            MsgBox("Error: " & Err.Number & " - " & Err.Description, , _
             "Unexpected Error")
            Resume AbortFunction
        End If

    End Function



End Module
