Unable to connect to SQL Server 2005

[corneagigi]

Hy people, I am using VB 6.0 and I am trying to connect to an SQL Server 2005 database but when running my application I get the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

and my code is:

Dim ConnStr As String
Dim db As New ADODB.Connection
Dim rss As New ADODB.Recordset
Dim sql As String

sql = "Select * from table"
ConnStr = "SQLNCLI; Server=ServerIP\SQLEXPRESS; Database=DatabaseName;Uid=usernname;Pwd=Password;"

db.Open ConnStr
rs.CursorLocation = adUseClient
rs.Open sql, db, adOpenDynamic, adLockPessimistic


Can anyone help me, pleaseeeeeeeeeeeeeee???

[szlamany]

Where did you get that connection string?

With VB6 I do not think you can use SQLNCLI

With our vb6 code we connect with:

    If strDriver = "" Then
        strDriver = "SQLOLEDB"
    End If
    
    'Create a connection to the database
    Set gCn = New ADODB.Connection
    
    gCn.Provider = strDriver
    gCn.Properties("Data Source").Value = strServer
    gCn.Properties("Initial Catalog").Value = strDatabase
    If gstrUser <> "" Then
        gCn.Properties("User Id").Value = gstrUser
        gCn.Properties("Password").Value = gstrPassWord
    Else
        gCn.Properties("Integrated Security").Value = "SSPI"
    End If
    
    gCn.CommandTimeout = 300
    gCn.Open

We build our string with property values - but you should get the basic idea...

[si_the_geek]

You can use it with VB6, you just need to use a valid connection string.

A connection string is a series of name/value pairs, and in the case of SQLNCLI you provided the value, but not the name to go with it. It should be like this:

ConnStr = "Provider=SQLNCLI; Server=ServerIP\SQLEXPRESS; Database=DatabaseName;Uid=usernname;Pwd=Password;"

[szlamany]

You can use it with VB6, you just need to use a valid connection string.

Si - I didn't know that...

Is there a benefit to changing the driver?

Does any of the other ADO code being used need to change??

[si_the_geek]

The benefit is that you can use the extra features that it provides (I'm not actually sure what they are!).

I haven't used it much, but haven't noticed anything that needs changes, which is what I expected (as it is basically an extended version of the OLEDB provider).

[szlamany]

And it would need to be installed on client PC's - right? Does it come along with the .Net framework?

[si_the_geek]

It does need to be installed, which is perhaps a reason to not use it (certainly if you aren't getting any benefits from it). From what I remember, SQLOLEDB is included MDAC, which of course you also still need to install.

I have no idea if it is included with the .Net framework, but the redistributable package can be found here (added to the SQL Server 2005 sticky).

[szlamany]

Thanks!

That's a nice link - lots of interesting downloads.

Wish I had more time to just play with functionality...

[corneagigi]

Hello everyone. I am using Vb6 to build a aplication that is using a SQL Server 2005 database and my code is listed below:

If strDriver = "" Then
strDriver = "SQLOLEDB"
End If

'Create a connection to the database
Set gCn = New ADODB.Connection

gCn.Provider = strDriver
gCn.Properties("Data Source").Value = strServer
gCn.Properties("Initial Catalog").Value = strDatabase
If gstrUser <> "" Then
gCn.Properties("User Id").Value = gstrUser
gCn.Properties("Password").Value = gstrPassWord
Else
gCn.Properties("Integrated Security").Value = "SSPI"
End If

gCn.CommandTimeout = 300
gCn.Open

when trying to connect I get the following error:

[DBNETLIB][ConnectionOpen(Connect()).] SQL Server does not exist or access denied



Cound someone help me?

[alex_read]

I've not used the Properties collection in that way, always just set the connection string as a whole, however my first port of call would be to get a working connection outside of your code.

See here for creating a UDL file to test a connection and read what the correct values for the parts of your connection should be: http://www.vbforums.com/showthread.p...&highlight=udl

This stage will also highlight if you have port / firewall / protocal issues which are blocking your connection. Once you have that working, check the values from this file's text match what you're getting in your code by stepping through and debugging the code from a breakpoint...

[kevchadders]

Using the SQL Management Studio you should be able to create another connection where you can test the connection info you are passing to your app.

This could be used a quick connection test.

[Hack]

Duplicate threads merged

[szlamany]

Is this SQL database on the local workstation or is that a remote ip address?

If it's remote - then go to that machine and go into MS SQL Server Surface Configuration Utility and make sure that REMOTE CONNECTIONS option is turned on.