best way to write to DB for ASP 2.0 project?

Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.

@.UserNamenvarchar(128),
@.Emailnvarchar(50),
@.FirstNamenvarchar(25),
@.LastNamenvarchar(50),
@.Teachernvarchar(25),
@.GradYrint

DECLARE@.UserIDuniqueidentifier
SELECT@.UserID =NULL
SELECT @.UserID = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserName
INSERT INTO[table name]
(UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
VALUES(@.UserID,@.UserName,@.Email,@.FirstName,@.LastName,@.Teacher,@.GradYr)

Also, add some error handling in the stored procedure after the insert. Something like as follows.

SET @.returnstatus = @.@.error

IF @.returnstatus <> 0
BEGIN
RETURN @.returnstatus
END

|||

From what you show, it seems like you are making a "custom" way to just write Profile data (email, First, last, Teacher, GradYr). If you enable the Profile provider and supply these fields, you can use the built-in provider to do this kind of stuff - - no need to write your own.

|||

pbromberg:

From what you show, it seems like you are making a "custom" way to just write Profile data (email, First, last, Teacher, GradYr). If you enable the Profile provider and supply these fields, you can use the built-in provider to do this kind of stuff - - no need to write your own.

OK, I give. I just read a bunch of articles on the subject of profile provider and none of them really helped me--seemed like they were talking about creating instead of enabling profiles. Where's some good, simple information on activating the built-in provider. The closest I've come is implementing _CreatingUser on the CreateNewUser wizard, and listing the field names from my new table in web.config. If you can educate we where to go from here in order to enable the profile provider, I'd appreciate it.

|||

I just read up on profiles in Walther'sUnleashed book, and came up with the stuff below for web.config. I get Intellisense in the code-behind, which is a good sign, but when I run the page, I can't get it right for "type = " and the book doesn't elaborate on it. Not having the correct entry for the type criteria or omitting it produces an error. If someone can help me on this, I think I'll be in good shape.

One other thing: Should I use a separate table (shown as tblAlumni below) or use one of the standard aspnet tables? I'd prefer the latter to keep things the most simple, but Walther shows an "outside" table. Thanks in advance for any help.

<profile defaultProvider="DNProfileProvider"> <properties> <add name="FirstName" /> <add name="LastName" /> <add name="GradYr" type="integer"/> <add name="Address1" /> <add name="Address2" /> <add name="City" /> <add name="State" /> <add name="Zip" /> <add name="SpouseName" /> <add name="Gender" /> <add name="MaidenName" /> <add name="Phone" /> </properties> <providers> <add name="DNProfileProvider" type="??" connectionStringName="sqlConnection" profileTableName="tblAlumni"/> </providers> </profile>

|||

It depends on how the Profile class is written. The default profile provider has its own table and it can hold any type and number of fields that you define for each profile. However, this data is "opaque" in the database - you cannot easily search on it. If you want to use a custom profile provide as it seems from the snippet you posted, then it may use it's own table. There are some samples for "Table Profile Provider" and "Stored Procedure Profile Provider" that you can use as a model if you want a custom table.

Here is an article with some examples:

http://www.eggheadcafe.com/articles/20060731.asp

|||

muybn:

Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.

@.UserNamenvarchar(128),
@.Emailnvarchar(50),
@.FirstNamenvarchar(25),
@.LastNamenvarchar(50),
@.Teachernvarchar(25),
@.GradYrint

DECLARE@.UserIDuniqueidentifier
SELECT@.UserID =NULL
SELECT @.UserID = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserName
INSERT INTO[table name]
(UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
VALUES(@.UserID,@.UserName,@.Email,@.FirstName,@.LastName,@.Teacher,@.GradYr)

A new problem has arisen, having to do with database, that I need to resolve first. I make an entry to my form, save it to the database; then the next entry I make throws the error that the same UID can't be written to the database. I believe that, Mr. DB, but since when am I writing the same UID to you? I don't know where to start tracing this, except to show the stored proc again (above), and to describe my process and some of my code that might generate it.

 
Protected Sub cuwCreateUserWizard1_CreatingUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cuwCreateUserWizard1.CreatedUser strEmail =CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Email"), TextBox).Text strUserName =CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text.ToLower strFirstName =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtFirstName"), TextBox).Text strLastName =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtLastName"), TextBox).Text lngGradYr =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtGradYr"), TextBox).Text strTeacher =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtTeacher"), TextBox).TextEnd Sub Protected Sub cuwCreateUserWizard1_CreatedUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cuwCreateUserWizard1.CreatedUserDim cmdAs New SqlCommand("sp_CreateUser", con) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@.UserName", strUserName) cmd.Parameters.AddWithValue("@.Email", strEmail) cmd.Parameters.AddWithValue("@.FirstName", strFirstName) cmd.Parameters.AddWithValue("@.LastName", strLastName) cmd.Parameters.AddWithValue("@.Teacher", strTeacher) cmd.Parameters.AddWithValue("@.GradYr", lngGradYr) Using con con.Open() cmd.ExecuteScalar() con.Close()End Using cmd.Equals(Nothing)End Sub
  

Please let me know any other info you'd need to help me determine what's wrong.

|||

Can you please start a new POST / Thread. By this way we can concentrate your new issue. Since a post is marked as answered, every one will think that your issue is resolved. Hope you understand.