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.

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

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 :)

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

commented: Very helpful, I'm sure this data will help for the future ;) +1

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

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

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.

how to connect database in asp.net using app_data?


Regards,
T.Devarajan,
09003409226

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.