[2005 Express] Simple SQLServer 2000 Connection Error

[RS_Arm]

I'm trying to connect to a SQLServer 2000 database.
This is my code:

On Form_Load i'm calling the Conect function.
On Form_Leave i'm calling the Disconect function.

  Public CNN As New SqlClient.SqlConnection
    Public rs As New SqlClient.SqlDataAdapter

    Public Const ID_DB As String = "GesProj"
    Public Const server As String = "TURION\PRIMAVERA"
    Public Const user As String = "SA"
    Public Const Password As String = "SA"

    Public Sub Conect()
        Try
            CNN.ConnectionString = "Data Source=" & server & ";Initial Catalog=" & ID_DB & ";Persist Security Info=True;User ID=" & user & ";Password=" & Password
            CNN.Open()
            Exit Sub

        Catch err As Exception
            MessageBox.Show("Erro: " & err.Message, "Sem conexão", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Clipboard.Clear()
            Clipboard.SetText(err.Message)
        End Try
    End Sub

    Public Sub Disconect()
        Try
            CNN.Close()
            CNN = Nothing
            Exit Sub
        Catch err As Exception
            MessageBox.Show("Erro: " & err.Message, "Sem conexão", MessageBoxButtons.OK, MessageBoxIcon.Error)
           
        End Try
    End Sub

When Conect function is called I get this error:

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)

Need help, please.

[Asgorath]

Hi.
Try to use Windows Authentication on connection string instead of SQL Authentication to see if you can connect.

[RS_Arm]

SQL Server Authentication is set to "SQL and Windows" but I've tried to do this connection in vb6 and everything works great.
What do you think?

Thank you.

[stanav]

In your Disconect sub, you set CNN = Nothing... So any subsequence call of your Conect and Disconect sub will fail.

[RS_Arm]

Why do you say that?
The Disconect function is only called when the program closes.

[bmahler]

So, you are opening a connection at startup and then closing it on close. This was pretty common in VB6 apps (At least where I work) but we have moved to opening and closing connections as needed since we moved to .net. I personally think it is a better method and perhaps you should think about switching to such a method.

[RS_Arm]

Thanks for the advice, but that is not a big problem for me, now. What I want to know is: why do I get the error?

As I said earlier, I can connect to DB throw vb6, but not with vb2005 Express.
What am I doing wrong?

Thank you

[stanav]

Why do you say that?
The Disconect function is only called when the program closes.

You're not calling the Disconect sub in Form_Leave event, are you? You should notice that Form_Leave event is not the same as Application.Exit() or Me.Close()

On Form_Load i'm calling the Conect function.
On Form_Leave i'm calling the Disconect function.

[RS_Arm]

Well, yes... I'm moving from vb6 to .net. Shouldn't be doing this?
Although my mistake, I removed the disconect function, and I still got the same error.

(PS:Thanks for the tip! )

[techgnome]

I'm dizzy from watching everyone dance around the real problem. It doesn't matter where the disconnect is.... the OP cant' even connect in the FIRST PLACE.

Are you sure the connection string is right? Normally, the server is just the computer name.
Also the reason for the error is in the message. The server is refusing remote connections. If you open up Enterprise Manager, right-click the server entry... select properties... goto the Connections tab... there should be a check box that allows you to turn on/off remote connecitons. Make sure it's on.

-tg

[RS_Arm]

Everything is right in SQL, the option "Allow other SQL..." is checked, the maximum user connections is set to unlimited.
What is strange is that I can do a connection from VB6.
And I think that the vb2005 code is right.

[stanav]

Double check your connection string and make sure it's correct.
You can look it up here http://www.connectionstrings.com/?carrier=sqlserver

[RS_Arm]

Still nothing...
My connection string is correct. I've also created another user with administrator privileges.

What should I do next?

Thank you

[bmahler]

I use a differnt style connection string then you, perhaps it may work, hell, can't hurt to try it right

connString = "Server=" & serverName & ";Database=" & Database & ";User ID=" & userID & ";Password=" & pass & ";Trusted_Connection=False"

[RS_Arm]

bmahler, I've used you example and error persists!!
I don't know what else to do!!

[bmahler]

What line is erroring? is it con.open?

[RS_Arm]

Yes.

[Ray.Of.The.Damed]

Hi

I also experienced this error and wound up banging my head so hard that I had a head ache for 3 days. For the life of me I cannot remember the solution and I cannot immediately access the project as I changed jobs and I am working mainly with oracle now. I will take a look through my archives if I get a hold of them.

I do seem to remember something about messing a whole lot around in SQL and then discovering something about the default user names and passwords not being activated or something like the other.

Have you tried creating a new user name and password and also specifying what tables the user can access?

[RS_Arm]

After very long time of search, vb.net express only can connect to local databases. So, even if my computer is named Turion, I will not be able to open it (I think).
Any reply is welcome

[techgnome]

Wait a minute... nononononono.... that's an incorrect statement. The Express IDE can only connect to local databases... but connecting through code, that works just fine, there's no limitations there.

Looking back at your code, however, I think your server name is wrong. What is "Turion", and what is "PRIMAVERA"? You should be just using the NAME of the server, nothing more, nothing less. The only other time you should have anything else with it is IF, and ONLY IF, you have MORE THAN ONE SQL instance on a machine. If there is only one instance of SQL Server running on the machine, then ONLY THE SERVER NAME is needed.

Example, here at the office, we only have SQL 2000 installed on our server. So to connect, we simply use jsut the server name "svrSQLServer".

At home, I have both SQL2000 and SQL2005 running, so each one gets a name for each of the instance, depending on which one I want to connect to: "myMachine\SQL2000" and "myMachine\SQL2005"

-tg

[RS_Arm]

Turion is the name of the server (yes, it's a laptop!!) and PRIMAVERA is an instance of SQL.
What do you think about this?? Strange, isn't it?

[techgnome]

Add "IntegratedSecurity=False;" to your connection string....(I think I spelled that right, Integrated Security maybe two words.)

And I know this is going to sound like a strange question, but sometimes it's those little things, but and you are on the same network where the server is, correct? Strike that.... I see now, the SQL Server is local.... try this:

.\PRIMAVERA as the server name... if that doesn't work try (local)\PRIMAVERA.

Lastly, can you connect to the instance/database using something like SQL Management Studio?

-tg

edit - it's not as strange as it sounds.... I've actually come up against this before with a client... gawd those were some long nights.

[RS_Arm]

Everything that u said is right but (there is always a 'but'...) I've tried all your suggestions and still got the same error.
I can access to database using SQL Enterprise Manager and Query Analyzer.
As i said in one of the firsts posts, I can even access to db using vb6.

I don't know if it may be relevant but I'm using WnXP x64.

Thank for your attention

[RS_Arm]

(techgnome, ff you don't mind, could you see my post on vb6 based on ActiveX EXE?
This (SQL conn) is not (Now) my ultimate project in my chain of priorities.)
http://vbforums.com/showthread.php?t=486105)
Thank you once again.