Ch. 19 and Appendix B

MStarks · November 19th, 2013, 03:26 PM

To Whom It May Concern:

I could use some help! I developed a Website in VWD 2010 using the techniques of Imar's book, which would have been most flustrating otherwise Thanks Omar! It uses ADO.NET Entity Framework and Data Controls. I published the site to IIS7. It is ASP.NET 4, and the DB is SQL Server 2008 R2. I am also using profiles. Locally in my develop environment everything works as it should. Iâll attempt to explain my issue the best I can. Basically, my site allows a user to register. Once a user registers, he/she can create a service request. The user can also log in to check on the status of his/her service request. Here in is where my problem lies. The management part works fine. Users can log in with no problems; however when the user attempts to create a service request (which is supposed to go into a SQL table named Ticket), I get an error. The tickets that I created locally during development are displaying on my admin page(s) on the published site. So I know that I can access my Ticket table (on the published site), and also I can access my TicketUpdate table (on the published site), and insert into it. The difference is that these management pages are not using Entity Data Source Controls. I am thinking it has something to do with my App_Code folder, and/or my connection string, or a permissions problem, but I really donât know. Below are the error messages I am getting.

This is the error I get when a user attempts to submit a service request.

Server Error in '/' Application.

Invalid object name 'dbo.Ticket'.
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 'dbo.Ticket'. Source Error: Line 30: Using myEntites As New AllegitechEntities() Line 31: Line 32: Dim lastTicket = (From l In myEntites.Tickets Line 33: Order By l.Id Descending Select l).First() Line 34:
Source File: E:\web\allegite\Helpdesk\CreateTicket.aspx.vb Line: 32 Stack Trace: [SqlException (0x80131904): Invalid object name 'dbo.Ticket'.] System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) +2074350 System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) +5066444 System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning() +234 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275 System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +86 System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311 System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987 System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior, String method) +141 System.Data.SqlClient.SqlCommand.ExecuteDbDataRead er(CommandBehavior behavior) +12 System.Data.Common.DbCommand.ExecuteReader(Command Behavior behavior) +10 System.Data.EntityClient.EntityCommandDefinition.E xecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443 [EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.] System.Data.EntityClient.EntityCommandDefinition.E xecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +479 System.Data.Objects.Internal.ObjectQueryExecutionP lan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +736 System.Data.Objects.ObjectQuery`1.GetResults(Nulla ble`1 forMergeOption) +149
System.Data.Objects.ObjectQuery`1.System.Collectio ns.Generic.IEnumerable<T>.GetEnumerator() +44 System.Linq.Enumerable.First(IEnumerable`1 source) +176 System.Data.Objects.ELinq.ObjectQueryProvider.<Get ElementFunction>b__0(IEnumerable`1 sequence) +41 System.Data.Objects.ELinq.ObjectQueryProvider.Exec uteSingle(IEnumerable`1 query, Expression queryRoot) +59 System.Data.Objects.ELinq.ObjectQueryProvider.Syst em.Linq.IQueryProvider.Execute(Expression expression) +150 System.Linq.Queryable.First(IQueryable`1 source) +265 Helpdesk_CreateTicket.EntityDataSource1_Inserted(O bject sender, EntityDataSourceChangedEventArgs e) in E:\web\allegite\Helpdesk\CreateTicket.aspx.vb:32 System.Web.UI.WebControls.EntityDataSourceView.OnI nserted(EntityDataSourceChangedEventArgs e) +112 System.Web.UI.WebControls.EntityDataSourceView.Exe cuteInsert(IDictionary values) +521 System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +89 System.Web.UI.WebControls.DetailsView.HandleInsert (String commandArg, Boolean causesValidation) +379 System.Web.UI.WebControls.DetailsView.HandleEvent( EventArgs e, Boolean causesValidation, String validationGroup) +611 System.Web.UI.WebControls.DetailsView.OnBubbleEven t(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.DetailsViewRow.OnBubbleE vent(Object source, EventArgs e) +112 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.Button.OnCommand(Command EventArgs e) +125 System.Web.UI.WebControls.Button.RaisePostBackEven t(String eventArgument) +167 System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +10 System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +36 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1016

This is the error I get when a user attempts to check service request status.

Server Error in '/' Application.

Invalid object name 'dbo.Ticket'.
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 'dbo.Ticket'. Source Error: Line 15: Select New With {status.Issue,
status.CreateDateTime, status.TicketUpdates} Line 16: Repeater1.DataSource = allIssues Line 17: Repeater1.DataBind() Line 18: Line 19:
Source File: E:\web\allegite\Helpdesk\CheckStatus.aspx.vb Line: 17 Stack Trace: [SqlException (0x80131904): Invalid object name 'dbo.Ticket'.] System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) +2074350 System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) +5066444 System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning() +234 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275 System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +86 System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311 System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987 System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior, String method) +141 System.Data.SqlClient.SqlCommand.ExecuteDbDataRead er(CommandBehavior behavior) +12 System.Data.Common.DbCommand.ExecuteReader(Command Behavior behavior) +10 System.Data.EntityClient.EntityCommandDefinition.E xecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443 [EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.] System.Data.EntityClient.EntityCommandDefinition.E xecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +479 System.Data.Objects.Internal.ObjectQueryExecutionP lan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +736 System.Data.Objects.ObjectQuery`1.GetResults(Nulla ble`1 forMergeOption) +149 System.Data.Objects.ObjectQuery`1.GetIListSourceLi stInternal() +13 System.Data.Objects.ObjectQuery.System.ComponentMo del.IListSource.GetList() +10 System.Web.UI.DataSourceHelper.GetResolvedDataSour ce(Object dataSource, String dataMember) +54 System.Web.UI.WebControls.ReadOnlyDataSource.Syste m.Web.UI.IDataSource.GetView(String viewName) +41 System.Web.UI.WebControls.Repeater.ConnectToDataSo urceView() +301 System.Web.UI.WebControls.Repeater.GetData() +13 System.Web.UI.WebControls.Repeater.CreateControlHi erarchy(Boolean useDataSource) +248 System.Web.UI.WebControls.Repeater.OnDataBinding(E ventArgs e) +61 System.Web.UI.WebControls.Repeater.DataBind() +76 Helpdesk_CheckStatus.Page_Load(Object sender, EventArgs e) in E:\web\allegite\Helpdesk\CheckStatus.aspx.vb:17 System.Web.UI.Control.OnLoad(EventArgs e) +91 System.Web.UI.Control.LoadRecursive() +74 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1016

Below are my connection strings.

<connectionStrings>
<clear/>
<add name="AllegitechConnectionString1" connectionString="Data Source=Server; Initial Catalog=DB_allegitech; User ID=DB_allegitech_user; Password=********; Integrated Security=False;" providerName="System.Data.SqlClient"/>
<add name="AllegitechEntities" connectionString="metadata=res://*/App_Code.Allegitech.csdl|res://*/App_Code.Allegitech.ssdl|res://*/App_Code.Allegitech.msl;provider=System.Data.SqlCl ient;provider connection string=" Data Source=Server; Initial Catalog=DB_aspnetdb;User Id=DB_aspnetdb_user;Password=********;Integrated Security=False;multipleactiveresultsets=True" " providerName="System.Data.EntityClient"/>
<add name="LocalSqlServer" connectionString="Data Source=; Initial Catalog=DB_ASPNETDB; User ID=DB_ASPNETDB_User; Password=********; Integrated Security=False;" providerName="System.Data.SqlClient"/>
</connectionStrings>

Any help sending me in the right direction will be much appreciated!

Imar · November 19th, 2013, 04:00 PM

That error seems to suggest that the database didn't get created. How did you create / deploy the database?

Imar (not Omar) ;-)

MStarks · November 19th, 2013, 04:49 PM

Imar,

The Allegitech DB (which would be the same as the PlanetWorx DB) was published to a hosted SQL 2008 R2 DB by means of a script using SQL Server Management Studio. The ASNETDB DB the same way. The App_Code folder I merely copied it to the hosted server.

Imar · November 19th, 2013, 06:55 PM

Can you check the online database to see if it has your tables?

Also, check your connection strings to see if they target the proper server and database, and make sure the account has permissions to access the database. The connection strings seems to be using different user names, so maybe one of them doesn't have the proper permissions.

Cheers.

Imar

MStarks · November 19th, 2013, 08:10 PM

I can view the tables as well as the data in those tables of the on-line DB both from the Website as well as SQL Server Management Studio. I don't have any issues with the Website connecting until the two pages that use LINQ and ADO.NET Entity Framework (Ch. 14). I might also mention that the same pages (forms) work in my local development environment.

Imar · November 19th, 2013, 08:45 PM

I can only think of any of the following:

1. The table really doesn't exist, and EF looks in a different database than you think.

2. The table is in a different schema (e.g. not in dbo.)

3. The user account doesn't have permissions.

The error message says that .NET cannot find the SQL Server table, so it must be SQL related.

Cheers,

Imar

MStarks · November 20th, 2013, 06:47 PM

Imar,

Your book is great! I finally figured it out. I just really didn't understand the connection strings. I just had to get the proper DB in the proper string. Thanks!