Access SQLServer Express Using ADODB

Hi,

How should I do to access a table in SQLServer Express database using VB.NET and ADODB?

Thanks!

Hi Ciro,

I would recommend using the SqlConnection object in VB.Net as it gives you all the new hotness of SNAC rather than MDCA. SNAC supports both ODBC and OLEDB. Here is an example that I created almost completely using the Insert Snippet functionality in VS 2005. (i.e. You hardly need to know how to code these days.)

Code Snippet

Imports System.Data.SqlClient

Module Module1

Sub Main()

Dim conn As New SqlConnection()

conn.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorksLT;Persist Security Info=True;Integrated Security=True"

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.Text

cmd.CommandText = "SELECT * FROM SalesLT.Customer"

cmd.Connection = conn

Dim reader As SqlDataReader

Dim previousConnectionState As ConnectionState = conn.State

Try

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

reader = cmd.ExecuteReader()

Using reader

While reader.Read

' Process SprocResults datareader here.

Console.WriteLine(reader.GetValue(3) & " " & reader.GetValue(5))

End While

End Using

Finally

If previousConnectionState = ConnectionState.Closed Then

conn.Close()

End If

End Try

End Sub

End Module

This gives you full access to all the SQL 2005 features.

If you just can't use SNAC, then the ADODB code for accessing SQL Express has not changed, just reference the ADODB library in your project and write standard ADODB code. You can find all kinds of sample connection strings at http://ConnectionStrings.com, where they suggest the following for OLEDB connections:

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

One you have that you do the whole

Code Snippet

Dim conn As New ADODB.Connection

And you're on your way...

Mike

|||

Hi Mike,

Thank you for your reply.

I realy need to use ADODB, because in my project, I need a Recordset object.

I visited http://connectionstrings.com/ (it′s realy a nice site) and a I found the string connection:

"Driver={SQL Native Client};" & _

"Server=.\SQLExpress;" & _

"AttachDbFilename=c:\PathToMyDatabaseFile\MyDataBasefile.mdf;" & _

"Database=dbname;" & _

"Trusted_Connection=Yes;"

The first time that I run the code, it worked nice, but when I tried again, I received the follow error message:

[Microsoft][SQL Native Client][SQL Server]Cannot open database "dbname" requested by the login. The login failed.

I tried to remove "Database=dbname;" but it didn′t work too:

"[Microsoft][SQL Native Client][SQL Server]Cannot open user default database. Login failed."

VB.NET and SQLServer Express are a new word to me, and I don′t have any idea about how to solve this problem.

|||

I tried new experiments:

Now, I tried to access the database file using VB6 and VB.Net at the some time.

The first access worked properly, but for second one, I received the following message:

[Microsoft][SQL Native Client][SQL Server]Unable to open the physical file "C:\MyFilePath\MyFileName.mdf". Operating system error 32 (The file has been used in another process"

As I said before, SQLSERVER EXPRESS is a new world fo me, and I don't have any idea about how to solve this problem.

Thanks for any kind of help.

|||

Hi Ciero,

First off, you mentioned that you really need to use ADODB, but then you indicated that you're using the SQL Native Client anyway, which is different. Anyway...

You're getting messed up because you're using both AttachDbFilename and Database and things are getting mixed up in the translation. You should probably use only one of these:

Use AttachDbFilename if you are using a user instance by placing your database file directly in your project and letting VB.NET create the connection string for you. Use Database is you are working with a database that is already created in your SQL Express instance and you're just connecting to it.|||Thank you, Mike!