Results 1 to 20 of 20

Thread: Can VB.net Express connect to SQL Server Express

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by jmcilhinney View Post
    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

  4. #4
    I'm about to be a PowerPoster! kleinma's Avatar
    Join Date
    Nov 2001
    Location
    NJ - USA (Near NYC)
    Posts
    23,373

    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.

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by kleinma View Post
    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

  6. #6
    I'm about to be a PowerPoster! kleinma's Avatar
    Join Date
    Nov 2001
    Location
    NJ - USA (Near NYC)
    Posts
    23,373

    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.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by kleinma View Post
    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.

  8. #8

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by kleinma View Post
    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?

  9. #9

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    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?

  10. #10

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Re: Can VB.net Express connect to SQL Server Express

    Thanks for all the great input. It is very helpful!

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by JosFKirby View Post
    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.

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by JosFKirby View Post
    Great work-around. I'll experiment with this. So would I need to install SQL local db to accomplish this?
    That is correct.

  13. #13

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    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?

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by techgnome View Post
    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

  16. #16

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    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....

  17. #17
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  18. #18

    Thread Starter
    Member
    Join Date
    Jun 2013
    Posts
    50

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by jmcilhinney View Post
    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

  19. #19
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  20. #20
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can VB.net Express connect to SQL Server Express

    Quote Originally Posted by JosFKirby View Post
    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:
    Quote Originally Posted by jmcilhinney View Post
    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
  •  



Click Here to Expand Forum to Full Width