Fill ComboBox With

SQL Generated Data

 

    Public Sub FillDataList(ByRef objList As Object, ByVal strSQL As String)
        Dim con As New OleDb.OleDbConnection
        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
        For inc = 0 To MaxRecCount - 1
            objList.Items.Add(ds.Tables("MyData").Rows(inc).Item(0))
        Next inc
        con.Close()
    End Sub

 

 

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

 

 

Calculate Data Grid Totals

    Public Function GetGridTotal(objGrid As Object, intcol As Integer) As Double
        On Error GoTo Err_Fail
        Dim X As Integer
        GetGridTotal = 0
        Dim currValue As Double
        'Loop Through Datagrid
        For X = 0 To objGrid.RowCount - 1
            'Protect From NULL Values In Grid
            If (objGrid.Rows(X).Cells(intcol).value) IsNot DBNull.Value Then
                currValue = Val(WithCurrencySign(Trim(objGrid.Rows(X).Cells(intcol).Value), False))
                If currValue <> 0 Then
                    GetGridTotal = GetGridTotal + currValue
                End If
            End If
        Next X
        Exit Function
Err_Fail:
        GetGridTotal = 0
        MsgBox(ErrorToString)
    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

 

Create A New Incremented ID Number

 
    Public Function CreateNewSessionID() As Integer
        On Error GoTo Err_Fail
        'Open Database
        dbprovider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
        dbsource = "Data Source =" & System.AppDomain.CurrentDomain.BaseDirectory() & "/Training2020.mdb"
        con.ConnectionString = dbprovider & dbsource
        con.Open()
        'Select Highest Current Session ID
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        sql = "SELECT top 1 sessionID from tblSessionLog order by sessionID desc"
        da = New OleDb.OleDbDataAdapter(sql, con)
        ' Check For No Records
        da.Fill(ds, "SessionID")
        If ds.Tables("SessionID").Rows.Count = 0 Then
            CreateNewSessionID = 1
        Else
            'Increment highest ID And Return It
            CreateNewSessionID = ds.Tables("SessionID").Rows(0).Item("sessionID") + 1
        End If
        con.Close()
        Exit Function
Err_Fail:
        MsgBox("Error Creating New Session ID. (This Is A Critical Error)", MsgBoxStyle.Critical)
    End Function

 

 

SpaceFooter
Website_Design_NetObjects_FusionWeb
 Counter

www.CarlsPlace.co.uk

This Is A United Kingdom Site...