-
Aug 27th, 2014, 10:39 AM
#1
Thread Starter
Member
Can VB.net Express connect to SQL Server Express
I have installed SQL Server Express on a server, and have installed VB.net 2010 Express on my local client. I am writing a program that needs to connect to the database in SQL Express, but am having problems connecting. I am trying to establish the connection and receive the following error:
---------------------------
Microsoft Visual Basic 2010 Express
---------------------------
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
---------------------------
OK
---------------------------
Am I trying something that does not work. Can VB.net express connect to SQL Server Express?
Any other thoughts on establishing the connection.
The program I am writing is simple and I would like to use bound data controls, and the solution needs to access a server based database.
ANy help would be appreciated.
Thanks
JOe
-
Aug 27th, 2014, 10:44 AM
#2
Re: Can VB.net Express connect to SQL Server Express
The answer is "yes". Assuming that your connection string is correct, the first thing to check is that you have configured your instance to allow remote connections, which is not the default.
-
Aug 27th, 2014, 11:26 AM
#3
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by jmcilhinney
The answer is "yes". Assuming that your connection string is correct, the first thing to check is that you have configured your instance to allow remote connections, which is not the default.
I think the SQL instance is correctly configured to allow remote connections. Mixed mode authentication is enabled on the SQL server and I'm using the standard port 1433. No firewalls exist between the SQL server host and the workstation. I have verified that we can connect to the same instance by creating a ODBC connection in Windows and the "Test Connection" passes successfully, but still cannot connect via VB.net.
Here's a snippet of the connection string I'm using:
Dim connetionString As String = "Data Source=\\(servername)\SQLEXPRESS;Initial Catalog=QLTraffic;User ID=sa;Password=(password)"
Dim cnn As SqlConnection = New SqlConnection(connetionString)
Try
cnn.Open()
MsgBox("Connection Open ! ")
cnn.Close()
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try
-
Aug 27th, 2014, 11:50 AM
#4
Re: Can VB.net Express connect to SQL Server Express
I never have the leading slashes \\ when specifying a server name in the connection string as the data source. I can't test right now to see if that even matters, but I know that I never use it in working connection strings.
-
Aug 27th, 2014, 03:52 PM
#5
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by kleinma
I never have the leading slashes \\ when specifying a server name in the connection string as the data source. I can't test right now to see if that even matters, but I know that I never use it in working connection strings.
Yes. I have removed the \\ and now have access to the database table. I've put in a simple loop to verify that it is connecting to the data!
So I can reach the data programmatically, but not through the Add New Data Source wizard. Below are my steps with the wizard:
1) Selecting Microsoft SQL Server Database file as the "Data Source" type.
2) I browse to the database file. It is on my "Z:" drive. I have tried the mapped drive as well as the fully qualified mapping to the file (both result in the same error shown in my original post)
3) I use the SQL Server Authentication (which is successful with my test script)
4) I select Test Connection, and after a delay the above-mentioned error is returned.
One difference that I note from my selections to what I have seen in the Murach's "Visual Basic 2010" book that I have is that the wizard in the book shows a database model of "Dataset" where mine shows "Database". I am a novice at this so I'm not sure what is relevant or not. Sorry about that.
I would really like to use bound controls if at all possible.
Joe
-
Aug 27th, 2014, 05:03 PM
#6
Re: Can VB.net Express connect to SQL Server Express
Are you selecting "SQL Server Database" or "SQL Server Database File" when making this new connection in the data source wizard? The former would be what you want when connecting to an already existing instance of a SQL Server, that already has the MDB file attached you want to access. The latter would be when you want to connect a disconnected MDF database file to an instance of SQL Server Express you have running on your local machine.
-
Aug 27th, 2014, 05:51 PM
#7
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by kleinma
Are you selecting "SQL Server Database" or "SQL Server Database File" when making this new connection in the data source wizard? The former would be what you want when connecting to an already existing instance of a SQL Server, that already has the MDB file attached you want to access. The latter would be when you want to connect a disconnected MDF database file to an instance of SQL Server Express you have running on your local machine.
As Matt says, although I'm not sure that option is available in VS Express. If not then what you could do is create a local data file with the same schema to generate the Data Source and then change the connection string in the config file to connect to your remote instance at run time. It's a bit of a pain but, as long as you're not changing the schema too much, not a big deal.
-
Aug 27th, 2014, 06:38 PM
#8
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by kleinma
Are you selecting "SQL Server Database" or "SQL Server Database File" when making this new connection in the data source wizard? The former would be what you want when connecting to an already existing instance of a SQL Server, that already has the MDB file attached you want to access. The latter would be when you want to connect a disconnected MDF database file to an instance of SQL Server Express you have running on your local machine.
That makes sense. So the data source options don't include SQL server database in express vb. is there a way to get this functionality or is it a limitation of express?
-
Aug 27th, 2014, 06:40 PM
#9
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
Great work-around. I'll experiment with this. So would I need to install SQL local db to accomplish this?
-
Aug 27th, 2014, 06:40 PM
#10
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
Thanks for all the great input. It is very helpful!
-
Aug 27th, 2014, 06:43 PM
#11
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by JosFKirby
That makes sense. So the data source options don't include SQL server database in express vb. is there a way to get this functionality or is it a limitation of express?
I don't have VS Express handy to test if that is indeed the case but I seem to remember that, for older Express editions at least, it was the case. That is a limitation and not something that can be changed, as far as I'm aware.
-
Aug 27th, 2014, 06:43 PM
#12
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by JosFKirby
Great work-around. I'll experiment with this. So would I need to install SQL local db to accomplish this?
That is correct.
-
Aug 28th, 2014, 10:46 AM
#13
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
Ok. SQL LocalDB installed and mirror database set up fine with Management Studio.
I've run into the following problems:
1) When attempting to connect to the database, I receive a "file is in use" error.
2) I stopped SQL Server and SQL Brower services and the "File is in use" error goes away. However, I get the same authentication error that I had when attempting to access the network database.
Any thoughts?
-
Aug 28th, 2014, 11:38 AM
#14
Re: Can VB.net Express connect to SQL Server Express
You CAN connect to the SQLExpress using VSExpress, IF it's on LocalHost... but it won't do it for remote servers. NOTE-this is actually a limitation of the WIZARD or template that runs as part of that process... other than that, there is no limits or other crippling functionality. So you could set up a local instance of SQL Express and your database... just to get through the wizard. Then change the connection string at run-time to the main server.
-tg
-
Aug 28th, 2014, 11:44 AM
#15
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by techgnome
You CAN connect to the SQLExpress using VSExpress, IF it's on LocalHost... but it won't do it for remote servers. NOTE-this is actually a limitation of the WIZARD or template that runs as part of that process... other than that, there is no limits or other crippling functionality. So you could set up a local instance of SQL Express and your database... just to get through the wizard. Then change the connection string at run-time to the main server.
-tg
I have set up the localdb with my database, but the wizard does not work. In a youtube video of this exact process, I see they are using a Data Source of "Microsoft SQL Server." This does not show up in my wizard. Is there a reference or service that I need to add to my VS Express to have this present? In the video, the man states that this is the source used for the localDB file.
Joe
-
Aug 28th, 2014, 12:01 PM
#16
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
It I purchase Visual Studio 2010 Professional version do these issues go away? For this one program I would rather not purchase it, but if that will make it work then....
-
Aug 28th, 2014, 10:05 PM
#17
Re: Can VB.net Express connect to SQL Server Express
You're taking something very easy and making it hard. You shouldn't be using Management Studio for this. The point is that VS Express can connect to a SQL Server data file but not an attached database, so creating an attached database is not of any use. Add a new item to your project and select the Server-based Database item template. That will add an MDF data file directly to your project and generate a connection string that uses AttachDbFilename rather than Initial Catalog. You then build the database schema in VS. When you run your project, the data file will be attached to your local instance on demand. If you change the connection string to specify a remote instance and an Initial Catalog then the app will connect to that instance and database instead of the local instance and data file. If the schema are the same, it will just work.
-
Aug 29th, 2014, 06:33 AM
#18
Thread Starter
Member
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by jmcilhinney
You're taking something very easy and making it hard. You shouldn't be using Management Studio for this. The point is that VS Express can connect to a SQL Server data file but not an attached database, so creating an attached database is not of any use. Add a new item to your project and select the Server-based Database item template. That will add an MDF data file directly to your project and generate a connection string that uses AttachDbFilename rather than Initial Catalog. You then build the database schema in VS. When you run your project, the data file will be attached to your local instance on demand. If you change the connection string to specify a remote instance and an Initial Catalog then the app will connect to that instance and database instead of the local instance and data file. If the schema are the same, it will just work.
Not sure what you just said; but I would like to understand. To clarify, I used Management Studio to construct the database only. I would like to use bound controls and the database needs to be shared across several users in the environment. Does your solution accomplish this? If so, I will spend the time to research what you are describing.
Thanks
-
Aug 29th, 2014, 06:46 AM
#19
Re: Can VB.net Express connect to SQL Server Express
Do you want to use LOCALDB?
I thought you wanted to connect to a "server instance" of MS SQL server?
-
Aug 29th, 2014, 06:54 AM
#20
Re: Can VB.net Express connect to SQL Server Express
Originally Posted by JosFKirby
Not sure what you just said; but I would like to understand. To clarify, I used Management Studio to construct the database only. I would like to use bound controls and the database needs to be shared across several users in the environment. Does your solution accomplish this? If so, I will spend the time to research what you are describing.
Thanks
What I just said was how to do this:
Originally Posted by jmcilhinney
what you could do is create a local data file with the same schema to generate the Data Source and then change the connection string in the config file to connect to your remote instance at run time.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|