|
Here is the SQL code you need to run to make this work in MySQL 5. This also makes the UserID code an autoincrement key.
<br />
CREATE TABLE aspnet_profile (<br />
PKID varchar(36) collate latin1_general_ci NOT NULL default '',<br />
LastUpdatedDate datetime default NULL,<br />
PropertyNames text default NULL,<br />
PropertyValuesString text default NULL<br />
);<br />
<br />
<br />
CREATE TABLE aspnet_applications (<br />
ApplicationName text default NULL,<br />
ApplicationId int(11) default NULL,<br />
Description text default NULL,<br />
PropertyValuesString text default NULL,<br />
PRIMARY KEY (`ApplicationId`)<br />
);<br />
<br />
CREATE TABLE `users` (<br />
PKID varchar(36) NOT NULL default '',<br />
Username varchar(255) NOT NULL default '',<br />
ApplicationName varchar(100) NOT NULL default '' ,<br />
Email varchar(100) NOT NULL default '',<br />
`Comment` varchar(255) default NULL,<br />
`Password` varchar(128) NOT NULL default '',<br />
PasswordQuestion varchar(255) default NULL,<br />
PasswordAnswer varchar(255) default NULL,<br />
IsApproved tinyint(1) default NULL,<br />
LastActivityDate datetime default NULL,<br />
LastLoginDate datetime default NULL,<br />
LastPasswordChangedDate datetime default NULL,<br />
CreationDate datetime default NULL,<br />
IsOnLine tinyint(1) default NULL,<br />
IsLockedOut tinyint(1) default NULL,<br />
LastLockedOutDate datetime default NULL,<br />
FailedPasswordAttemptCount int(11) default NULL,<br />
FailedPasswordAttemptWindowStart datetime default NULL,<br />
FailedPasswordAnswerAttemptCount int(11) default NULL,<br />
FailedPasswordAnswerAttemptWindowStart datetime default NULL,<br />
ApplicationId bigint(20) default NULL,<br />
MobileAlias text NULL,<br />
IsAnonymous varchar(5) NULL default NULL,<br />
PRIMARY KEY (`PKID`)<br />
);<br />
modified on Friday, April 25, 2008 4:56 PM
|
|
|
|
|
Hi
I have created the tables as stated here.
But I still get a an 'Object not set to an instance of an object' error from this line 411
410 cmd = new MySqlCommand("SELECT PKID FROM aspnet_Profile WHERE PKID = '" + userId + "'", conn, trans);
411 object result = cmd.ExecuteScalar();
I have a good PKID value from the users table but the aspnet_profile table is empty so no records are returned from line 410.
I am attempting to create the profile when the user is initialy created.
Am I missing something here?
All the best
Hawkmoth
|
|
|
|
|
Ok think I got around it.
This is the original code in the MySQLProfileProvider.cs
410 cmd = new MySqlCommand("SELECT PKID FROM aspnet_Profile WHERE PKID = '" + userId + "'", conn, trans);
411 object result = cmd.ExecuteScalar();
412 string userid1 = result.ToString();
413 string userid2 = userId.ToString();
414 if (result != null && (userid1 == userid2)) {
415 cmd = new MySqlCommand("UPDATE aspnet_Profile SET PropertyNames ='" + names + "', PropertyValuesString ='" + values + "', LastUpdatedDate ='" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "' WHERE PKID ='" + userId + "'", conn, trans);
416 }
417 else {
418 cmd = new MySqlCommand("INSERT INTO aspnet_Profile (PKID, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES ('" + userId + "','" + names + "','" + values + "','" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "')", conn, trans);
419 }
Line 412 fails cause result is null due to this being a new user with now profile yet.
userid1 is only used in line 414 as a check to make sure the profile matches the user.
So I moved it so the code now reads:-
410 cmd = new MySqlCommand("SELECT PKID FROM aspnet_Profile WHERE PKID = '" + userId + "'", conn, trans);
411 object result = cmd.ExecuteScalar();
412 if (result != null ) {
413 string userid1 = result.ToString();
414 string userid2 = userId.ToString();
415 if (userid1 == userid2){
416 cmd = new MySqlCommand("UPDATE aspnet_Profile SET PropertyNames ='" + names + "', PropertyValuesString ='" + values + "', LastUpdatedDate ='" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "' WHERE PKID ='" + userId + "'", conn, trans);
417 }
418 } else {
419 cmd = new MySqlCommand("INSERT INTO aspnet_Profile (PKID, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES ('" + userId + "','" + names + "','" + values + "','" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "')", conn, trans);
420 }
All the best
Hawkmoth
modified on Thursday, August 7, 2008 11:04 AM
|
|
|
|
|
I noticed the same thing. In particular, changing the name in the aspnet_Profile table from UserId to PKID and changing the type of the same field from BIGINT to VARCHAR(36).
Also, I had to write code to insert an entry for the ApplicationName in the aspnet_Applications table to make sure it exists.
Finally I made the same changes as Hawkmoth with one exception:
410 cmd = new MySqlCommand("SELECT PKID FROM aspnet_Profile WHERE PKID = '" + userId + "'", conn, trans);
411 object result = cmd.ExecuteScalar();
412 if (result != null ) {
413 string userid1 = result.ToString();
414 string userid2 = userId.ToString();
415 if (userid1 == userid2){
416 cmd = new MySqlCommand("UPDATE aspnet_Profile SET PropertyNames ='" + names + "', PropertyValuesString ='" + values + "', LastUpdatedDate ='" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "' WHERE PKID ='" + userId + "'", conn, trans);
417 }
418 else {
419 cmd = new MySqlCommand("INSERT INTO aspnet_Profile (PKID, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES ('" + userId + "','" + names + "','" + values + "','" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "')", conn, trans);
420 }
421 } else {
422 cmd = new MySqlCommand("INSERT INTO aspnet_Profile (PKID, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES ('" + userId + "','" + names + "','" + values + "','" + DateTime.Now.ToString("yyyy:MM:dd hh:mm:ss") + "')", conn, trans);
423 }
The else clause at line 418 will make sure we don't fall though without initializing cmd at all.
|
|
|
|
|
First, we have to confige Web.config to accept anonymous user's operation
<^providers>
<^add name="MySqlProfileProvider"
type="Malachi.MySqlProviders.MySqlProfileProvider"
connectionStringName="ReportConnectionString2"
applicationName="Pro_report"/>
<^/providers>
<^properties>
<^add name="lastName" type="string" allowAnonymous="true" />
<^add name="firstName" type="string" allowAnonymous="true" />
<^/properties>
</profile>
<^anonymousIdentification
enabled="true"
cookieName=".ASPXANONYMOUS"
cookieTimeout="43200"
cookiePath="/"
cookieRequireSSL="false"
cookieSlidingExpiration="true"
cookieProtection="All"
cookieless="UseCookies"/>
Secondly, on line 388, string username = (string)sc["UserName"];
I actually get a value which equal to PKID's value when I create a user as anonymous user!!!!!
It will cause line 407 return a null value and fail to add a new profile record.
cmd = new MySqlCommand("SELECT PKID FROM Users WHERE UserName = '" + username + "'", conn, trans);
So, we have to let a visitor create a user and auto login first and then add profile records.
Thirdly, In "Get Profiles" Methods,the query command try to search the field "IsAnonymous" which is not exist in users table
line 690,700,710,721 string sqlQuery = "SELECT u.UserName, u.IsAnonymous,....
-- modified at 0:09 Monday 7th May, 2007
|
|
|
|
|
If you look at the table setup from the original member and role provider article there is a "IsAnonymous" field.
I am posting an updated zip file with all three source files. The Role,Membership, and profile source can be download from this article.
|
|
|
|
|
when I go to execute the command to create tables:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''aspnet_applications' (
'ApplicationName' text,
'ApplicationId' int(11) NO' at line 1
thanks!!!
"A terra é redonda mais nela cabem varias bestas quadradas"
|
|
|
|
|
|
I couldn't find the source too?
Nobody can provide a new reference to the source?
Thanks a lot.;)
|
|
|
|
|
Ditto, 404 File not found.
|
|
|
|
|
It's this one: http://www.codeproject.com/aspnet/MySQLMembershipProvider.asp
|
|
|
|
|
its all right in the schema and on the tables, but i'm getting this error, the membership and role provider is working good, but the provider have a lot of null reference.
|
|
|
|
|
Hi,
I have seen some strange behaviour with this, I'm not sure if it's by design or not, but let me explain.
I have a very basic page (just for testing this) that has one label. The Code-behind has the following code in the Page_Load function:
Label1.Text = Profile.FirstName;
Ok, that's fine, it calls GetPropertyValues as it should, but for some reason after it's done that, it calls SetPropertyValues as well - have you noticed it doing this? Do you know if it's supposed to do this? I would have thought that if you are simply reading a value then it would only need to call GetPropertyValues.
Thanks
Andy
|
|
|
|
|
I noticed that when I ported this from the Microsoft Access version. I don't know why that is in there. At the time I just wanted to get this to work with MySQL and the membership provider. I didn't really take time to optimize the code. I will take a look at it when I get a chance.
|
|
|
|
|
Has anybody got this working who is using Web Application Projects?
Ben.
|
|
|
|
|
Are you talking about smart apps or asp.net pages?
|
|
|
|
|
ASP.NET Web Application Projects. If you create web sites as a web application, it breaks the profile provider. There is a fix here: http://www.gotdotnet.com/workspaces/workspace.aspx?id=406eefba-2dd9-4d80-a48c-b4f135df4127
but i can't get it to work with your mysql version of the profile provider (not that I’ve tried that hard). I just wondered if anyone else had got it working?
|
|
|
|
|
There are an error in the code, in the method "SetPropertyValues".
The following will give an "Object reference not set to an instance of..." exception, if you create a new user from scratch. As no information about the user is in the aspnet_profile table, "result" will be null, and therefore "result.ToString()" will produce the exception.
Code snippet:
--------------
cmd = new MySqlCommand("SELECT PKID FROM aspnet_profile WHERE PKID = '" + userId + "'", conn, trans);
object result = cmd.ExecuteScalar();
string userid1 = result.ToString();
Also, there are errors in the SQL statements written in the beginning of the code. Can't remember where, but I belive it was the script creating the users table. A charset decleration and a "default" kayword spelled as "deafult".
Otherwise great job!!!
|
|
|
|
|
The UserId field in the aspnet_profile table should be an auto increment. This will fix your problem.
-- modified at 16:24 Friday 6th October, 2006
|
|
|
|
|
I had the same Problem and solved it like this:
Line 410f:
-----------------
cmd = new MySqlCommand("SELECT PKID FROM aspnet_Profile WHERE PKID = '" + userId + "'", conn, trans);
object result = cmd.ExecuteScalar();
// string userid1 = result.ToString();
string userid2 = userId.ToString();
if (result != null && (result.ToString() == userid2)) {
// Not modified (UPDATE)
}
else {
// Not modified (INSERT)
}
------------------
Note that in your original Version you're testing 'result' for 'null', but used a method of it before.
|
|
|
|
|
I have taken this project and through multiple headaches have converted it to vb.net. My current website is already done in vb.net and I THOUGHT it would be easier to convert this to vb instead of converting my site to C# (I tried the post from earlier on getting this to work for vb.net but couldn't get it to work and really didn't want to do it that way). Anyway, I've got all the code translated and am stuck with the following error when I try and add users via the asp.net configuration page...
--------- Error Message starts ------------------------------------
"An error was encountered. Please return to the previous page and try again.
The following message may help in diagnosing the problem: Object reference not set to an instance of an object. (C:\Documents and Settings\Administrator.REYNOLDSMCP\My Documents\Visual Studio 2005\WebSites\TheReynoldsFam\web.config line 87) at System.Web.Administration.WebAdminPage.CallWebAdminHelperMethod(Boolean isMembership, String methodName, Object[] parameters, Type[] paramTypes) at ASP.security_users_adduser_aspx.PopulateCheckboxes() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles\Security\Users\addUser.aspx:line 28 at ASP.security_users_adduser_aspx.Page_Load() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles\Security\Users\addUser.aspx:line 22 at System.Web.Util.CalliHelper.ArglessFunctionCaller(IntPtr fp, Object o) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) "
--------- Error Message ends ------------------------------------
This is what my web.config page looks like...(it's blowing up on the line
type="Andri.Web.MySqlRoleProvider"
------Code starts-------
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appsettings>
<connectionstrings>
<add name="ConnString"
="" connectionstring="Database=thereynoldsfam;Data Source=localhost;User Id=root;Password=root">
<system.web>
<authentication mode="Forms">
<compilation debug="true" strict="false" explicit="true">
<pages>
<namespaces>
<clear>
<add namespace="System">
<add namespace="System.Collections">
<add namespace="System.Collections.Specialized">
<add namespace="System.Configuration">
<add namespace="System.Text">
<add namespace="System.Text.RegularExpressions">
<add namespace="System.Web">
<add namespace="System.Web.Caching">
<add namespace="System.Web.SessionState">
<add namespace="System.Web.Security">
<add namespace="System.Web.Profile">
<add namespace="System.Web.UI">
<add namespace="System.Web.UI.WebControls">
<add namespace="System.Web.UI.WebControls.WebParts">
<add namespace="System.Web.UI.HtmlControls">
<add namespace="MySql.Data">
<add namespace="MySql.Data.MySqlClient">
<add namespace="Andri.Web">
<membership defaultprovider="MySQLMembershipProvider" userisonlinetimewindow="15">
<providers>
<add
name="MySQLMembershipProvider"
="" type="Andri.Web.MySQLMembershipProvider" connectionstringname="ConnString" applicationname="TheReynoldsFam" enablepasswordretrieval="false" enablepasswordreset="true" requiresquestionandanswer="true" requiresuniqueemail="true" passwordformat="Hashed" writeexceptionstoeventlog="false">
<rolemanager defaultprovider="MySqlRoleProvider"
="" enabled="true" cacherolesincookie="true" cookiename=".ASPROLES" cookietimeout="30" cookiepath="/" cookierequiressl="false" cookieslidingexpiration="true" cookieprotection="All">
<providers>
<add
name="MySqlRoleProvider"
="" <b="">type="Andri.Web.MySqlRoleProvider"
connectionStringName="ConnString"
applicationName="TheReynoldsFam"
writeExceptionsToEventLog="false"
/>
------- code ends --------
ANY help would be awesome. I'm really stuck here and am looking for a little light anyone might have to shed.
Thanks again!
|
|
|
|
|
I am a little confused. Did you convert the Role Provider? Or did you convert the Role Provider, Profile Provider, and Membership provider?
FYI and maybe this will help, If you have a vb.net project you can still debug a c# class file if you only have C# code files in your App_Code folder. Or you can start a seperate c# web project to debug with just a skeleton to get the user,membership, and profile stuff working. Then after everything is working compile your c# class files into vb.net dlls.
|
|
|
|
|
Check that your UserId field in the aspnet_profile table is set to autoincrement. This will prevent the field from being NULL which is causing the runtime error.
|
|
|
|
|
1. Do we need to create a new schema (database) in MySql, and add the tables. It confuses me because the default MySql schema already has a users table, but I am assuming we need a new database (schema) in the MySql environment for the tables that have been shown in the MembershipAndRoles project, plus tables in your Profile project.
2. Using Windows 2000 for MySql 5.0, and developing in VS 2005 on Windows XP. Should all the fields and tables be lower case. Though I read something about windows converting some to lower case.
Of course, MySql and C# are also case sensitive.
|
|
|
|
|
Yes, You need to create a new Database on your MySQL Server. Do not modify the default database MySql that will alter your MySql Server user information! I would follow the schema specs and if that means lowercase then so be it. You might be ok on a Windows server but if you are on a linux server you might have a problem with case. Just be consistent in what you choose to do.
|
|
|
|
|