VB.Net Question

Question

I am working on a VB.Net project and trying to get it to pull data from a database. I have the data base located in my bin folder with in the project but I do not know the exact path how to do it. The code that I am using is noted below

       Private Sub btnTotalTravelCost_Click(ByVal sender As System.Object, ByVal e As     System.EventArgs) Handles btnTotalTravelCost.Click
        'strsql is a sql statement that selects all the fields from the 
       'ApprovedTravelRequest table

        Dim strSql As String = "SELECT * FROM ApprovedTravelRequests "

        'strPath provides the database type and path of the Travel database.
        Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" & "Data     Source=c:\Travel.accdb"
        Dim odaTravel As New OleDb.OleDbDataAdapter(strSql, strPath)
        Dim DatCost As New DataTable
        Dim intCount As Integer
        Dim decTotalCost As Decimal = 0D

        'The DataTable name datCost is filled with the data
        odaTravel.Fill(DatCost)

        'The connection to the databsise is disconnected
        odaTravel.Dispose()

        For intCount = 0 To DatCost.Rows.Count - 1
            decTotalCost += Convert.ToDecimal(DatCost.Rows(intCount)("Travel Cost"))
        Next

        Me.lblTotalTravelCost.Visible = True
        Me.lblTotalTravelCost.Text = "The Total Approved Travel Cost is " & decTotalCost.ToString("C")




    End Sub
End Class

Will someone be able to explain how to do this? I am wanting to pull the data from the Bin File. I know the current location shown is incorrect.

Answer 1

Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" & "Data Source=c:\Travel.accdb"
Dim strSql As String = "SELECT * FROM ApprovedTravelRequests"
Dim decTotalCost As Decimal = 0.0
Using connect As New OleDb.OleDbConnection(strPath)
    Using command As New OleDb.OleDbCommand(strSql, connect)
        connect.Open()
        Using reader As OleDb.OleDbDataReader = command.ExecuteReader()
            If reader.HasRows Then
                While reader.Read()
                    decTotalCost += Convert.ToDecimal(reader(0))
                End While
            End If
            Me.lblTotalTravelCost.Visible = True
            Me.lblTotalTravelCost.Text = "The Total Approved Travel Cost is " & decTotalCost.ToString("C")
        End Using
    End Using
End Using

Answer 2

If the database is in the bin folder (with the executable), then you can do:

' Get the current directory (where the exe resides)
Dim folder As String = System.AppDomain.CurrentDomain.BaseDirectory
' Construct the full path to the DB (assuming it's with the exe)
folder = System.IO.Path.Combine(folder, "Travel.accdb")
' Build your connection string
Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" & "Data Source=" & folder