Error in DataList test query - sqlserver

Error in DataList test query - sqlserver
I found some vs 2005 asp.net walkthroughs.
The first I tried required Northwind.
But sqlserver 2005 came with AdventureWorks, so I used that instead.

I followed the walkthrough instructions, dragging a DataList to Design, and clicking the smart tag to configure my datasource.

sqldatasource=SqlDataSource1
connection string - Data Source=YOUR-02910F1DF1;Initial Catalog=AdventureWorks;Integrated Security=True
I tried the test query.
There was an error executing the query....
Invalid name '[Employee}
I tried the query builder, and then the second line is my own sql statement. Both produced the error above.

SELECT [EmployeeID], [Title] FROM [Employee]
SELECT [EmployeeID], [Title] FROM Employee

I tried this with several different tables, all producing the same error.

When I do with with an access database with an access datasource, it works fine. But since I really want to put something on the web, I need sqlserver 2005.

I confirmed that sqlserver.exe was running.

Can somebody help me?

dennist685

I'd really appreciate an answer to this question. I can't go very far in web applications if I can't attach a datasource to a DataList.

dennist685|||

I got an answer from of all places, the 2003 newsgroups.

Hi Dennist685,

Welcome to ASPNET newsgroup.
From your description, when you try creating a data-driven page in ASP.NET
2.0/VS.NET 2005, you always get error on test query, yes?

Based on my experience, the VS.NET 2005 will use configuration wizard to
help create Sql query statements for our SqlDataSource control. Are you
following the wizard to configure the datasoruce? We can first confirm that
the SqlDataSource is configured correctly. We can open the aspx after
configured the sqldatasource control and check the sql query statement
embeded (test in SQLServer's query console/analyzer to see whether it
works....).

After the SqlDataSource is configured OK, we can then attach it to the
DataList control so as to displaying data.

BTW, for Northwind and Pubs databases, we can manually copy their mdf file
from the original SQLServer 2000 and manually attach them into SQLServer
2005. Also, VS.NET 2005/ .net 2.0 support sqlserver 7.0 2005 (also
include a SQLExpress edition which replace the original MSDE). So please
feel free to use either of them for your data store.

If there're anything unclear or need any further help, please feel free to
post here.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

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(TdsParserStateObject 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(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.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

hank 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.)

Steven,

thank you every so much for the time you spent tracing down the bug. Frankly, I'm very disappointed in Visual Studio for this and other problems, especially in asp.net 2.0.

Also thank you for solving the problems.

There are also problems I found with other asp.net walkthroughs, but those I found easy ways around.

I find it interesting that the people in Microsoft asp.net forums and Microsoft technical forums couldn't answer the question, and you people, still concentrating of vs 2003, did.

dennist685

|||Thank you Dennis for posting the answer you got; the same problem has been driving me crazy for couple of days. At some point I thought it might be the naming issue but when I got the same error in testing it from the query builder (which I assumed is doing its job right) I did not pursue that direction further.
I don't understand why the common logic was not used - i.e., as long as the name is unque you don't have to qualify it.

Arie|||There's still a problem. The query builder doesn't support WHERE and other clauses. So I guess we have to use the table name and type in the schema qualifier.

It's really disappointing. I don't see how the visual studio team overlooked something so glaring. I imagine in the advanced areas there are bugs galore.

I'd bet SP1 is coming soon.

dennist685