Importing Users into the Database (TSQL)


InstantForum Question
Hi There,

So, I am working on an import script that will import the Users from my SQL database into the InstantForum database. I am 99.9% of the way there but I am running into one small problem. Here are the steps I am taking to import... am I missing anything?- Insert my Users into InstantASP_Users, setting PrimaryRoleID to 2

- Insert Users from InstantASP_Users into InstantForum_Users, setting PermissionID to 2
- Inserting a row into InstantASP_UsersRoles for each UserID (RoleID = 1)
- Inserting a row into InstantASP_UsersRoles for each UserID (RoleID = 2)

I can see all the users in the database, the system allows me to login with one of the users.... but... Once the login is successfull it looks as though I am still logged in as a guest and I am not logged in at all.
Edited
15 Years Ago by DigiChaos
InstantForum Question

I wish I could help.. but I'm not scheduled to start my user import script until this upcoming week.. Obviously, anything you could offer, in regards to tips & tricks (through your experience) would be greatly appreciated.

..:: Bare ::..



Ok, I figured out the issue. I had to do one more step...

- For each user create a row for the three PM folders "PMFolder_InBox", "PMFolder_SentItems", and "PMFolder_DeletedItems" in the table InstantForum_Folders.

This resolves my problem :w00t:


Awesome... I'm going to rate this topic with a bunch of stars :)

Thanks so much!


..:: Bare ::..



Great work, this will help the rest of us when we port our users :)

Hi All,
I've uploaded some updated documentation which offers a little guidance on this...

https://support.instantasp.co.uk/FindPost6756.aspx

See specifically the "Existing User Integration" section. Hope this helps a little. The documentation is not yet complete and i'm very happy to take any suggestions for any additional information you find useful.

http://www.instantasp.co.uk/images/line.gif
Kindest Regards,

Ryan Healey



ryan@instantasp.co.uk
www.instantasp.co.uk
Blog | Community | Docs

http://www.instantasp.co.uk/core/assets/images/email/facebook.png http://www.instantasp.co.uk/core/assets/images/email/twitter.png http://www.instantasp.co.uk/core/assets/images/email/google.png http://www.instantasp.co.uk/core/assets/images/email/linkedin.png

This information has helped me today :) I was going nuts trying to figure out why my registration form was not creating accounts properly in IF til I remembered your post :w00t:

I've done the following as the post suggests:

1.  insert user into InstantASP_Users
2.  insert into InstantASP_UsersRoles
3.  insert user into InstantForum_Users
4.  insert three PM folders into InstantForum_Folders

After items 1-3, I was able to login but it would say I was logged in as guest.  Once I completed step 4, I now get an ASP.NET error.

Exception Type:        System.NullReferenceException
Exception Message:     Object reference not set to an instance of an object.
Exception Source:      InstantASP.InstantForum.UI
Exception Target Site: BuildRightOptions

Any ideas to why this would not work?  We'd like to bulk register our users so I'm first attempting to create one with direct sql inserts to test.

 

Thanks!



The general steps which you are taking look to be correct, below is an excerpt of SQL I'm running to insert users into the Forums tables, hopefully you'll be able to spot some difference between this and yours.If you post a similar (modified if necessary) bit of SQL I'll take a look for anything that looks odd/wrong.



INSERT INTO Forums.dbo.InstantASP_Users

(emailaddress, username, [password], PrimaryRoleID, OurID)

VALUES (

coalesce(@email, 'noemail@noemail.com'),

coalesce(@username, 'nousername'),

coalesce(@password, 'nopasword'),

2, @OurID

)

SET @lastid=scope_identity()

-- set up permissions

INSERT INTO Forums.dbo.InstantASP_UsersRoles

(userID, roleID)

VALUES

(@lastid, 3)

-- set up user table 2

INSERT INTO Forums.dbo.InstantForum_Users

(userID, permissionID)

VALUES

(@lastID, 0)

-- set up users folders

INSERT INTO Forums.dbo.InstantForum_Folders

(UserID, FolderName, Inbox, sortorder)

VALUES

(@lastID, 'PMFolder_InBox', 1, 1)

INSERT INTO Forums.dbo.InstantForum_Folders

(UserID, FolderName, DeletedItems, sortorder)

VALUES

(@lastID, 'PMFolder_DeletedItems', 1, 2)

INSERT INTO Forums.dbo.InstantForum_Folders

(UserID, FolderName, SentItems, sortorder)

VALUES

(@lastID, 'PMFolder_SentItems', 1, 3)





Thanks!

Here's a short but complete script, modified from the 'create database' script.

========================================
INSERT INTO InstantASP_Users (UserID,EmailAddress,Password,Username,PrimaryRoleID,Culture,IPAddress,TimeZoneOffset,DateTimeFormat,TotalVisits,CreatedDate,LastLoginDate,ConfirmationCode,FirstDayOfWeek) VALUES('JasonBourne@dizzycharacters.com','bKj4eFBHbnA=','JBourne','5','','127.0.0.1',-8,'G','0',GetDate(),GetDate(),'','0')
INSERT INTO InstantASP_Users (UserID,EmailAddress,Password,Username,PrimaryRoleID,Culture,IPAddress,TimeZoneOffset,DateTimeFormat,TotalVisits,CreatedDate,LastLoginDate,ConfirmationCode,FirstDayOfWeek) VALUES('MickeyMouse@dizzycharacters.com','bKj4eFBHbnA=','User1','5','','127.0.0.1',-8,'G','0',GetDate(),GetDate(),'','0')
INSERT INTO InstantASP_Users (UserID,EmailAddress,Password,Username,PrimaryRoleID,Culture,IPAddress,TimeZoneOffset,DateTimeFormat,TotalVisits,CreatedDate,LastLoginDate,ConfirmationCode,FirstDayOfWeek) VALUES('DonaldDuck@dizzycharacters.com','bKj4eFBHbnA=','User2','5','','127.0.0.1',-8,'G','0',GetDate(),GetDate(),'','0')
INSERT INTO InstantASP_Users (UserID,EmailAddress,Password,Username,PrimaryRoleID,Culture,IPAddress,TimeZoneOffset,DateTimeFormat,TotalVisits,CreatedDate,LastLoginDate,ConfirmationCode,FirstDayOfWeek) VALUES('Goofy@dizzycharacters.com','bKj4eFBHbnA=','User3','5','','127.0.0.1',-8,'G','0',GetDate(),GetDate(),'','0')
GO

INSERT INTO InstantASP_UsersRoles (UserID,RoleID) VALUES('2','5')
INSERT INTO InstantASP_UsersRoles (UserID,RoleID) VALUES('3','5')
INSERT INTO InstantASP_UsersRoles (UserID,RoleID) VALUES('4','5')
INSERT INTO InstantASP_UsersRoles (UserID,RoleID) VALUES('5','5')
GO

INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('2','PMFolder_DeletedItems',0,0,1,'0','0','1',1)
INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('2','PMFolder_SentItems',0,1,0,'0','0','2',1)
INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('2','PMFolder_InBox',1,0,0,'0','0','3',1)
GO

INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('3','PMFolder_DeletedItems',0,0,1,'0','0','1',1)
INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('3','PMFolder_SentItems',0,1,0,'0','0','2',1)
INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('3','PMFolder_InBox',1,0,0,'0','0','3',1)
GO

INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('4','PMFolder_DeletedItems',0,0,1,'0','0','1',1)
INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('4','PMFolder_SentItems',0,1,0,'0','0','2',1)
INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('4','PMFolder_InBox',1,0,0,'0','0','3',1)
GO

INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('5','PMFolder_DeletedItems',0,0,1,'0','0','1',1)
INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('5','PMFolder_SentItems',0,1,0,'0','0','2',1)
INSERT INTO InstantForum_Folders (UserID,FolderName,InBox,SentItems,DeletedItems,Unread,Total,SortOrder,Required) VALUES('5','PMFolder_InBox',1,0,0,'0','0','3',1)
GO

INSERT INTO InstantForum_Users (UserID,PermissionID,Skin,PublicEmailAddress,WebAddress,BlogAddress,AvatarURL,PhotoURL,ICQ,AIM,Yahoo,MSN,Notes,Location,Occupation,Interests,Biography,PostSignature,DOBDay,DOBMonth,DOBYear,PostCount,LastPostDate,LastPMDateStamp,IsAnonymous,ReceiveEmailFromAdmins,ReceiveEmailFromMembers,EnablePM,EnablePMPopUp,ReceivePMsFromAdmins,ReceivePMEmailNotification,ReceivePMReadReceipts,CreateSentPMCopy,ViewSignatures,ViewImages,ViewEmotIcons,ViewAvatars,TopicsPerPage,PostsPerPage,DefaultDateFilter,DisplayMode,RichTextBoxTemplate,UserLevelTitle,UserLevelImageURL,ExecuteSQL) VALUES('2','5','','','','','','','','','','',NULL,'','','','','','0','0','0','1',NULL,GetDate(),0,1,1,1,1,1,1,0,0,1,1,1,1,'0','0','0','0','1','','',1)
INSERT INTO InstantForum_Users (UserID,PermissionID,Skin,PublicEmailAddress,WebAddress,BlogAddress,AvatarURL,PhotoURL,ICQ,AIM,Yahoo,MSN,Notes,Location,Occupation,Interests,Biography,PostSignature,DOBDay,DOBMonth,DOBYear,PostCount,LastPostDate,LastPMDateStamp,IsAnonymous,ReceiveEmailFromAdmins,ReceiveEmailFromMembers,EnablePM,EnablePMPopUp,ReceivePMsFromAdmins,ReceivePMEmailNotification,ReceivePMReadReceipts,CreateSentPMCopy,ViewSignatures,ViewImages,ViewEmotIcons,ViewAvatars,TopicsPerPage,PostsPerPage,DefaultDateFilter,DisplayMode,RichTextBoxTemplate,UserLevelTitle,UserLevelImageURL,ExecuteSQL) VALUES('3','5','','','','','','','','','','',NULL,'','','','','','0','0','0','1',NULL,GetDate(),0,1,1,1,1,1,1,0,0,1,1,1,1,'0','0','0','0','1','','',1)
INSERT INTO InstantForum_Users (UserID,PermissionID,Skin,PublicEmailAddress,WebAddress,BlogAddress,AvatarURL,PhotoURL,ICQ,AIM,Yahoo,MSN,Notes,Location,Occupation,Interests,Biography,PostSignature,DOBDay,DOBMonth,DOBYear,PostCount,LastPostDate,LastPMDateStamp,IsAnonymous,ReceiveEmailFromAdmins,ReceiveEmailFromMembers,EnablePM,EnablePMPopUp,ReceivePMsFromAdmins,ReceivePMEmailNotification,ReceivePMReadReceipts,CreateSentPMCopy,ViewSignatures,ViewImages,ViewEmotIcons,ViewAvatars,TopicsPerPage,PostsPerPage,DefaultDateFilter,DisplayMode,RichTextBoxTemplate,UserLevelTitle,UserLevelImageURL,ExecuteSQL) VALUES('4','5','','','','','','','','','','',NULL,'','','','','','0','0','0','1',NULL,GetDate(),0,1,1,1,1,1,1,0,0,1,1,1,1,'0','0','0','0','1','','',1)
INSERT INTO InstantForum_Users (UserID,PermissionID,Skin,PublicEmailAddress,WebAddress,BlogAddress,AvatarURL,PhotoURL,ICQ,AIM,Yahoo,MSN,Notes,Location,Occupation,Interests,Biography,PostSignature,DOBDay,DOBMonth,DOBYear,PostCount,LastPostDate,LastPMDateStamp,IsAnonymous,ReceiveEmailFromAdmins,ReceiveEmailFromMembers,EnablePM,EnablePMPopUp,ReceivePMsFromAdmins,ReceivePMEmailNotification,ReceivePMReadReceipts,CreateSentPMCopy,ViewSignatures,ViewImages,ViewEmotIcons,ViewAvatars,TopicsPerPage,PostsPerPage,DefaultDateFilter,DisplayMode,RichTextBoxTemplate,UserLevelTitle,UserLevelImageURL,ExecuteSQL) VALUES('5','5','','','','','','','','','','',NULL,'','','','','','0','0','0','1',NULL,GetDate(),0,1,1,1,1,1,1,0,0,1,1,1,1,'0','0','0','0','1','','',1)
GO



GO

Login
Existing Account
Email Address:


Password:


Social Logins

Select a Forum....







InstantASP Forums


Search