1,105,416 Community Members

Connecting to an .mdf database

Member Avatar
BluePaper
Light Poster
31 posts since Mar 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

Through using Visual Studio to learn more about ASP.net and VB.net I would like to connect to a database. I create an .mdf database file but I've no idea how to connect to it (well, I have some code below...) that's not my only problem but I'll ask the next question in a moment. The code I'm using is shown below, am I missing something? Or using the wrong connection string?

Private Function sqlDBConnection() As Boolean
        'Set the variables
        Dim SQLConnectionString As String
        Dim SQLConnection As New SqlConnection()
        'Create the Connection String
        SQLConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=\App_Data\Overland.mdf;Initial Catalog=Overland;Integrated Security=True;User Instance=True"
        'Set the connection string into the SQL connection
        SQLConnection.ConnectionString = SQLConnectionString
        'Open up the connection
        SQLConnection.Open()
End Function

A similar connection string works ( "Data Source=LAMBDA\SQLEXPRESS;Initial Catalog=General;Integrated Security=True" ) with an actual database server (SQL Express 2005) but modifying it a bit doesn't work for an SQL Server file (.mdf in this case) so if you have any idea please help :)

Secondly as you can see I've got the function set as a Boolean, however I don't return anything and the .Open() function doesn't return anything. Therefore how can I check to see whether a connection is succesful or not before it breaks out into an error? I'd like to be able to catch it just incase.

Thanks for your time.

Member Avatar
green2go
Newbie Poster
12 posts since Mar 2008
Reputation Points: 1 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

Through using Visual Studio to learn more about ASP.net and VB.net I would like to connect to a database. I create an .mdf database file but I've no idea how to connect to it (well, I have some code below...) that's not my only problem but I'll ask the next question in a moment. The code I'm using is shown below, am I missing something? Or using the wrong connection string?

Private Function sqlDBConnection() As Boolean
        'Set the variables
        Dim SQLConnectionString As String
        Dim SQLConnection As New SqlConnection()
        'Create the Connection String
        SQLConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=\App_Data\Overland.mdf;Initial Catalog=Overland;Integrated Security=True;User Instance=True"
        'Set the connection string into the SQL connection
        SQLConnection.ConnectionString = SQLConnectionString
        'Open up the connection
        SQLConnection.Open()
End Function

A similar connection string works ( "Data Source=LAMBDA\SQLEXPRESS;Initial Catalog=General;Integrated Security=True" ) with an actual database server (SQL Express 2005) but modifying it a bit doesn't work for an SQL Server file (.mdf in this case) so if you have any idea please help :)

Secondly as you can see I've got the function set as a Boolean, however I don't return anything and the .Open() function doesn't return anything. Therefore how can I check to see whether a connection is succesful or not before it breaks out into an error? I'd like to be able to catch it just incase.

Thanks for your time.

You cannot directly 'Open' and .mdf file with Vb.NEt. You need to you an SQL server or MSDE. The second connection string you mention appears to connect to an SQL server, hence why it works.
As to your second question, If you are looking to catch exceptions, use the Try Catch method as demontrated below:

Try

SQLConnection.Open()

Catch Ex As System.Exception

MsgBox(ex.Message, MsgBoxStyle.Critical, "Error Information")

End Try

If you are looking to obtain the state of an SQL connection before you open it you can use this if statement:

If sqlConnection.State = ConnectionState.Closed Then

        sqlConnection.Open()

End If

Hope this helps.

Green2Go

Member Avatar
BluePaper
Light Poster
31 posts since Mar 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

If I can't directly open the .mdf file how can I access it? It's just something I'm looking into since I've no idea what it is compared to an actual database on a SQL server :/

Thank you so much with the Try/Catch method, I had seen it once or twice before in PHP but it's not used much by the people I speak to but it seems to come in useful in VB.net thanks :) and the second part will be useful to, saving me opening up multiple unneeded connections. thanks :)

Member Avatar
green2go
Newbie Poster
12 posts since Mar 2008
Reputation Points: 1 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
1
 

No problems for previous comment. You need to have an instance of SQL server express (http://msdn2.microsoft.com/en-gb/express/bb410792.aspx) free from Microsoft running on your local machine if you can't access an SQL server already setup on a network and/or are planning to deploy the application to locations without access to a server.

You then use a connection string to connect to the SQL server instance and read, write and query the data as you are already. See this link for connection strings for SQL Server 2005(http://www.connectionstrings.com/?carrier=sqlserver2005)
You can mount database files (.MDF) and other handy stuff using these commands.

Other than using a full blown server you could use an Access database to store the data, if there isn't lots of it, all that it requires is the correct drivers which I think are standard on most windows based machines anyway. You would have to convert the data from the SQL Server MDF file into an Access MDB file.

Green2Go

Question Answered as of 6 Years Ago by green2go
Member Avatar
BluePaper
Light Poster
31 posts since Mar 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

Thank you for your time and help, after some fiddling and with that site I managed to open up the database file, now I'm going to test whether I can run SQL queries on it and such. Thanks once again, I'll keep that site bookmarked :D

Member Avatar
dkerr
Newbie Poster
4 posts since Apr 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Can I query a SQL Server 2000 mydatabase.mdf file directly via SQL? If so, how?

Member Avatar
quangvo
Newbie Poster
3 posts since Jan 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Can I query a SQL Server 2000 mydatabase.mdf file directly via SQL? If so, how?

No sure if you can do this in sql server 2000. But you can do it in 2005.

Member Avatar
Devarajan.T
Newbie Poster
1 post since Nov 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

how to connect database in asp.net using app_data?


Regards,
T.Devarajan,
09003409226

shaikh abdul
Newbie Poster
1 post since Sep 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

please help me to connect my final project to database>>>>>>>

Member Avatar
Reverend Jim
Noli mentula
5,436 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 653 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

Please read what's in front of you. In big bold letters it states This question has already been solved: Start a new discussion instead. It was put there for a reason. If you aren't going to read what's in front of you then there is no point an anyone trying to post help. Also, you obviously didn't read this or you would have provided some details.

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article