Database location question 1

[GaryWilsonCPA]

I have the following connection string that works, however it is hardcoded to one location for my db.


The location of my database is below.
\DublinFundRaising\DublinFundRaising.mdb"";

Is there a way to change my programing to allows the database location to exist anywhere on the server, and allow a user to change location. Any sample code or links would be appreciated.





This is my working dataconnection string.

Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase L" & _
"ocking Mode=1;Jet OLEDBatabase Password=;Data Source=""\DublinFundRaising\Dub" & _
"linFundRaising.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Tran" & _
"sactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLE" & _
"DB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Pa" & _
"ssword=;Jet OLEDB:Create System Database=False;Jet OLEDBon't Copy Locale on Co" & _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLE" & _
"DB:Encrypt Database=False

 

[GaryWilsonCPA]

I found a good thread on this


thread796-922584

 

[PankajBanga]

Use a config file to store the location of your db. In ur app provide a utility (could be Open File Dialog box) which user can use to change the path to the db and amend the location to the config file.

Read the location from the config file before opening connection to the db.

 

[GaryWilsonCPA]

Being somewhat an newby at vb.net, I am not sure where to start on a config file. Would this be a new item you add to a project?

Any suggestions on sample code?

 

[PankajBanga]

Adding a config file is quite simple:

Click Project--> Add New Items
under Templates Pane click Application Configuration File.

This will add a App.config file in ur project. The config file is a xml file. Under <configuration></configuration> tag and <appSettings></appSettings> tag, as follow:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="DbPath" value="C:\Temp" />
</appSettings>
</configuration>

when u compile ur application a file named YourApplicationName.exe.config is automatically added to your bin folder. You can read and write elements in this file.

There are helps of article available on web

http://www.codeproject.com/dotnet/config.asp

http://msdn.microsoft.com/library/d...en-us/cpref/html/frlrfSystemConfiguration.asp

 

[GaryWilsonCPA]

Great article, but i still have problem with data connection. My error message says that it can not find datafile - do you see anything that is screwup?


This is what i am placing on the form
Dim DbPath As String = System.Configuration.ConfigurationSettings.AppSettings("DatabasePath")

Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase L" & _
"ocking Mode=1;Jet OLEDBatabase Password=;Data Source=""" & DbPath & """;Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Tran" & _
"sactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLE" & _
"DB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Pa" & _
"ssword=;Jet OLEDB:Create System Database=False;Jet OLEDBon't Copy Locale on Co" & _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLE" & _
"DB:Encrypt Database=False"


MY XML FILE

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appsettings>
<add key="Databasepath" value= "C:\DublinFundRaising\DublinFundRaising.mdb>"
</appsettings>
</configuration>

 

[GaryWilsonCPA]

Found good article on this

http://www.ondotnet.com/pub/a/dotnet/2003/01/27/ztd.html

The app in the article can be reconfigured to a nice datastring.

XML FILE LOOKS LIKE THIS...

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="teststring" value="D:\DublinFundRaising\DublinFundRaising.mdb" />
</appSettings>
</configuration>

DATA CONNECTION LOOKS LIKE THIS

REPLACE THIS

data source = ""D:\DublinFundRaising\DublinFundRaising.mdb""


WITH THIS
Data Source=""" & (o.ToString()) & """

Dim settings As New System.Configuration.AppSettingsReader
Dim o As Object = settings.GetValue("teststring", GetType(String))
MessageBox.Show(o.ToString())
TextBox1.Text = (o.ToString())

Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase L" & _
"ocking Mode=1;Jet OLEDBatabase Password=;Data Source=""" & (o.ToString()) & """;Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Tran" & _
"sactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLE" & _
"DB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Pa" & _
"ssword=;Jet OLEDB:Create System Database=False;Jet OLEDBon't Copy Locale on Co" & _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLE" & _
"DB:Encrypt Database=False"