none
Connection to DB from VB.net express

    Question

  • When trying to connect to a db in VB.net express I get this warning

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

     

    this is my code

    'verbing maken mat de db

    Dim DBconnection As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Documents and Settings\Pieter Van Driessche\Mijn documenten\Visual Studio 2005\Projects\kasboek\kasboek\db1.mdf';Integrated Security=True;User Instance=True")

    DBconnection.Open()

     

    Can somebody help?

    Wednesday, November 08, 2006 3:43 PM

Answers

  • Ok it seems to work when I add the code in a module. But when i try it in a form it does not work! Is this normal?

    And maybe you can help me with another problem. when i debug with vb.net express all the docked windows(toolbox, solution explorer etc) start floating. How can i solve this?

     

     

     

     

    Friday, November 10, 2006 9:17 AM

All replies

  • The error indicates that your server name is not correct, open the SQL Configuration Manager and verify that everything is correct.

    The information about remote connections is bogus and you do not need to enable TCP to get this to work. This is a local connection and will work without TCP.

    Mike

    Wednesday, November 08, 2006 5:32 PM
    Moderator
  • Ok the problem seems to be with db itself. Because when i open the sql server manager i get the connect to server screen, but when i click on the connect button, nothing seems to happen.

    After a few seconds the cancel button becomes available and when i click that one the program blocks.

    What can i do about that?

    gr

    Wednesday, November 08, 2006 6:06 PM
  • You're going to need to take the troubleshooting one step at a time and work out the issue.

    1. Did you check the SQL Configuration Manager to verify the name of your instance and that it is running? SQL Configuration Manager is different than Management Studio. Do this and make sure the server is running and that you know the instance name.
    2. If it's running and the instance name is actually SQLEXPRESS open a command window and try running the following: SQLCmd -S .\SQLEXPRESS -E. You should get a prompt that looks like "1>". You can just type Exit at the prompt. If you get an error, report back.

    The errors you're getting are still indicating that you've got the wrong instance name.

    Mike

    Wednesday, November 08, 2006 7:00 PM
    Moderator
  • The server is running

    I got the following things from the properties of the server

    Host name Pieter
    name: Sql Server (SQLEXPRESS)
    instance ID: MSSQL.1

    When i try to do SQLCmd -S .\SQLEXPRESS -E  I get the prompt 1> so that is ok then??

    When i try with the SQLmanager I still can't get a connection with the prompt at the being of the programme (logon screen) but when I double click on  'pieter\sqlexpress' in the database engine tree, I get the databases and I can created new tables.

     

    but still I can't make a connection with vb.net

     

    gr

    Wednesday, November 08, 2006 7:38 PM
  • Hi gr,

    The test in SQLCmd tells you that your SQL Express instance is running and you can connect to it. The fact that you can open the server in Management Studio by double clicking on the server name, but not using the connection dialog indicates that you have something entered incorrectly in the connection dialog. Could you list out what is set for each of the text boxes in your connection dialog?

    Everything you've done up till now has been using the parent instance, SQLEXPRESS, but your VB code is actually connecting to a User Instance. User Instances are a special type of SQL Express instance that was design to work with VS Express for deploying applications that use SQL Express to store local data. As long as your parent instance is working, you should be able to create a User Instance when your application runs.

    Let's take as much out of the equation as we can and just do a basic connection to a User Instance and run a simple query. Try out the following code in a new Console applciation:

        Sub Main()
    
            Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;INTEGRATED SECURITY=TRUE;USER INSTANCE=TRUE")
            conn.Open()
    
            Dim cmd As New SqlCommand
            cmd.CommandText = "SELECT Name FROM sys.databases"
            cmd.CommandType = CommandType.Text
            cmd.Connection = conn
    
            Dim rdr As SqlDataReader
            rdr = cmd.ExecuteReader
    
            While rdr.Read
                Console.WriteLine(rdr(0).ToString())
            End While
    
            conn.Close()
    
        End Sub

     This might give you a timeout the first time you run it, if that happens, run it a second time. If it fails the second time, let me know what the error is.

    Mike

    Wednesday, November 08, 2006 11:11 PM
    Moderator
  • In de dialog i got following inserts

    server type: database engine
    server name: PIETER/SQLEXPRESS
    authentication: windows autentication
    user name: pieter van driessche
    pasword: 'blank'

    I got no problem with this sub.

    But I don't realy understand the user instance file that you've given me.
    so it should work now?

    gr

    Thursday, November 09, 2006 7:48 AM
  • Hi gr,

    I've you've typed everything exactly as it appeared in the connection dialog, the problem is that you have a forward slash (/) between your computer name and the instance name when you should have a back slash (\). Change the server name text box to PIETER\SQLEXPRESS and you should be able to connect using the dialog. Easy mistake to make, hard to find.

    If the sample code I gave you worked, then your code should work. The only think I changed in the connection string is I removed the AttachDbFilename attribute to take the database out of the picture. This verified that you can successfully connect to the parent instance (SQLEXPRESS) and create the User Instance process as specified by the connection string.

    Since you know my code sample works, you might try copying the declaration for the SqlConnection object from the sample into your program and then add the AttachDbFilename attribute into that line to see if that changes anything.

    Mike

    Friday, November 10, 2006 2:02 AM
    Moderator
  • Ok it seems to work when I add the code in a module. But when i try it in a form it does not work! Is this normal?

    And maybe you can help me with another problem. when i debug with vb.net express all the docked windows(toolbox, solution explorer etc) start floating. How can i solve this?

     

     

     

     

    Friday, November 10, 2006 9:17 AM