Hi, I will start step by step:
1. a new web site with VS 2005.
2. I added a sqldatasouce and connect with AdventureWorks sample database ,which comes with sql server 2005 developer edition, selected by drop-down list. [ server name:(local) ]
3. Test connection. It is OK.
4. Saved as 'AdventureWorksConnectionString'.
5. Some columns are selected in the 'product' table.
6. At the end while testing query with 'test query' button it gives:
"There was an error executing the query. Please check the syntax of the command and if present, the types and values of parameters and ensure the are correct.
Invalid object name 'Product'. "
7. However when I choice NorthWind database sample I installed externally, there is no problem.Moreover, when I choice AWBuildVersion table in the AdventureWorks, and it's columns, there is also no problem.
8. I compared NorthWind and AdventureWorks security properties in the SQL server managment studio, but can't find any differences.
9. I have been searching all the web since two days.
10. Thanks.
can you paste the SQL command that fails...
There are two problems.
The AdventureWorks database uses a "Schema.Table" naming convention that the SQLDataSource Wizard doesn't pickup.
Table Names are Case-Sensitive in the AdventureWorks database.
Workaround:
Instead of "Specify columns from table or view", select "Specify a custom SQL statement..."
On the next screen, use the "Query Builder" to generate the SQL statement. It will pickup the schema name.
PS: After I post this message, I'm going to move this thread to the SQL database forum. I think it's more appropriate there.
|||
Yeah, it works... Thank you very much... But,I couldn't figure out why this happened to me, because I have started to read"Addison Wesley ASP NET .2.0 Illustrated". In that book, it is theprocedure how to implement AdventureWorks database in Chapter 2, and does normention any info of "Schema.Table" and SQLDataSouce relation. Ihave tried some method to accomplish that with "Specify columns from tableor view" method:
Source Code of"Specify columns from table or view" method which gives error:
<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
ProviderName="<%$ConnectionStrings:AdventureWorksConnectionString.ProviderName %>"
SelectCommand="SELECT [ProductID], [Name], [ProductNumber], [ListPrice],[Color], [SafetyStockLevel], [StandardCost], [Size] FROM[Product]">
</asp:SqlDataSource>
Source Code of "Specify a custom SQL statement..."and applying "Query Builder" method which gives NO error:
<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
SelectCommand="SELECT ProductID, Name, ProductNumber, ListPrice, Color,SafetyStockLevel, StandardCost, Size FROMProduction.Product">
</asp:SqlDataSource>
What I could try as a beginner to get a validoutput is changing[Product] as Production.Product.Of course, it gives the same 'invalid object name' error.
As I told you before, AWBuildVersion tablegives no error with "Specify columns from table or view" method:
<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
ProviderName="<%$ ConnectionStrings:AdventureWorksConnectionString.ProviderName%>"
SelectCommand="SELECT [SystemInformationID], [Database Version] ASDatabase_Version, [VersionDate], [ModifiedDate] FROM [AWBuildVersion]">
</asp:SqlDataSource>
I have understood from your note theonly way to implement "Schema.Table" s is use the "QueryBuilder", right?
And for your consideration here is Stack Trace:
------
SqlException (0x80131904): Invalid object name 'Product'.]System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2406
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1038
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1657
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68
System.Web.UI.WebControls.GridView.DataBind() +5
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67
System.Web.UI.Control.EnsureChildControls() +97
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Control.PreRenderRecursiveInternal() +171
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731
------
Again, Thank you very much to help and save my time.
sql
ystem 
|||Hold your horses! - Assuming you are using the included ADS wizard, you are welcome to submit the config file and your two databases to me, and I will see if I can reproduce your problem. (I do not work for MS). You can find my email address on my profile here on the forum.