DAL help?

Topics: Developer Discussion, Help, Request
Nov 25, 2007 at 1:59 AM
I've been at this for 6 or 7 hours now and I'm still banging my head against the wall.

Starting with the Standard version 3b1. I've installed the database on my providers SQL 2005 server and I changed the connectionstring.config file an the .data/webconfig files. Both changes allow the site to run just fine on my local machine.

I then changed the connectionstring.config file to map to my server and uploaded the .web/ contents up to the server. Everything seems to run just fine until you go to login. At that point I get an error indicating that aspnet_CheckSchemaVersion can't be found. After working on this issue for a long time, downloading installaing and executing the subsonic tools, etc... I dropped back and punted.

I downloaded the Express(vb) version of the site. I changed the connectionstring.config file and the site ran on my local machine just fine. I changed the connectionstring.config file to match the needs of my server and replaced all of the code on the server with the express version (I left the database schema and data on the SQL server untouched.)

Now when I request the default.aspx page I get the following error:
Server Error in '/' Application.

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30560: 'Tables' is ambiguous in the namespace 'ClubStarterKit.Data'.

Source Error:

Line 9: Public Class Events
Line 10: Public Shared Sub Delete(ByVal Id As String)
Line 11: Dim eventqry As New Query(Tables.ClubEvent)
Line 12: eventqry.QueryType = QueryType.Delete
Line 13: eventqry.AddWhere(ClubEvent.Columns.ID, Id)

Source File: D:\Webs\user12003f\App_Code\BLL\Events.vb Line: 11

I think the issue lies with the subsonic generated code but I'm just not sure how to troubleshoot the issue at this point. If anyone has any ideas or can point me towards a resource to help me figure the problem out I'd really appreciate it.


Nov 25, 2007 at 2:38 AM
Are you able to regenerate your DAL? See the docs on how to do this. That would make sure your connection string is right. Did you change ANY thing else? Like the connection string name? Or the provider?
Dec 2, 2007 at 11:01 PM

z2bass wrote:
Are you able to regenerate your DAL? See the docs on how to do this. That would make sure your connection

string is right. Did you change ANY thing else? Like the connection string name? Or the provider?

Thanks for your reply.
In order to verify that nothing else was changed, I started over from scratch. I erased my local copy and downloaded a fresh copy of the express version from the distribution site.

I changed the path to the .mdf file in the connectionstrings.config file and executed the site locally.

Everything seems to work great.

Next, I went to the production server and wiped out all code and uploaded a new copy of the site. I changed the connectionstrings.config file to:
<add name="ClubSiteDB"
connectionString="Data Source=.\SQLExpress;Integrated Security=SSPI;"

In this case, the actual connection string is a copy and paste from the providers instructions. Of course, it's intended to be used from within their IIS host so I can't use it on my dev machine remotely.

I used the instructions for generating the express Dal and requested the dev/generators.aspx page.

I specified a folder under my account on the IIS machine under /app_code but access was denied (no surprise.)

I used a different folder under the root of the site and all of the files generated OK. I then used FTP to move those files into the original /app_code/DAL/Generated folder and deleted them from the root level folder.

At this point everything should be ready to go so I requested the default page. It returned from the server just fine but when I tried to log in, I got the same error as before.

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Next, I dropped all tables, views and stored procedures on the production SQL server and re-generated them using schema.sql and data.sql included with the express edition.

No change. I then re-created the DAL again against the new version of the database.

Again, the site seems OK, but as soon as you request the default.aspx page the following error is returned.

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.
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: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Source Error:

Line 236: <asp:HyperLink ID="HyperLink1" runat="server" Text='<%# Eval("Title") %>' NavigateUrl='<%# "~/blogs/post.aspx?id=" & Eval("postid").ToString %>'></asp:HyperLink></strong></td>
Line 237: <td style="width: 437px; height: 28px; text-align: right">
Line 238:  
Dec 18, 2007 at 3:10 PM
Edited Dec 20, 2007 at 1:14 PM
Problems with DAL, we are at the beginning ...

The choice of Subsonic seems great but it is now a part of the project and we (all users) need to know how to generate the DAL.
If you use sonic.exe to generate 'DAL source code' with the ClubDB.mdf you will not obtain the source code in ClubStarterKit.Data\Generated differences are significatives !
You must know how to use SubSonic if you use the wrong command parameters you will not obtain the right code, like me during tree days.

News from DAL's problem :
I find the "small gye" ! Trying to solve problems with DAL, I found this connection string :

connectionString="Data Source=.\SQLExpress;Integrated Security=true;AttachDBFileName=C:\Users\Zack\Documents\ClubSK\ClubStarterKit\ClubStarterKit.Web\App_Data\ClubDB.mdf;User Instance=True"

In a ASP.NET 2.0 it is usfull to change it to the following connection string :
connectionString="Data Source=.\SQLExpress;Integrated Security=true;AttachDBFileName=|DataDirectory|ClubDB.mdf;User Instance=True"

This make that the ClubDB works on Zack's computer but not on my computer !

I solve my problems with the DAL :

During tree days I had use the following batch to generate DAL's code :
>sonic generate /server .\SQLExpress /db ClubDB.mdf /out ClubStarterKit.Data/Generated /generatedNamespace ClubStarterKit.Data /lang vb

I was asking my self why ! I do not obtain the right code. And I found the SubSonic commands !
Then I open the file ClubStarterKit.Data\App.config it looks like Subsonic commands.
So I've tryed to apply this commands in my batch that is now looking like :

>sonic generate /server .\SQLExpress /lang vb /db ClubDB.mdf /out ClubStarterKit.Data/Generated /generatedNamespace ClubStarterKit.Data /fixPluralClassNames false /spClassName SPs

Then it works !!!!!!!!!!!!
Dec 18, 2007 at 3:29 PM
Edited Dec 20, 2007 at 1:13 PM
Dec 20, 2007 at 12:14 PM
Edited Dec 20, 2007 at 1:22 PM
Sorry for the small gye that put 's' on objects it was only a Subsonic commands.

I do forget a Subsonic command : /provider ClubStarterKitData