SQL Server Connection Question

[Jeff Carlin]

We just delivered our first VB6-SQL Server app. The client reports the following error when opening the connection:

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

We declare the following in the sub main():

Public MyConnection As New ADODB.Connection

Our connection code look like this:

    With MyConnection
        .ConnectionString = "  Driver={SQL Server};" _
                            & "server=" & sServername & ";" _
                            & "Database=" & sDatabase & ";" _
                            & "uid=" & sUserID & ";" _
                            & "pwd=" & sPasswd
        .Open
    End With

His email to me states:
"I have the DNS set up properly, I don't know if there is something in the code pointing somewhere?"

Can anyone help???

Thanks

[PsudoLogical]

Try this code... it was generated on an ADO Data control. Actually what I did was to copy the string that was generated... the following... and code it into my app as I didn't want to use a data control.... I hope this works for you

Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DRIVER=SQL Server;SERVER=SQL-SERVER;APP=Visual Basic;WSID=WorkstationId;Trusted_Connection=Yes"

[Gary Juleff]

Jeff,

I assume that you are trying to connect to SQL Server using the OLE DB Provider for SQL Server and not via a DSN or DSNLess connection.

If this is so then you need to specify a provider such as SQLOLEDB. The connection string should look like this:

cn.connectionstring = "PROVIDER=SQLOLEDB" & _
";SERVER=" & sServer & _
";UID=" & sLoginID & _
";PWD=" & sPassword & _
";DATABASE=TCW"
cn.open

Hope this helps

Gary

[Jeff Carlin]

Thanks, Gary-

In his email to me he said "I have the DNS set up properly...". Should I have him remove the DNS?

[Jeff Carlin]

PsudoLogical:

You're code works on this end. Thanks. I'll upload it to my client. Let you know results tomorrow.

[Gary Juleff]

Jeff,

If you mean can he remove the entry for the DSN in the ODBC Data Source Administrator via Control Panel then yes.

Ciao

Gary

[Jeff Carlin]

Thanks to all. Problem 1 solved, Problem 2 is at hand.

PsudoLogical, your connection code seems to work. Thanks very much. However, now my client reports the following error:

Error 430
Class does not support automation or does not support expected interface.

The code he is executing is:

Dim rsTestKey As New ADODB.Recordset
    
g_sSQL = "SELECT * FROM tblKeys WHERE sKeyLine='" & lblKeyLine & "'"
rsTestKey.Open g_sSQL, PROJ_DB, adOpenKeyset, adLockPessimistic, adCmdText

Anyone want to guess? He's running the app on an NT 4 box. Could DCOM or MDAC be at fault? Are they normally in NT or what? Thanks very much.

[PsudoLogical]

My first 'guess' is that the version of MDAC running on your client machine is ver 2.0.... I am running 2.5 on Win2K Pro.... Let me know if this is true or not


Regards