ASPNETDB.mdf "...already in use..." Error and Broader Questions

When you create and host an ASP.Net 2.0 site on your local computer and then configure .Net 2.0's drop-in user registration and management system it creates a SQL file named ASPNETDB.mdf with a whole bunch of tables and stored procedures (…as most of you know). If you add your own custom tables to that database file and then try to call those tables you get an error that says that ASPNETDB.mdf is already in use. One way to get around this is to create a separate database in the form of another ".mdf" file and then put all of your own custom tables in it. You avoid the "...already in use..." errors that way but all your user accounts and the primary key structure that identifies them are in the original ASPNETDB.mdf file. This makes it impossible to do primary/foreign key relationships between those tables and those in the new separate database you created. It's kind of a catch-22 situation...unless I'm missing something that relates to releasing the ASPNETDB.mdf file from use whenever it is called so that additional tables and queries against that database file can be made without the "…already in use…" error cropping up. I am wondering if this problem is because the ASPNETDB.mdf file is not a "real" SQL database and as such imposes multiple-connectivity limitations such as those I am seeing. If this is true, migrating to a real SQL database would alleviate this? Finally, from a broader security and scalability standpoint…what are the best practices relating to use of the ASPNETDB.mdf database for all your custom tables? Should an additional database be created for all my application's custom tables (leading to the primary/foreign key problems) or should the additional tables be put into the ASPNETDB.mdf file (with some way of working around the "…already in use…" error)? A long-winded and broad question…thanks in advance for any responses.

MDCragg

The default membership provider already has an open connection to the file, which seems like the reason why you can't make your own connection. Perhaps you could extend this provider to keep its current functionality, but add your own requirements to access the other tables, as well. The default provider is the System.Web.Security.SqlMembershipProvider class. After you create this, set it as the default provider in your web.config.

Sample on how to configure a membership provider in web.config:http://msdn2.microsoft.com/en-us/library/44w5aswa.aspx

|||

That article tells how to merge the membership provider tables and stored procedures into my own database. I will try this to see if it solves the problem. I am at least a little bit doubtful if it will though because it seems like it will just shift the same persistent and exclusive connectivity problem I am having from the current user account database to my own database.

|||

This seems to have worked. I installed SQL Server Management Studio Express. I wasn't able to navigate to the existing custom database that I had created so I copied it to the default directory that SQL Server MSE utilizes. I was able to connect to it there and thus "attach" it to my PC's SQL host. Once that was done I was able to use the aspnet_regsql.exe utility to populate that database with all of .Net's Membership tables, views, stored procedures, etc. I copied the database back to the App_Data folder. I adjusted all the Membership entries in the web.config folder to "point to" the custom database instead of the ASPNETDB.mdf file (which I removed). I did some additional tweaking with things such as connection strings. Then I launched the site and everything seemed to work. I am able to connect to the .Net Membership tables as well as all my own tables...all of which exist in the one custom database file.

So, this is fixed although I don't know what the difference was between the ASPNETDB.mdf file and the custom ".mdf" file that I created. I'm sure there is a setting or two somewhere in the database instance, the connection, or something or other that led to the difficulty.

ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)

I just ran the utility without any commands ant the wizard has come up. This is all independent of my web application.

I am guessing this is going to install the database tables to my existing database.UPDATE: They have been created

Will I then connect my web app to this database schema that is created?

How do I change the membership/roles users in my web app to point to the database tables?

To connect you app to the membership database you define the membership provider in web.config
main setting are connectionstring and application name.

Here a link with more info about the settings for you

http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

To manage the membership role and user you can use ASP.NET Configuration tool you will
find it on the website menu in visual web developer.

Hope that helps you.

|||

Weird, there is only 1 thing in my applications web config that references the roles and memberships..

<roleManager enabled="true" />

What should I be doing to connect the current roles and membership to the new SQL Server 2000 tables that the utility created?
In the ASP.NET Configuration tool, there is a provider tab, in this area there is a AspNetSqlProvider and a radio button, there is also a test link.

When I click the test link it eventually fails, even though I have created all the necessary tables in the 2000 database.

|||

Ok you will need to setup a connection string to you database and define the membership provider.

Here is example for you

<connectionStrings><remove name="LocalSqlServer"/><!-- Connection for SQL 2000 --><add name="LocalSqlServer" connectionString="Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;" providerName="System.Data.SqlClient"/></connectionStrings> <membership> <providers> <clear/> <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true"requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/" /> </providers></membership>

You then can setup the users and roles using the asp.net confrigation tool you will find it in the website menu in visual web developer express

I hope this helps

|||

Ok, I will try to get farther. I am suprised none of this was added to the Web.config file as I have a working membership login ability... anyone know why?

Also, for the record, I am using Visual Studio 2005Standard Edition, will that change anything?

What is the situation with <remove name="...">

Are you just clearing the existing pair/key value? Is that needed?

|||

I think the logic around why it is not added is to allow you to define the setting ie because you are using SQL 2000 or 2005.

No problem with a higher version just lots of people here are using express.

Yes you are right about the remove name, it just to make sure you are overriding the system defaults.

Hope it helps

Asp.net 2.0 Registration: Must declare the scalar variable @BillingAddress

I started to receive this error as soon I added the line"DataSource.SelectParameters.Clear()" Otherwise I was getting"variableuserid already decalred". I'm pasting the code below, please let meknow what I'm doing wrong. I think it is a minor problem.

1<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Registration.aspx.vb" Inherits="Members_Registration" Trace="true" %>23<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">45<html xmlns="http://www.w3.org/1999/xhtml" >6<head runat="server">7 <title>Untitled Page</title>8</head>9<body>10 <form id="form1" runat="server">11 <div>12 <asp:CreateUserWizard ID="CreateUserWizard1" runat="server" BackColor="#FFFBD6" BorderColor="#FFDFAD" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em" OnCreatedUser="CreateUserWizard1_CreatedUser">13 <WizardSteps>14 <asp:WizardStep ID="CreateUserWizardStep0" runat="server">15 <table>16 <tr>17 <th>Billing Information</th>18 </tr>19 <tr>20 <td>Billing Address:</td>21 <td>22 <asp:TextBox runat="server" ID="BillingAddress" MaxLength="50" />23 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator1" ControlToValidate="BillingAddress"24 ErrorMessage="Billing Address is required." />25 </td>26 </tr>27 <tr>28 <td>Billing City:</td>29 <td>30 <asp:TextBox runat="server" ID="BillingCity" MaxLength="50" Columns="15" />31 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator2" ControlToValidate="BillingCity"32 ErrorMessage="Billing City is required." />33 </td>34 </tr>35 <tr>36 <td>Billing State:</td>37 <td>38 <asp:TextBox runat="server" ID="BillingState" MaxLength="25" Columns="10" />39 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator3" ControlToValidate="BillingState"40 ErrorMessage="Billing State is required." />41 </td>42 </tr>43 <tr>44 <td>Billing Zip:</td>45 <td>46 <asp:TextBox runat="server" ID="BillingZip" MaxLength="10" Columns="10" />47 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator4" ControlToValidate="BillingZip"48 ErrorMessage="Billing Zip is required." />49 </td>50 </tr>51 </table>52 </asp:WizardStep>53 <asp:WizardStep ID="CreateUserWizardStep1" runat="server">54 <table>55 <tr>56 <th>Shipping Information</th>57 </tr>58 <tr>59 <td>Shipping Address:</td>60 <td>61 <asp:TextBox runat="server" ID="ShippingAddress" MaxLength="50" />62 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator5" ControlToValidate="ShippingAddress"63 ErrorMessage="Shipping Address is required." />64 </td>65 </tr>66 <tr>67 <td>Shipping City:</td>68 <td>69 <asp:TextBox runat="server" ID="ShippingCity" MaxLength="50" Columns="15" />70 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator6" ControlToValidate="ShippingCity"71 ErrorMessage="Shipping City is required." />72 </td>73 </tr>74 <tr>75 <td>Shipping State:</td>76 <td>77 <asp:TextBox runat="server" ID="ShippingState" MaxLength="25" Columns="10" />78 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator7" ControlToValidate="ShippingState"79 ErrorMessage="Shipping State is required." />80 </td>81 </tr>82 <tr>83 <td>Shipping Zip:</td>84 <td>85 <asp:TextBox runat="server" ID="ShippingZip" MaxLength="10" Columns="10" />86 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator8" ControlToValidate="ShippingZip"87 ErrorMessage="Shipping Zip is required." />88 </td>89 </tr>90 </table>91 </asp:WizardStep>92 <asp:CreateUserWizardStep ID="CreateUserWizardStep2" runat="server">93 <ContentTemplate>94 <table>95 <tr>96 <th>User Information</th>97 </tr>98 <tr>99 <td>Username:</td>100 <td>101 <asp:TextBox runat="server" ID="UserName" />102 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator9" ControlToValidate="UserName"103 ErrorMessage="Username is required." />104 </td>105 </tr>106 <tr>107 <td>Password:</td>108 <td>109 <asp:TextBox runat="server" ID="Password" TextMode="Password" />110 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator10" ControlToValidate="Password"111 ErrorMessage="Password is required." />112 </td>113 </tr>114 <tr>115 <td>Confirm Password:</td>116 <td>117 <asp:TextBox runat="server" ID="ConfirmPassword" TextMode="Password" />118 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator13" ControlToValidate="ConfirmPassword"119 ErrorMessage="Confirm Password is required." />120 </td>121 </tr>122 <tr>123 <td>Email:</td>124 <td>125 <asp:TextBox runat="server" ID="Email" />126 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator11" ControlToValidate="Email"127 ErrorMessage="Email is required." />128 </td>129 </tr>130 <tr>131 <td>Question:</td>132 <td>133 <asp:TextBox runat="server" ID="Question" />134 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator12" ControlToValidate="Question"135 ErrorMessage="Question is required." />136 </td>137 </tr>138 <tr>139 <td>Answer:</td>140 <td>141 <asp:TextBox runat="server" ID="Answer" />142 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator14" ControlToValidate="Answer"143 ErrorMessage="Answer is required." />144 </td>145 </tr>146 <tr>147 <td colspan="2">148 <asp:CompareValidator ID="PasswordCompare" runat="server" ControlToCompare="Password"149 ControlToValidate="ConfirmPassword" Display="Dynamic" ErrorMessage="The Password and Confirmation Password must match."></asp:CompareValidator>150 </td>151 </tr>152 <tr>153 <td colspan="2">154 <asp:Literal ID="ErrorMessage" runat="server" EnableViewState="False"></asp:Literal>155 </td>156 </tr>157 </table>158159160 <asp:SqlDataSource ID="InsertExtraInfo" runat="server"161 ConnectionString="<%$ ConnectionStrings:Member %>"162 InsertCommand="INSERT INTO UserAddress (Userid, BillingAddress, BillingCity,BillingState,BillingZip, ShippingAddress,ShippingCity,ShippingState,ShippingZip) VALUES (@.userid, @.BillingAddress, @.BillingCity, @.BillingState, @.BillingZip, @.ShippingAddress, @.ShippingCity, @.ShippingState, @.ShippingZip)"163 SelectCommand="SELECT UserAddress.* FROM UserAddress">164 <InsertParameters>165 <asp:ControlParameter Name="BillingAddress" Type="String" ControlID="BillingAddress" PropertyName="Text" />166 <asp:ControlParameter Name="BillingCity" Type="String" ControlID="BillingCity" PropertyName="Text" />167 <asp:ControlParameter Name="BillingState" Type="String" ControlID="BillingState" PropertyName="Text" />168 <asp:ControlParameter Name="BillingZip" Type="String" ControlID="BillingZip" PropertyName="Text" />169 <asp:ControlParameter Name="ShippingAddress" Type="String" ControlID="ShippingAddress" PropertyName="Text" />170 <asp:ControlParameter Name="ShippingCity" Type="String" ControlID="ShippingCity" PropertyName="Text" />171 <asp:ControlParameter Name="ShippingState" Type="String" ControlID="ShippingState" PropertyName="Text" />172 <asp:ControlParameter Name="ShippingZip" Type="String" ControlID="ShippingZip" PropertyName="Text" />173 </InsertParameters>174 </asp:SqlDataSource>175176 </ContentTemplate>177 </asp:CreateUserWizardStep>178 <asp:CompleteWizardStep ID="CompleteWizardStep1" runat="server">179 </asp:CompleteWizardStep>180 </WizardSteps>181 <NavigationButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"182 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />183 <HeaderStyle BackColor="#FFCC66" BorderColor="#FFFBD6" BorderStyle="Solid" BorderWidth="2px"184 Font-Bold="True" Font-Size="0.9em" ForeColor="#333333" HorizontalAlign="Center" />185 <CreateUserButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"186 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />187 <ContinueButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"188 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />189 <SideBarStyle BackColor="#990000" Font-Size="0.9em" VerticalAlign="Top" />190 <TitleTextStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />191 <SideBarButtonStyle ForeColor="White" />192 </asp:CreateUserWizard>193194 </div>195196 </form>197</body>198</html>199200  
PartialClass Members_RegistrationInherits System.Web.UI.PageProtected Sub CreateUserWizard1_CreatedUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles CreateUserWizard1.CreatedUserDim UserNameTextBoxAs TextBox =DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("UserName"), TextBox)Dim DataSourceAs SqlDataSource =DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("InsertExtraInfo"), SqlDataSource)Dim UserAs MembershipUser User = Membership.GetUser(UserNameTextBox.Text)Dim UserGUID UserGUID = User.ProviderUserKey Trace.Write("userid", UserGUID.ToString) DataSource.SelectParameters.Clear()' DataSource.InsertParameters.Clear() DataSource.InsertParameters.Add("UserId", UserGUID.ToString) DataSource.Insert()End SubEnd Class
 

Maybe a typo..... Dim UserGUID? Should be Dim UserGUID as GUID? I think I had a problem with that parameters.clear method also, so I added a check to see if there are any parameters before clearing,

IfMe.UsersObjectDataSource.SelectParameters.Count > 0Then

Me.UsersObjectDataSource.SelectParameters.Clear()

EndIf

Burl