Oledb Provider having problem in MS SQL Server 2005

Hi,

I try to access the table "UserGroup" in my local sql server 2005 database. However I get this error message
"Invalid object name 'UserGroup'. "

I'm using MS Visual Studio 2005 and MS Sql Server 2005. I did install the latest Sql Native. In fact I just migrate from ASP.NET to ASP.NET 2.0 . It is working if my connection string is point to my "online host" without any error.

'Online Connection String - Working
<addkey="OLEDB"value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=abc;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx;Connect Timeout=3600"/>

'Local Connection String - Not working
(SQL Authentication)
<addkey="OLEDB"value="Provider=SQLNCLI;Trusted_Connection=No;UID=calvin;Initial Catalog=mydatabase_db;Data Source=CALVINNB; Password=xxxxxx"/>
OR
(Windows Authentication)
<addkey="OLEDB"value="Provider=SQLNCLI;Server=CALVINNB;Database=mydatabase_db;Trusted_Connection=yes"/
Is the Oledb provider having problem in MS SQL Server 2005?

Any help would be more appreciate.

Calvin

First thing I would try it to make sure you do have a table named UserGroup, and also try prefixing with with the owner.

Next, you should be using the SQL provider and not the OLEDB providers for SQL Server.

|||Yes, I do have a table named UserGroup. If I change the OLEDB provider to SQL provider, then I have a lot of code need to be change and debug. Is there any way to solve this problem? And I don't understand why I use my "Online" connection string(bottom) without error? Even the provider isSQLOLEDB.1?

<addkey="OLEDB"value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxx;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxxxx;Connect Timeout=3600"/>

The difference to my local connection is just the "UID", "Password" and Data Source only. I'm sure that the login account for local is created perfectly. The host and my computer also using MS SQL Server 2005.

|||

EDUStreet:

Yes, I do have a table named UserGroup. If I change the OLEDB provider to SQL provider, then I have a lot of code need to be change and debug.

OLEDB provider is meant to be used for older data sources. For SQL Server 7.0+ you should really be using the SQL Providers, that is what it is there for. In terms of coding changes there should not be too many apart from renaming all the declarations and instantiations from Oledb to Sql.

One of the best ways to make sure you got the right connection string is to drag and drop something like a gridview or sqldatasource and go through the wizard to connect to your datasouce and use the auto generated connection string

Here is a web site that has all connection string formats

http://www.connectionstrings.com/?carrier=sqlserver2005

|||

Finally, I change all the code which is from OLEDB provider to SQL provider

This is the connection string i'm using now.

<addkey="OLEDB"value="User ID=xxxx;Initial Catalog=mydatabase_db;Data Source=xxx.xx.xx.xx,xxxx; Password=xxxx"/>

|||

EDUStreet:

"The ConnectionString property has not been initialized. "

When you create the SqlConnection object you need to supply the connection string. Please ensure you have done this.

|||

Thanks Jimmy :)