?
Solved

VB.NET Database programming question

Posted on 2004-10-12
9
Medium Priority
?
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
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.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 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