Have you granted database-access and (at the very least) select-permissions to the IUSR_machinename user in MSSQL?

The reason that the ODBC-test is working but your code isn't could be that the ODBC-test runs with YOUR privileges - that is, the account you used to log on to Windows. ASP has it's own account, named IUSR_thenameofyourcomputer...

Good sites for learning ASP:
msdn.microsoft.com
www.4guysfromrolla.com

For COOL SQL-tricks, do a Google on "Joe Celko"...

Sorry about that. Something went in my mind that he is using Access. It think connectionstrings.com site doesn't open up for a long time. not sure if I am alone facing that problem or the site has been moved.

You can also try this.

Using OLEDB provider. For Standard Security

oConn.Open "Provider=sqloledb;" & _
         "Data Source=myServerName;" & _
         "Initial Catalog=myDatabaseName;" & _
         "User Id=myUsername;" & _
         "Password=myPassword"

Or check out this link. - http://www.able-consulting.com/MDAC/...erForSQLServer

Using ODBC driver. For Standard Security

oConn.Open "Driver={SQL Server};" & _
         "Server=MyServerName;" & _
         "Database=myDatabaseName;" & _
         "Uid=myUsername;" & _
         "Pwd=myPassword"

http://www.able-consulting.com/MDAC/...erForSQLServer

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

hai
  if u want to use DSN to connect database in asp. u must create system dsn instead of user dsn.

bye.

:):) Thanks to all the hearts who help me :):)
After followings all of your advices finally I was able to connect with SQL server. The following code worked for me:

Using ODBC driver. For Standard Security

oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

but OLEDB provider is still not working

Further I have questions:
-------------------------
1) What is the difference between User DSN and System DSN?

2) What is the difference between ODBC and OLEDB?

3) I want to do my College project in ASP with SQL Server which type of Connection to use (ODBC or OLEDB). Which is safer and faster?

Any replies appreciated. Once again thanks to all who helped me.

Arul Kumar.PA