Error in DataList test query - aspnet 2.0
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

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






--------------------
Show quoteHide quotemicrosoft.public.dotnet.framework.aspnet.webcontrols:11729
Show quoteHide quote

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

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




--------------------
<BHbBhH64FHA.1***@TK2MSFTNGXA01.phx.gbl>
Show quoteHide quotemicrosoft.public.dotnet.framework.aspnet.webcontrols:11759
Show quoteHide quoteSystem.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjec
t
Show quoteHide quoteSystem.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBeh
avior behavior) +7
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectAr
guments arguments) +1657
Show quoteHide quote

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

Thanks for your followup Dennis,

I'm sorry to hear that those problems make you disappointed  in the new
VS.NET products. However, really appreciate your sincere feedback to us.
Also, please do believe us that our product dev guys are always trying
their best to make the products more convenient and comfortable for our
customer to use though there still existing many drawbacks and problems
within them. 

Anyway, we're always looking forward to more and more feedback from you and
other customers or community guys which can help make our product better
and better.

Thanks again for your support to Microsoft!

Steven Cheng
Microsoft Online Support

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



--------------------
<BHbBhH64FHA.1***@TK2MSFTNGXA01.phx.gbl>
<4FC4F0B5-9332-4D10-9CA0-355FC2EA6***@microsoft.com>
<oTcKPzO5FHA.1***@TK2MSFTNGXA02.phx.gbl>
Show quoteHide quotemicrosoft.public.dotnet.framework.aspnet.webcontrols:31024
Show quoteHide quote

I'm afraid this isn't the end of the problems.  Although the test query
worked fine, the DataList didn't produce any  labels.  And when I built it
the browser was blank.  Apparently the datalist doesn't work with
humanresources.ID and humanresources.Title

If I am right this is a huge bug.

dennist685

Show quoteHide quote

Hi Dennist,

As for your new problem, I think there may have some problems within the
DataList's aspx template. Is the lable or other controls in the template
autogenerated by wizard? Here is a test page(autogenerated)  which works
well in my Whidbey/Yukon enviornment. Here is the page's aspx template (no
code in code file...)

It just select some fields from the AdventureWorks database's Employee
table.

========================================

<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="DataListPage.aspx.cs" Inherits="DataAccess_DataListPage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
            SelectCommand="SELECT EmployeeID, NationalIDNumber, ContactID,
Title FROM HumanResources.Employee">
        </asp:SqlDataSource>

    </div>
        <asp:DataList ID="DataList1" runat="server"
DataKeyField="EmployeeID" DataSourceID="SqlDataSource1">
            <ItemTemplate>
                EmployeeID:
                <asp:Label ID="EmployeeIDLabel" runat="server" Text='<%#
Eval("EmployeeID") %>'>
                </asp:Label><br />
                NationalIDNumber:
                <asp:Label ID="NationalIDNumberLabel" runat="server"
Text='<%# Eval("NationalIDNumber") %>'>
                </asp:Label><br />
                ContactID:
                <asp:Label ID="ContactIDLabel" runat="server" Text='<%#
Eval("ContactID") %>'></asp:Label><br />
                Title:
                <asp:Label ID="TitleLabel" runat="server" Text='<%#
Eval("Title") %>'></asp:Label><br />
                <br />
            </ItemTemplate>
        </asp:DataList>
    </form>
</body>
</html>
===============================

Hope helps. 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.)




--------------------
<BHbBhH64FHA.1***@TK2MSFTNGXA01.phx.gbl>
<4FC4F0B5-9332-4D10-9CA0-355FC2EA6***@microsoft.com>
<oTcKPzO5FHA.1***@TK2MSFTNGXA02.phx.gbl>
<4F4E026B-DB34-45DB-9DB4-5994F3134***@microsoft.com>
<sO8MekZ5FHA.3***@TK2MSFTNGXA02.phx.gbl>
Show quoteHide quotemicrosoft.public.dotnet.framework.aspnet.webcontrols:31043
Show quoteHide quote

Steven,

Oops.  I slipped up.  I did it again and it worked fine.

dennist685

Glad to hear that it also works for you.
If you need any further help, please feel free to post here.

Thanks & Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--------------------
<BHbBhH64FHA.1***@TK2MSFTNGXA01.phx.gbl>
<4FC4F0B5-9332-4D10-9CA0-355FC2EA6***@microsoft.com>
<oTcKPzO5FHA.1***@TK2MSFTNGXA02.phx.gbl>
<4F4E026B-DB34-45DB-9DB4-5994F3134***@microsoft.com>
<sO8MekZ5FHA.3***@TK2MSFTNGXA02.phx.gbl>
<E72E74CE-6F8D-44A5-99F5-540105652***@microsoft.com>
<$42FL8c5FHA.1***@TK2MSFTNGXA02.phx.gbl>
Show quoteHide quotemicrosoft.public.dotnet.framework.aspnet.webcontrols:31049
Show quoteHide quote

It works fine with a grid.

dennist685

getting Membership userid to use and store in a custom database
Gridview Updates not writing to database after clicking update
detailsview asp.net 2 and insert confirmation
General Event Handler For Runtime Buttons
Dynamically Adding DataLists
Custom DropDownList Control and ListItem
GridView DataFormatString not formatting
Custom Control Design Mode
10M free hosting
10M free hosting