Database

  
  '############################################################

    Public Function MarkAsAlreadyPacked(ByVal strOrderNum As String) As Boolean
        'Make Required Declarations...
        Dim sql As String
        Dim con As New OleDb.OleDbConnection
        Dim dbprovider As String
        'Dim dbsource As String
        dbprovider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
        con.ConnectionString = dbprovider & dbsource
        Dim upd As New OleDb.OleDbCommand
        con.Open()
        sql = "UPDATE tblDeliveryAdds SET [AlreadyPacked]=TRUE WHERE OrderNum = '" & strOrderNum & "'"
        Try
            upd = New OleDb.OleDbCommand(sql, con)
            upd.ExecuteNonQuery()
            con.Close()
            upd.Dispose()
            MarkAsAlreadyPacked = True
        Catch ex As Exception
            MarkAsAlreadyPacked = False
            MessageBox.Show("Couldnt Update Order Packed Status..." & Err.Description, "Caution")
            con.Close()
            upd.Dispose()
        End Try
    End Function

  '############################################################

Run An Update Query

Retrieve Boolean Value

  
  '############################################################
  
    Public dbsource = "Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory() & "/dbSales.mdb"


    Public Function IsWorkingDay(strDayName As String) As Boolean
        Dim con As New OleDb.OleDbConnection
        Dim dbprovider As String
        'Dim dbsource As String
        dbprovider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
        con.ConnectionString = dbprovider & dbsource
        con.Open()
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        sql = "select * from tblSettings Where DayName = '" & strDayName & "'"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "myData")
        IsWorkingDay = ds.Tables("myData").Rows(0).Item("Working") = True        
        con.Close()
    End Function

  '############################################################

Save Form Data To Database

Simple Loop Through Recordset

  
  '############################################################
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = dbprovider & dbsource
        con.Open()
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim strSQL As String = "SELECT  tblSessionLog.Duration, tblSessionLog.TrainingType, tblSessionLog.SessionDate FROM tblSessionLog WHERE tblSessionLog.SessionDate >now()-366"
        da = New OleDb.OleDbDataAdapter(strSQL, con)
        da.Fill(ds, "MyData")
        Dim MaxRecCount As Integer
        Dim inc As Integer
        inc = -1
        MaxRecCount = ds.Tables("MyData").Rows.Count
        Dim intStrength, intAerobic As Integer
        For inc = 0 To MaxRecCount - 1

                    intStrength = intStrength + ds.Tables("MyData").Rows(inc).Item("Duration")

        Next
        con.Close()

  '############################################################

Get Total Of Specified Field From SQL Recordset

  
  '############################################################
     Public Function ReturnRecordValueSUM(strSQL As String, strField As String) As Long
        On Error GoTo Err_Fail
        'Open Database
        Dim con As New OleDb.OleDbConnection
        Dim dbprovider As String
        Dim dbsource As String
        dbprovider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
        dbsource = "Data Source =" & "G:/My Applications/Training/Training2020.mdb"
        con.ConnectionString = dbprovider & dbsource
        con.Open()
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        sql = strSQL
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "MyData")
        Dim MaxRecCount As Integer
        Dim inc As Integer
        inc = -1
        MaxRecCount = ds.Tables("MyData").Rows.Count
        Dim currTotal As Double = 0

        For inc = 0 To MaxRecCount - 1
            'Dont Add Empty Options !!!
            If (ds.Tables("MyData").Rows(inc).Item(0)) IsNot DBNull.Value Then
                currTotal = currTotal + ds.Tables("MyData").Rows(inc).Item(strField)
            End If
        Next inc
        ReturnRecordValueSUM = currTotal
        con.Close()
        Exit Function
    Err_Fail:
        ReturnRecordValueSUM = 1
        con.Close()
    End Function
  '############################################################

Retrieve Value By ID Field Search

  
  '############################################################
    Public Function GetTrackingNumber(ByVal strOrderCode As String) As String
        Dim con As New OleDb.OleDbConnection
        Dim dbprovider As String
        'Dim dbsource As String
        dbprovider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
        con.ConnectionString = dbprovider & dbsource
        con.Open()
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        sql = "select TrackingRef from tblDeliveryAdds Where OrderNum = '" & strOrderCode & "'"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "tblTracking")
        'Check For Null Return
        GetTrackingNumber = ds.Tables("tblTracking").Rows(0).Item("TrackingRef")
        con.Close()
    End Function
  '############################################################

  
  '############################################################
  
    Public Function SaveHealthData() As Boolean
        'Open Database Connection
        Dim sql As String
        Dim con As New OleDb.OleDbConnection
        dbprovider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
        dbsource = "Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory() & "/Training2020.mdb"
        con.ConnectionString = dbprovider & dbsource
        con.Open()
        'Declare Variables
        Dim intWeight, intHeartSystolic, intHeartDiastolic, intPulseRate, intBloodOxygen, intPalpitations As Integer
        Dim txtHealthNotes As String
        Dim dteSessionDate As Date = Date.ParseExact(Me.txtHealthDate.Text, "dd/MM/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
        intWeight = Val(Me.txtWeight.Text)
        intHeartSystolic = Val(Me.txtHeartSystolic.Text)
        intHeartDiastolic = Val(Me.txtHeartDiastolic.Text)
        intPulseRate = Val(Me.txtPulseRate.Text)
        intBloodOxygen = Val(Me.txtBloodOxygen.Text)
        txtHealthNotes = Me.txtHealthNotes.Text
        intPalpitations = Me.txtPalpitations.Text
        'Save Data
        Dim upd As New OleDb.OleDbCommand
        Try
            sql = "Insert into tblHealth values ('" & dteSessionDate & "', " & intWeight & ", " & intHeartSystolic & ", " & intHeartDiastolic & ", " & intPulseRate & ", " & intBloodOxygen & ", '" & txtHealthNotes & "', " & intPalpitations & ")"

            upd = New OleDb.OleDbCommand(sql, con)
            upd.ExecuteNonQuery()
            'MessageBox.Show("SAVE SUCCESSFUL", "Update Complete")
            con.Close()
            upd.Dispose()
            SaveHealthData = True
        Catch ex As Exception
            SaveHealthData = False
            MessageBox.Show("SAVE FAILED : " & Err.Description, "Caution")
            MsgBox(Err.Description)
        End Try
    End Function

  '############################################################

www.CarlsPlace.co.uk