Solved

VB.NET Database programming question

Posted on 2004-10-12
9
200 Views
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"

0
Comment
Question by:gbilios
9 Comments
 
LVL 9

Expert Comment

by:checoo
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.
0
 
LVL 5

Expert Comment

by:TRUENEUTRAL
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.
0
 

Author Comment

by:gbilios
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.
0
 
LVL 5

Expert Comment

by:TRUENEUTRAL
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 10

Expert Comment

by:Hans Langer
ID: 12287737
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
 
LVL 7

Accepted Solution

by:
natloz earned 125 total points
ID: 12288644
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
 

Author Comment

by:gbilios
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
        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
 

Author Comment

by:gbilios
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

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now