Best way to Connect to SQL server 2000 with ASP 4.0 on .NET 4.0 build

Can I do this using SqlClient or do I have 开发者_如何学编程to use the ODBC route > ? I get error's in VS but from reading through the other post it appears to be an IDE specific problem.

The best way is to use ADO.NET which should work just fine. You should absolutely stay away from the ODBC route:

using (var conn = new SqlConnection("Data Source=mySql200Server;Initial Catalog=myDataBase;User Id=user;Password=secret;"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT count(*) FROM foo";
    var result = cmd.ExecuteScalar();
}

If this doesn't work here are the things to check:

• Your connection string is correct (serverName/database/username/password)
• You have access to the SQL server from within your ASP.NET server (verify network and firewall)
• Your SQL query is correct

Use Microsoft Enterprise Library 5.0, specifically the Data Access application block.

http://msdn.microsoft.com/en-us/library/ff632023.aspx

http://www.codeproject.com/KB/architecture/MS-EntLib-DataAccess1.aspx

http://jwalin.wordpress.com/2009/02/11/get-started-with-the-enterprise-library-data-access-application-block/

Yes, you can use SqlClient, and you shouldn't get any errors.