Connectting SQL 2005 with VB6

[getusama]

Could you please tell me how I can connect VB6 with SQL 2005 server via ADODB.


Does anyone know what connection string to use to connect to the database (mdf files)?


thans

[Hack]

Try http://www.connectionstrings.com/

[si_the_geek]

In the "SQL Server 2005" sticky thread at the top of this forum (which is also linked from the SQL Server section of the FAQs thread), there is a link in post 4 to an example/tutorial by Microsoft on exactly this.

[getusama]

thanks

i've tried this

Provider=SQLNCLI;Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;

but what should i put in the place of a provider..??

[Hack]

You already have SQLNCLI as a Provider. That should be sufficient.

[getusama]

Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName= C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\bank.mdf;Data Source=KONG-DCCEC8B2B4\sql2005"

    cn.Open
        Set rs = New ADODB.Recordset
        rs.Open "Select * from orders", cn
    cn.Close

i've used te above code but it keeps giving different errors. now its telling me that i've already got that databse existing and therefore i cannot use cn.open

[Hack]

How many times are you trying to open it?

What is the exact wording of the error message?

[si_the_geek]

I'm pretty sure you only need to Attach the file once, as it will then be registered in the database engine. From then on, you should specify the database name instead (see what it is called in Management Studio, I presume it will be "bank"), eg: Database=bank

[getusama]

Error: an attemp to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\bank.mdf failed. A database with the same name exists, or specfied file cannot be opened, or it is located on UNC share.


I have used the following:
in the general area:

Public cn As ADODB.Connection 'this is the connection
Public rs As ADODB.Recordset 'this is the recordset

in the form load area:

Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=SQLNCLI;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName= C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\bank.mdf;Data Source=KONG-DCCEC8B2B4\sql2005"
 
    cn.Open
        Set rs = New ADODB.Recordset
        rs.Open "Select * from client", cn
    cn.Close

[si_the_geek]

That is what I suspected - so you need to change the AttachDBFileName part to a Database part, using the name of the database that is shown in Management Studio.

[getusama]

thanks a lot .. that error is gone...

now its telling me that the log in has failed. i suppose i need to give it te user name and the password.

could you please tell me how and where in the string do i specify it?

[getusama]

i am using windows authenticated login... where i use a blank password to log into windows..

could someone please tell me the syntax of the connection string please ?

[si_the_geek]

The part "Integrated Security=SSPI" (or the equivalent "Trusted_Connection=yes") specifies to use your Windows login details, and those details will be passed automatically.

I have never known that to fail, so I would assume that your Windows user hasn't got permission to connect to the Database, or perhaps even the database server itself. To check (and change it if apt), go into the Security section of Management Studio.