Solved

VB.NET Database programming question

Posted on 2004-10-12
9
204 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.net VBA word safemode 1 37
Round a string to two digits 12 30
Help with creating multiple xml files from one xml file using VB.NET 6 37
More on Time zones in vb 2010 12 37
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

16 Experts available now in Live!

Get 1:1 Help Now