• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

VB.NET Database programming question

This connection string connects (should) to an Access database and its obvious here that it wont connect because VB.NET does not know where the underlying database resides.  What if the application is ported somewhere else like a different folder, would the application still be able to connect to the database.  Its obvious i would need a data source to specify where the underlying database is but what should i do to have the connection string dynamic in a such a way i can port the application without the need of the data source?
 
Dim ConnectionString _
            As String = "Provider=Microsoft.Jet.OLEDB.4.0;Integrated " & _
        "Security=SSPI;Persist Security Info=False;Initial " & _
        "Catalog=Students;Packet Size=4096;Workstation ID=Admin;" & _
        "Use Encryption for Data=False"

0
gbilios
Asked:
gbilios
1 Solution
 
checooCommented:
rather than hard coding the database path within the application, you can use a app.config file to store the location of the database. Then at runtime you can read that setting and build your connection string accordingly.
0
 
TRUENEUTRALCommented:
Why not set up an odbc datasource on the machine and connect to that.  Then if you mov the app to another folder it won't matter and if you move it to a different machine, you just need to set up the odbc datasource on that machine as well.
0
 
gbiliosAuthor Commented:
i use the oledb connection

if thats the case :  Data Source=localhost

the client does not want do any of that when its installed.  I'll look further in doing a config file.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
TRUENEUTRALCommented:
The client will have to do *something* when it is installed.  Much easier to do the odbc datasource than mess with a custom config file.
0
 
Hans LangerCommented:
Maybe you can use Access (JET) ODBC Driver
Like this,
' VB.NET
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
         "Driver={Microsoft Access Driver (*.mdb)};" & _
         "Dbq=c:\somepath\mydb.mdb;" & _  'you can specify this path when the user install the applicacion
         "Uid=Admin;" & _
         "Pwd="
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()

0
 
natlozCommented:
I normally would create a simple text file in the application folder that has a path...

eg..

C:\Temp\name.mdb

The program when loading reads the text file upon login and uses the string to create the final Connection string.

If the test for the database is invalid...prompt the user to search for the database file on the Computer/network and re-write the text file with the new location.

Hopefully this makes sense.
0
 
gbiliosAuthor Commented:
Imports System.Data.OLEDB

class .....
Private dataset1 As New DataSet
Private Connection1 As OleDbConnection
Private OleDbDataAdapter1 As OleDbDataAdapter
......
Private Sub btnLoad_Click(ByVal sender _
        As System.Object, ByVal e As System.EventArgs) _
            Handles btnLoad.Click

        dataset1 = New DataSet("dataset1")

        'Setup Connection string via the Jet OLEDB data provider

        Dim ConnectionString As String = _
        "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;" & _
        "Jet OLEDB:Database Locking Mode=1;Data Source=C:\SCC313\Project4\Students.mdb;" & _
        "Mode=Share Deny None;" & _
        "Jet OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:" & _
        "System database=;Jet OLEDB:SFP=False;persist security info=False;" & _
        "Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;" & _
        "Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;" & _
        "Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:" & _
        "Global Bulk Transactions=1"

        Connection1 = New OleDbConnection(ConnectionString)

        Dim Command1 As OleDbCommand = _
            New OleDbCommand("SELECT Name, Grade FROM tblStudents;")

        Command1.CommandType = CommandType.Text

        'Establish the connection to the database via the Connection's Open method
        Connection1.Open()

        Command1.Connection = Connection1

        OleDbDataAdapter1 = New OleDbDataAdapter

        OleDbDataAdapter1.SelectCommand = Command1

        OleDbDataAdapter1.Fill(dataset1, "tblStudents")
        DataGrid1.SetDataBinding(dataset1, "tblStudents")

        Command1.Dispose()

    End Sub

I assume you did a xml file that prompts the user to locate the database when the application is loaded.
0
 
gbiliosAuthor Commented:
i am very sorry for the delay.
This line :
Data Source=C:\SCC313\Project4\Students.mdb

Should be:

I put the Students.mdb database in the project's bin folder so it can open in the current directory.
Data Source=Students.mdb

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now