none
Cannot connect to SQL 2008 Express from VB 2008 Express

    Question

  • I installed VB 2008 Express with SP1 and SQL 2008 Express. However, I cannot link to SQL 2008 Express from  VB 2008 Express. Please Help.

     

    Since I'm new to this environment, I may not even be asking the right question. A brief background: I did some minimal work with VB 2008 Express - creating an sdf database and some forms that interacted with the database. I also did some minimal work with SQL 2008 Express, running some queries against the AdventureWorks Database. When I tried to create a form in VB 2008 Express that accessed the AdventureWorks Database in SQL 2008 Express, I have been unable to figure out how to create the needed connection.

    Wednesday, November 26, 2008 3:07 AM

All replies

  • Use a SQLConnection to connect to the database.  A SQLConnectionStringBuilder can help you create your connection string.  Then use SQLCommand's and SQLDataReader's (or other objects) to process the data.  Let me know if you have any specific questions and I can provide more detailed examples.

     

    HTH....

    • Proposed as answer by deman Friday, March 19, 2010 12:57 AM
    Wednesday, November 26, 2008 6:07 AM
    Moderator
  • AA - Your proposed solution appears to involve the writing of VB code. If I were to go that direction, wouldn't I miss out on the ability to use the drag and drop features for creating forms in VB 2008 Express? I was hoping to find a solution that permits me to use the Data Sources Window to "Add a New Data Source". Did I misunderstand your response? If so, I guess I need a more detailed step by step road map. If not, I still need help to gain access to the db via the Data Sources Window.

    Wednesday, November 26, 2008 4:02 PM
  • This is exactly my situation as well.  I prototyped my application using an Access 2007 database thinking it would be relatively trivial to make it work with 2008 SQL Express but I'm unable to get any further.  I've finally gotten my database converted to SQL Express running on my machine, but there doesn''t seem to be anyway to connect to it in VB 2008 Express.

    • Proposed as answer by kumaresan_m Saturday, April 16, 2011 6:26 PM
    Thursday, December 11, 2008 6:06 PM
  • Hi Folks,

     

    I don't work with the GUI tools very much, so I asked some of the other moderators and answerers on the forums if they had any resources that might be of assistance.

     

    Pappy Normand suggested the following links:

     

     Pappy Normand wrote:

    The posters are beginners in VB. They have used VBA ( for ACCESS ) so i suggest :
    - this link from Microsoft
    title : Data samples for Visual Basic ( they are lucky : it is for Visual Studio 2008 )
    It is really simple but for beginners, that would be a good beginning way
     
    - a book Microsoft Visual Basic
    Microsoft Visual Basic Express 2005 : Build a program ( around $30 )
    Easy to read excellent for beginner
     
    Programming ADO.Net 2.0 step by step
    with examples in VB and VC# for beginners
     
    Programming ADO.Net 2.0 Core reference : much complex but i used this book during 3 years and 90% what i know about ADO.Net 2.0, i learnt in this book
     
    I hope that they help.
    Thursday, December 11, 2008 9:26 PM
    Moderator
  • Aaron,
      Thanks for the effort.  I'm not using VBA, but VB Express 2008.  I'll happily admit I'm a beginner programmer and I'm sure that there's all kind of things I'm not doing correctly, but I'm not using the GUI really except to add the data source.  With the Access database, I'm using the generated TableAdapters and DataSets to manipulate data.  It's been quite a learning experience.  I have the ADO.NET core reference (1.0) and agree that it's a great reference.
      I did a few more hours of research today, and I think I found the answer.  The SQL Express database can only be accessed with the VB Express data adapter if it is detached from the SQL server.  Once I did that, I could use the VB Express function to access the database and the TableAdapters and such were regenerated (using SQL DB).  The problem is that puts me back into having a database file on a file share somewhere on the network which is what I'm trying to avoid.
      I can't find the post that I found anymore, but it suggested that the only resolution was to buy the full version of VB and that limitation wasn't in it.  I suppose I could connect to the database using a different data provider/connector, but I don't know how to get to the dataset model that the program itself is built around.

    David
    Thursday, December 11, 2008 11:26 PM
  • Hello,

    If you are using VB Express 2008 , be cautious : in the connection to your database, it is better to block the connection string with user instance = false.
    Recreate a connection to your datasource with a new name and force user instance = false and i think the problem will disappear.
    user instance is a feature specific to Express Edition of Sql Server and is rejected by other Sql Server Editions.
    A program using user instance will not work versus a Standard ( for example ) Sql Server Edition, so avoid it , you will have less problems ( moreover the use of user instance is incompatible with connection to a remote server )

    Have a nice day

    PS: i have succeeded to access to a non detached database with my VC# Express 2005,VC# Express 2005 and VS Standard 2008 without problem but you have to modify the property user instance in the connection dialogbox.
    Please remember to click 'Mark as Answer' on the post that helped you. Unmark if it provides no help
    Friday, December 12, 2008 10:16 PM
    Moderator
  • I might be new to here but hey to everybody. I have successfully completed this process by simply changing the connection string in the dbml file in my project. First use this small script.

    Option explicit
    dim objwshell

    set objwshell = createobject("Wscript.shell")

    objwshell.run "%comspec% /k C:\SqlMetal.exe /server:[servername/instance] /database:[database name] /dbml:[new dbml filename and path]"

    after running this script you should see that a new dbml file has been created.

    Now go to the dbml file on the drive you created it on. then open the file up. a different visual studio should open up. after this got to the server explorer and go to datasources. after going there try to add a new database. don't literally add one we just want the connection string it generates. now select microsoft sql server as database type then select you server instance after then go down to settings or advanced i believe and you should see you connection string at the bottom now copy that string make sure you have defined all passwords and usernames so it can generate the connection string with the correct rights. now that ou have the connection string copied now go to you application open up the dbml file you are using and determine what connection setting it is using. after getting that go to your project properties and go to settings then you should see you connection string clear out the box with the connection string and paste the one you got in memory and BAMMMM!!!!! you are now using a connection string to a network server.


    if you need any detailed explaination just let me know.


    Peace and hair grease LOL
    Thursday, January 22, 2009 9:03 PM
  • is it really possible to connect visual basic 2008 express edition to a sql server 2008 express edition database?..

    can someone please post a sample  code on how to connect sql server 2008 database to a textbox..
    i'm trying to make a login form but cant proceed because of unsuccessful connection to the database..

    PLEASE HELP!! :((
    Tuesday, March 24, 2009 1:23 AM
  • Yes this works and I do it all the time. VS Express products support attaching a database on the fiy at run time using a feature called User Instances as well as connecting to the parent instance of SQL Server and using a database that is already attached there, i.e. for multiuser access. Connecting to the parent instance from VS Express products requires that you open the Advanced Properties of the Connection dialog for your Data Connection and change the User Instance property to False as Papy indicates.

    Since everyone here seems to be struggling with getting data onto forms, particularly for enabling drag and drop type behavior I'm going to point you to the excellent video series by Beth Massi from the VS team at http://msdn.microsoft.com/en-us/vbasic/bb466226.aspx. You'll find a number of short video demonstratons of using the numerous data binding features of VS in the 'Forms Over Data' segments. In addition to seeing specific VS features demonstrated, at least a few of the videos will show how she creates the Data Source, which is the key to getting the automatic binding.

    Regards,
    Mike


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 25, 2009 2:56 PM
    Moderator
  • Hi Aaron
    I'm researching the net to find why is that I can't connect from VB 2008 xpr to a database I built on SQL 2008 xpr.
    I'm the owner of the computer and the owner of the SQL database, however, When I try to add a new data source that I created that refers to the database, VB answers that I have not enough permission to open the data source.
    I'm a programmer that have a lot of experience working with access databses an applications.  I'm trying to get into VB 2008 (I did something also with VB6) and so far, I have not been seccesfull

    Hope you can help

    Michel
    Sunday, November 01, 2009 8:00 PM
  • Hey Mike,

    Your response to the problem, "I cannot connect to the SQL Server while using VB 2008 Express" describes where I am right now. I am quite new to this programming language; My problem: Beth Massi's videos I downloaded, and tried the first, "How to create a DB"; while following her (video) example in VB 2008 Express, I chose View, then Database Explorer, right click on data connections, where Beth suggest to click on "Create new SQL Server database." This is greyed out. Most of the responses I read are going way over my head, about f.e. named pipes, etc. I re-installed VB 2008 Express edition 3 times, then being at the end of my rope, I formatted my HD in the hope that a clean install would solve the problem, but the situation is unchanged. A while ago I bought the book, "Build a program now" from MS, VB 2008, where the server is the 2005 edition. Never any problem. Do you know what I can do? I am very discouraged. Thanks for answering!

    Leen
    Friday, November 20, 2009 9:28 PM
  • Hi, I am also a beginner and I have downloaded VB 2008 express and I am having a similar problem.
    I click on ADD NEW DATA SOURCE (I get three options - Database, Service, Object)
    I click on DATABASE - (Asks for Connection - connection dropdown is blank and connection string is blank)
    I click on NEW CONNECTION - at this point the window disappears and nothing further happens.

    I have uninstaled and re-installed but still the same.
    I am stumped.

    All help is appreciated
    Tuesday, November 24, 2009 11:25 PM
  • I met this problem too. I guess it may be OS problem. I have a visa machine. visa has lots of issue.
    Wednesday, November 25, 2009 5:26 AM
  • I had the same problem getting vb 2008ex to find sql 2008 express. Finally, went to the vb2008 tools pulldown, options, expanded database tools, picked database connections, and type in "sqlserver" (without the quotes) in the SQL server intance name.
    It worked.
    Leaving it blank as suggested above did not work.
    Hope this help someone out there so they won't waste a day like I did.
    Good thing MS doesn't make cars.

     

    Monday, January 11, 2010 9:54 PM
  • I have been looking around at this issue as well and found the following that worked for what I was wanting to do.

    Had made custom database in SQL Server 2008 Express

    Wanted to connect to database in Visual Basic 2008 Express

    Issue that I had was that for the life of me it would not connect, nor find the database that I was searching for.

    etbetz then helped me realize my mistake. Which, from someone who is new to this isnt saying much but... here is what information I found that helped me resolve this.


    Following etbetz instructions I went into the VB2008 Tools, Options, Expand the Database Tools option, Database Connections, but instead of typing "sqlserver" you type the exact name of the SQL server you created with Server 08 Express. It initially had SQLEXPRESS as mine, but simply by changing it to the exact name of the server I was on, it seemed to work. Initially it said that my database was in use by another program, so I stopped and restarted the server and it worked.

    Hopefully this helped someone who is new and confused like I was, as I know most people who have worked with this stuff would have been able to fix this option from the get go and never have had such an issue.
    Thursday, January 28, 2010 7:35 PM
  • I'm not seeing what you and others are  under Tools, Options. I have no choice called Database Tools option. You do have VB2008 Express right? My choices under options are  Environment, Projects and Solutions, Text Editor Basic, Windows forms designer.

    After reading all of the above stuff maybe somene can just answers these...

    1)VB 2008 Express does not let you create a connection to SQL Server 2008 Express database via any of the menu options or wizards, right? It will let you connect to a .mdf file however.

    2)People have sussessfully connected to SQL Server 2008 Express using connections strings in the VB code, right? 

    3)I used SQL Server 2008 Express Management console to create my database. How do I find the database.mdf that question 1 above would use. I searched the whole hard disk for *.mdf and only found adventureworks in a samples folder. Do I export it out of SQL?

    My goal is to populate the SQL tables using the VB.Net app and display the info from the tables using web pages. Will both VB 2008 Express and Visual Web Developer 2008 Express connect to .mdf files? (If I Ever figure out how to make one)

    Thanks,

    Peter
    Old Programmer
    Tuesday, February 16, 2010 6:53 PM
  • I struggled with making a connection between VB 2008 Express and SQL 2008 Express.  I read all of the posts.  Tried many things.  It just wasn't working.  I got many different errors from 'you don't have permissions' to 'failed because access denied' to 'either the name is being used or isn't available'...and so on.

    I am using Win 7 Home Premium 64 bit.  I ended up putting the instance name back to SQLEXPRESS because that's what was listed in the configuration manager.

    What I figured out, was that I had to start VB 2008 Express with RUN AS ADMINISTRATOR on (right click icon and select run as admin).  That got me the ability to select a DETACHED database in the connection dialog box without it saying you don't have permission to open this.  Also, I did have to click advanced and make User Instance=false.
    I also moved the detached database to a different folder on my hard drive and it connected just fine.  Once the connection is made, you will see that database attached in the SQL SSMS tree.
    I even tried an attached database and it worked fine as well.  Though, in the SQL SSMS tree it the database was renamed with the full path.

    Once the connection is made (and presuming you don't delete it) you can start VB normally and just re-open the connection and all is right with the world.

    Hope this helps those who are struggling like I did.


    • Proposed as answer by Reneeb35 Monday, April 12, 2010 8:42 PM
    Thursday, February 18, 2010 2:55 AM
  • You are a genius, dbrew0! Thank you so much - I have been struggling with this very same problem for days!
    Friday, March 12, 2010 6:14 PM
  • If you change your registry key for the following key, I think that it fixes your problem.



    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\RestrictGuestAccess  = 0
    Tuesday, March 16, 2010 5:32 PM
  • Hi all,

    I just wanted to add my experience on this point. There is a lot of advice on this forum and others about  changing Windows firewall settings to resolve the errors 40 and 26 that happen when you try to connect to a SQL database from VS2010 with this sort of unresolved issue lurking in the background. Please IGNORE that advice - somewhere in the "real" MS KB stuff it says to NOT do that as it exposes your machine to threats (I've spent too many hours of my life on this problem!). I was eventually able to connect to a SQL database after 3 SQL reinstalls(!) and using the advice of etbetz and dbrew above - the only trick was to find the database options, to see these you need to select "Show all settings" in the Tools/options dropdown. For me, I had to blank out the SQLServer Instance name to get the connection to work.

    I did NOT need to change any firewall settings to implement this fix.

    I didn't quite cry, but I did fill up with relief when I got the "connection succeeded" message after clicking on Test Connection!

    Thanks so  much to the guys who posted their tips and tricks in this thread - I hope I can help someone in the future as much as you have helped me.

    Monday, September 20, 2010 10:21 AM
  • Hi dbrew0,

    It works! Thank you very much.

    Wednesday, April 11, 2012 1:23 PM
  • I was able to connect in VB 2010 express by changing my Data Connection under tools, options, instance name as SQLEXPRESS. I still cannot access CREATE A NEW SQL DATABASE, but I could ADD A NEW CONNECTION. Where am I going wrong?

    I can however connect to SQL EXPRESS MANAGEMENT! 

    Thursday, April 12, 2012 10:43 PM
  • check this link this ma help you out

    http://www.dotnetspark.com/links/23528-cannot-connect-to-sql-2008-express-from.aspx

    Monday, April 16, 2012 3:39 PM