Thank you for the detailed response Dennis,
After tested through your further description, I've reproduce the problem
you encountered. Yes, when using the SqlDataSource's configuration wizard
to generate select statement by "specify columns from a table or view", the
generated SQL statement always cause Error.
And based on my further research, I think this should be a problem of the
SqlDataSource control's configuration wizard. Because for SQL 2005 tables,
they could be grouped by a new unit called "Schema", so when we reference a
table in our SQLStatement , we can not simply use their tablename, but need
to add the schema name together . for example, in our problem, the
Adventureworks database's Employee table are in a schema named
"HumanResources", then, our sql statement should be
"SELECT HumanResources.Employee.* FROM HumanResources.Employee"
or "SELECT * FROM HumanResources.Employee"
rather than "Select * from Employee", "HumanResources" is the schema name.
And seems the SqlDataSource control's
"specify columns from a table or view" wizard dosn't support this
correctly. I'll report this issue to our product team. Curently, I suggest
you try the following means to workaround this problem when dealing with
the SQL2005 database tables:
1. We can manually adjust the sql statement in web page (aspx )'s template
by adding the Schema name before the table name.
2. Instead of using the "specify columns from a table or view" in the
wizard, we can choose another option
"specify a custom SQL statement or stored procedure"
in this wizard, we can use another SQL statement generating tool (Query
Builder ) to auto generate the sql statement (which will correctly add the
Schema name for SQL 2005 data tables based on my tests). Or we can manually
enter sqlstatement also.
Anyway, since this is a problem of our product , I'm sorry for the
inconvenience it brings you. Thanks for your understanding.
Steven Cheng
Microsoft Online Support
Get Secure!
www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--------------------
| Thread-Topic: Error in DataList test query - aspnet 2.0
| thread-index: AcXkMVgHtMlKbj8PR7Sai1iz2bQq9w==
| X-WBNR-Posting-Host: 80.179.14.44
| From: "=?Utf-8?B?ZGVubmlzdDY4NQ==?=" <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
| Subject: RE: Error in DataList test query - aspnet 2.0
| Date: Mon, 7 Nov 2005 22:55:03 -0800
| Lines: 99
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.dotnet.framework.aspnet.webcontrols
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.dotnet.framework.aspnet.webcontrols:11759
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.webcontrols
|
| Thank you Steven,
|
| I never got as far as configuring the datasource correctly.
|
| There was an error executing the (test) query. Please check the syntax
of
| the command and if present, the types and values of the parameters to be
sure
| they are correct.
|
| Invalid name 'Employee'
|
| There is no stack trace. I was in the configuration stage where the
wizard
| asks you to perform a test query.
|
| I went ahead and built default.aspx successfully,
|
| But when I tried to view it in a browser, I got the stack trace you
wanted.
|
|
| Server Error in '/FormatDataList' Application.
----------------------------------------------------------------------------
----
|
| Invalid object name 'Employee'.
| Description: An unhandled exception occurred during the execution of the
| current web request. Please review the stack trace for more information
about
| the error and where it originated in the code.
|
| Exception Details: System.Data.SqlClient.SqlException: Invalid object
name
| 'Employee'.
|
| Source Error:
|
| An unhandled exception was generated during the execution of the current
web
| request. Information regarding the origin and location of the exception
can
| be identified using the exception stack trace below.
|
| Stack Trace:
|
|
| [SqlException (0x80131904): Invalid object name 'Employee'.]
| 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(TdsParserStateObjec
t
| stateObj) +199
| System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand
| cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
| bulkCopyHandler, TdsParserStateObject stateObj) +2300
| 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)
| +1021
| 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(CommandBeh
avior 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(DataSourceSelectAr
guments arguments) +1657
| System.Web.UI.WebControls.BaseDataList.GetData() +53
| System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean
| useDataSource) +267
| System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +57
| System.Web.UI.WebControls.BaseDataList.DataBind() +62
| System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55
| System.Web.UI.WebControls.BaseDataList.CreateChildControls() +62
| 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) +5729
|
|
|
|
----------------------------------------------------------------------------
----
| Version Information: Microsoft .NET Framework Version:2.0.50727.42;
ASP.NET
| Version:2.0.50727.42
|
| dennist685
|
|
ataList ID="DataList1" runat="server"