locked
Cannot see my data from my LightSwitch App RRS feed

  • Question

  • <dir>

    Hello,

    I’ve created my first LightSwitch app and deployed it but can’t see my data.  Here are the characteristics of my app...

    1. created from Visual Studio 2012 update 2

    2. Forms Authentication

    3. Web based application type

    4. Azure website hosted

    5. SQL Azure database

    I can log on as the Site Administrator and set up my users, roles and permissions (which I've done).  I can also log in with a user account that I have created and navigate around the app, but I can’t see any of my data.  When I first used the LS Publish wizard, I noticed my Sql Express user tables had not been created.  Upon further research, I realized it's my responsibility to upload them to Azure, which is what I've done with SQL Azure Migration Wizard.

    After uploading my tables to Azure, I checked again to see if I can see my data, but all I see is the red X. 

    Question 1: What am I doing wrong, I'm sure it's something simple. Does it have something to do with permissions maybe?

    Question 2: when I set up my website on Azure, I noticed that there are diagnostic options (app and site).  Would this help?  I think I will pursue this anyway, but first I need to setup/configure a storage account (you can't do application logic with out it.

    Question 3: is there a best practice in LS for showing the errors to your user's instead of getting simple a red X?  Can I log the errors using something like ELMAH perhaps?

    Thanks,

    John

    Screen Shots

    </dir>

    John Marsing http://MyHebrewBible.com/

    Tuesday, May 14, 2013 2:47 PM

Answers

  • Hi John,

    AH HA! There's the problem right there:

    "Data Source=MYLAPTOP\SQLEXPRESS"

    There's no way that the middle-tier running in Azure is going to be able to connect to your laptop.. nor would you want it to ;-)

    This means you are indeed using an external (to LightSwitch) data source. (And yes, the "intrinsic" database is what you model directly in LS).

    You will need to publish your external database to SQL Azure manually (looks like you did that). Then in the publish wizard you need to change the "Attached Data Sources" connection string to point to your SQL Azure data:

    HTH,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Friday, May 17, 2013 3:32 PM

All replies

  • Hey John,

    You can get a better understanding of the errors by enabling tracing in your LightSwitch app - check out this blog - Diagnosing Problems in LightSwitch

    That should walk you through on adding some error logs to your application. Check that out and see if it helps if you can.

    Thx - Matt S


    R. Matt Sampson

    Tuesday, May 14, 2013 3:51 PM
  • Will do, thanks

    John Marsing http://MyHebrewBible.com/

    Tuesday, May 14, 2013 4:08 PM
  • Question 1:  Permissions

    Under SQL Database  --> Servers --> Configure, or from one of the individual databases in the server, add your IP address to the allowed IP addresses (it should populate automatically in the "current client" field.  Also set allowed services to yes if it is not already.

    Question 2:  Assuming you successfully logged on to your SQL Azure database from the LightSwitch publish wizard, the connection strings should populate correctly automatically.  I doubt diagnostic options will help your problem, if it is caused by the above.

    Question 3:  When your application is deployed, errors such as this appear as dialog boxes with some specific error information, in this case, "There was a problem connecting to the database."  Don't know much about ELMAH but Fiddler should be very useful.  There are tracing features built into LS that are detailed in "Tracing LightSwitch Applications" from Pro Visual Studio LightSwitch 2011 Development, chapter 17.

    Tuesday, May 14, 2013 4:45 PM
  • I re-published after adding the tracing options and got a 500 status code, see the following errors...

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

    The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
    (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    ....

    What I don't understand is if the app can read the membership tables correct, why can't it read my app tables which are in the same database.


    John Marsing http://MyHebrewBible.com/

    Tuesday, May 14, 2013 5:38 PM
  • Hello AllenAdams,

    looks good to me, see image


    John Marsing http://MyHebrewBible.com/

    Tuesday, May 14, 2013 6:05 PM
  • I am guessing that the obscured fields contains your correct IP address.  I haven't run across too many situations where SQL Azure doesn't want to play nice with Azure websites, but I did occasionally have this problem when I would also publish to non-Azure web hosting sites.

    One issue I had was that the LS publish wizard would become corrupt with data from publishing to different domains.  If this possibly has happened to you, you can try to reset your publish wizard settings and re-publish.

    Thursday, May 16, 2013 4:02 PM
  • AllenAdams,

    thanks for responding.

    You said.

    "One issue I had was that the LS publish wizard would become corrupt with data from publishing to different domains.  If this possibly has happened to you, you can try to reset your publish wizard settings and re-publish."

    I read the reset post "Declaring LightSwitch Publish Wizard Bankruptcy" you suggested but I don't see how this is applicable to me.  First off I'm not getting weird errors during publishing (or republishing), nor am I trying to republish to different targets e.g. from IIS then to Azure, finally I was getting the errors before I republished and the only reason I did republish was so that I could get logging.

    As I said initially "I can log on as the Site Administrator and set up my users, roles and permissions (which I've done).  I can also log in with a user account that I have created and navigate around the app, but I can’t see any of my data."

    I can communicate with the database as long as it's not the application specific tables I created.

    This is very baffling to me, it's like the is security settings for the membership objects (tables, views etc.) are correct, but the tables I created don't have the same permissions.

    Is the problem because my application data was initially created by SQL Express 2012 and uploaded with SQL Migration Wizard? Yet I can log on to the azure server via the azure portal or via SSMS and query both types of tables.

    Is the problem because I'm using the free version of azure?

    I feel like I'm grasping at straws. 

    Has anyone created a similar application like how I've done it (i.e. VS 2012 update 2, free azure website, sql 2012 express & SQL Migration Wizard)?

    I really want to make my LS application succeed so I can feel comfortable doing other applications that I have, but right now I'm stuck.

    Below is the error I'm getting, maybe someone can see something I that I'm missing

    [Microsoft.LightSwitch.DataService][Application:Error][LightSwitchServiceApplication.eswordData:Resources] An exception has occurred:  Microsoft.LightSwitch.DataServiceOperationException: The underlying provider failed on Open. ---> System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
       --- End of inner exception stack trace ---
      at Microsoft.LightSwitch.Threading.DispatcherExtensions.Invoke(IDispatcher dispatcher, Action action)
       at Microsoft.LightSwitch.ServerGenerated.Implementation.DataService`1.LogicInvoke[T](Func`1 f)
       at Microsoft.LightSwitch.ServerGenerated.Implementation.DataService`1.Microsoft.LightSwitch.ServerGenerated.Implementation.IODataService.LogicInvoke[T](Func`1 f)
       at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceQueryProvider.QueryableWrapper`1.ExecuteGeneric[TResult](Expression expression)
       at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceQueryProvider.QueryableWrapper`1.Execute[TResult](Expression expression)
       at System.Linq.Queryable.LongCount[TSource](IQueryable`1 source)
       --- End of inner exception stack trace ---


    John Marsing http://MyHebrewBible.com/

    Thursday, May 16, 2013 10:20 PM
  • Hi John,

    What does your web.config say for connection strings? The part I'm not clear on is whether you have one database you created with LS or you also have an additional external datasource in SQLAzure. I'm confused why you needed the SQL migration wizard at all. If you have two data sources then it makes sense that the security tables (which are loaded into the intrinsic database) are OK and the external data source is not. I would look at your connection strings in the web.config first for clues.

    HTH,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Friday, May 17, 2013 12:43 AM
  • Hi Beth,

    Here are the two connections strings in my server project.

       
    <add name="_IntrinsicData"
        connectionString="Data Source=|SqlExpressInstanceName|;AttachDbFilename=|ApplicationDatabasePath|;
        Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=True" />

    <add name="e77eb667-2c23-4740-ab4b-2e0462152b4a"
        connectionString="Data Source=MYLAPTOP\SQLEXPRESS;Initial Catalog=esword;Integrated Security=True" />

    Could you clarify what is meant by intrinsic data in the LS vernacular?  I thought it was data that I created in LS i.e. tables that I created by using the LS entity designer.  When I initially created my data sources (via the "Attach Data Sources Wizard") I used a sql server express database (esword.mdf) that already existed.  From that database I selected two tables StudyNote and Resource and those are what I'm calling my "app/business tables".  So my question is are these "app tables" should also be called intrinsic? 

    Is this what's happening i.e. when I log on to my LS website, through the browser, it's unaware of my azure database, so it's just resorting to my local sql express database?  I was unaware of the database called "esword\Bin\Data\ApplicationDatabase.mdf" which, upon inspection, has all of the membership tables (obviously created by LS) but none of my "app" tables.  In my head, those tables and only those are "intrinsic" tables.  Is this a wrong assumption?

    If this is true, then I need to better understand the LS publish wizard.  I initially thought that the wizard would bring in my local app tables and create/copy then to the sql azure database along with any "intrinsic" tables that LS created.  After the wizard was completed I looked at the azure database via SSMS and all that was their were the tables and objects from the "ApplicationDatabase.mdf" database.  Upon further research I concluded that it was my responsibility to copy my app tables to the sql azure database.  This is why I used SQL migration wizard.  The implication in your question is that I should have needed to do this.

    Hopefully I've clarified better my problem.

    Thanks,

    John (#JohnMarsing)


    John Marsing http://MyHebrewBible.com/

    Friday, May 17, 2013 3:10 AM
  • Hi John,

    Perhaps Beth can tell from your exception trace exactly what the problem is.  I started out similar to your situation by taking local SQL express databases and migrating to SQL azure.  This was not entirely straightforward because SQL azure does not support the entire DB schema that SQL express does, so even if your migration wizard worked, the SQL azure database schema may be internally different than what LS expects to see and is returning errors.

    Two suggestions:   Before publishing, right-click on every external datasource in your LS project and select "Update Datasource."  Make sure you can connect to the SQL azure table with your entities and there are no errors.  In the publish wizard, make sure under "Data Connections-->Attached Data Sources" you have updated the connection as well to SQL azure and not SQL Express.

    Finally, create a new database in SQL Azure and specify this new database as the publish database for your application data under Data connections.  If your site publishes successfully,  use the "Import and Export Data" in SQL Server or SQL Server Data Tools or your favorite SQL utility program (mine is Navicat for SQL Server as it happens to work with SQL Azure) to transfer the original application data from your SQL express database to this SQL azure database.  In this way you will avoid any problems with the SQL Express-->SQL Azure migration wizard that might be tripping you up.


    Friday, May 17, 2013 1:50 PM
  • Hi John,

    AH HA! There's the problem right there:

    "Data Source=MYLAPTOP\SQLEXPRESS"

    There's no way that the middle-tier running in Azure is going to be able to connect to your laptop.. nor would you want it to ;-)

    This means you are indeed using an external (to LightSwitch) data source. (And yes, the "intrinsic" database is what you model directly in LS).

    You will need to publish your external database to SQL Azure manually (looks like you did that). Then in the publish wizard you need to change the "Attached Data Sources" connection string to point to your SQL Azure data:

    HTH,
    -Beth


    Senior Program Manager, Visual Studio Community http://www.bethmassi.com http://msdn.com/lightswitch http://dev.office.com

    Friday, May 17, 2013 3:32 PM
  • Thanks so much for this question and answer from Beth. It has solved my error.
    Friday, August 9, 2013 1:04 AM