New to ADO programming

[MathewLeung]

Hi,

I would like to connect to SQL server via TCPIP using ADO. Can someone tell me more about on how to setup connection using TCPIP?

Thanks

 

[link9]

Well, if you want to use TCP/IP, you go into

Start --> Programs --> SQL Server --> Client Network Utility

and from there, you set TCP/IP to be your default protocol. Then, requests from your computer to a SQL Server will go via TCP/IP by default.

Once you do that, you can connect via a dsn-less connection like this:

'DECLARE AND SET A CONNECTION STRING
dim strconn
strconn="PROVIDER=MSDASQL;DRIVER={SQL Server};"
strconn=strconn & "SERVER=10.0.0.1;DATABASE=myDB;"
strconn=strconn & "UID=uid;PWD=pwd;"

'DECLARE AND OPEN YOUR CONNECTION
dim con
set con = server.createObject ("ADODB.Connection&quot
con.open strconn

'DECLARE AND SET A SQL STATEMENT TO EXECUTE
dim mySQL
mySQL="select * from publishers where state='NY'"

'DECLARE AND OPEN YOUR RECORDSET
dim rs
set rs = server.createObject ("ADODB.Recordset&quot
rs.activeConnection = con
rs.open mySQL
'the two preceding lines can be combined like:
' rs.open mySQL, con
'I showed you the other way just to be clear

hope that helps!
Paul Prewett

 

[MathewLeung]

Thanks Paul !!! It is really helpful :>

However, the "UID=uid;PWD=pwd;" section does not seem to work as SQL server always use my window login rather than the UID/PWD.