VB.NET Database programming question

Posted on 2004-10-12
Medium Priority
Last Modified: 2010-04-23
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"

Question by:gbilios

Expert Comment

ID: 12285922
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.

Expert Comment

ID: 12286179
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.

Author Comment

ID: 12286215
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.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.


Expert Comment

ID: 12287140
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.
LVL 10

Expert Comment

by:Hans Langer
ID: 12287737
Maybe you can use Access (JET) ODBC Driver
Like this,
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;" & _
oODBCConnection = New Odbc.OdbcConnection(sConnString)


Accepted Solution

natloz earned 500 total points
ID: 12288644
I normally would create a simple text file in the application folder that has a path...



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.

Author Comment

ID: 12294900
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

        Command1.Connection = Connection1

        OleDbDataAdapter1 = New OleDbDataAdapter

        OleDbDataAdapter1.SelectCommand = Command1

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


    End Sub

I assume you did a xml file that prompts the user to locate the database when the application is loaded.

Author Comment

ID: 12636624
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


Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Article by: Jorge
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question