Best way to track our users in database while filling out web form.

[milliKidd]

Hello Experts,
Here's a tricky one...well, for me anyway. (-: Okay, I'll just be straight up with it. I have several ASP.Net pages that the client is going to be filling out. First one is like demograhics.aspx where they put in just regular information, you know, like, FirstName, LastName, SSN stuff like that. Now, with each page, I'm dumping to a SQL table via stored procedures. So, for the aforementioned page I have a table named Data_Demographic and a stored procedure named sp_DataDemographic_Insert or something like that. And, for the rest of my pages I have a table for each page and stored procedures for each page. Also, later I'm going to write stored procs to Update and Retrieve as well. Anyway, here's my dilema. I'm trying to figure out the BEST way to keep track of someone that will be filling out this web form from table to table. Here's something that we are doing to keep track of someone so it will be unique to that person. On the demographics page (my first page they come to), I am creating what we call a token and storing it in that first table. Data_Demographics.
Here's the code for that:

Private Function CreateUserID() 'or could be called CreateToken

        Dim str As New StringBuilder(Left(tbLastName.Text, 3))
        Dim currentDate As Date = Now
        Dim [ReturnValue] As String

        str.Append(currentDate.ToString("MMMddyy"))
        str.Append(Right(mstbSSN.Text, 4))
        str.Append(currentDate.ToString("hhmmssff"))

        [ReturnValue] = str.ToString.ToUpper
        Return [ReturnValue]

End Function

So, to keep it unique to only that user, we are grabbing the first 3 letters of their last name, appending the date, appending the last 4 numbers of their Social Security Number, and then the hours, minutes, seconds and, what is that, milliseconds or something? Now, I have a Sub routine that goes like this:

Protected Sub insertData()

    tkn = CreateUserID()

    Dim fName As String = (TryCast(FindControl("tbFirstName"), FilteredTextBox)).Text
    Dim midName As String = (TryCast(FindControl("tbMiddleName"), FilteredTextBox)).Text
    and so on...
   
    With sqlCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "sp_DataDemographic_Insert"
            .Connection = sqlCnx

            .Parameters.AddWithValue("@FirstName", fName)
            .Parameters.AddWithValue("@Middle", midName)
            .Parameters.AddWithValue("@LastName", lName)
            .Parameters.AddWithValue("@MaidenName", maidenName)
            .Parameters.AddWithValue("@SSN", ssn)
            .Parameters.AddWithValue("@DateOfBirth", birthDate)
            .Parameters.AddWithValue("@Suffix", suffix)
            .Parameters.AddWithValue("@NPNNumber", npn)
            .Parameters.AddWithValue("@Gender", gender)
            .Parameters.AddWithValue("@USCitizen", usCitizen)
            .Parameters.AddWithValue("@Token", tkn)
    End With

    If sqlCnx.State = ConnectionState.Open Then
        sqlCnx.Close()
    End If

    Try
        sqlCnx.Open()
        sqlCmd.ExecuteNonQuery()
    Catch ex As Exception
        'do stuff here to send error to developers.
    Finally
        sqlCmd.Dispose()
        sqlCnx.Close()
    End Try

End Sub

Now, in my first table (Data_Demographic) I have a column named Token which is nvarchar (22). So, here's where it gets tricky for me. I'm going to throw this out there and see what you guys think. I have a couple of ideas but I don't know if it's possible.
1. The user clicks a button at the bottom of the form named "Next Page", it runs the stored procedure above and dumps all that data into the table for that page. Now, for the next page that is named employment.aspx inside it's stored procedure (sp_DataEmpAddr_Insert) I also have a field named Token. Is it possible within the Insert to do something like this:

CREATE PROCEDURE [dbo].[sp_DataEmpAddr_Insert]
(
    ...other fields...
    @Token nvarchar (22)
)
AS
INSERT INTO Data_EmploymentAddr
(
    Token --do stuff here to connect back to Data_Demographics table and retrieve Token for this user.
)
VALUES
(
    @Token
)
GO

Or
2. Should I maybe put a textbox on each form and set it to Visible="False" and have Token dumped to it on Page_Load or something? Then on every page postback have it dump to Token in each table? Sorry about the dumb questions, I am not a SQL guy. (-:

Thanks in advance...



milliKidd

Hope this all clear enough.

Update:
Ok, I got something to work and here's what I did. I changed my stored procedure to this:

CREATE PROCEDURE [dbo].[sp_DataDemographic_Insert]
(
      @FirstName nvarchar (50),
      @LastName nvarchar (50),
      @Middle nvarchar (50),
      @Suffix nvarchar (8),
      @MaidenName nvarchar (50),
      @DateOfBirth smalldatetime ,
      @SSN nvarchar (9),
      @CountryOfCitizenship nvarchar (50),
      @Gender nvarchar (6),
      @FinancialInstitution nvarchar (1),
      @USCitizen nvarchar (1),
      @NPNNumber nvarchar (12),
      @Token nvarchar (22)
)
AS
INSERT INTO Data_Demographics
(
      FirstName,
      LastName,
      Middle,
      Suffix,
      MaidenName,
      DateOfBirth,
      SSN,
      CountryOfCitizenship,
      Gender,
      FinancialInstitution,
      USCitizen,
      NPNNumber,
      Token
)
VALUES
(
      @FirstName,
      @LastName,
      @Middle,
      @Suffix,
      @MaidenName,
      @DateOfBirth,
      @SSN,
      @CountryOfCitizenship,
      @Gender,
      @FinancialInstitution,
      @USCitizen,
      @NPNNumber,
      @Token
)
INSERT INTO Data_EmploymentAddr
(
      Token
)
VALUES
(
      @Token
)
GO

And this worked GREAT, but now when I open the table Data_EmploymentAddr Token is in there like it should be but all those other fields have <NULL> in them. And I knew it would be because that row has been written to and there was no data to fill all those other fields. So now my question is, is there a way now, when the client moves to the next page (employment.aspx) and they click the "Next Page" button at the bottom of the form to move off that page, have the data append to the same row as Token is in?
So, here's an example:
Let's say the user comes to the demographics page and starts filling it out.
Name: John Doe SSN:123457890 and he's filling it out today. So his token would be something like this:
DOEOCT1408789012595987 Now, it's dumped into 2 tables at once. Demographic and Employment. So when he moves to the employment.aspx page and enters all of his info on that page and clicks the button at the bottom, can I append all of his information into that SAME row that Token is in?

 

[jebenson]

Take a look at this MSDN article:

ASP.NET Session State Overview

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[milliKidd]

Okay thanks jebenson,
good article.