3 pages of forms for 1 sql record

[heavenly_blue]

My site's subscription form requires much information from the user. Currently there are 3 pages that collect the necessary info.

What is the best way to have those three pages all edit the same record? Can I carry the variables from the 1st two pages on to the 3rd and submit them all at once? Or can I insert a record on the first page, and have the other two edit that record? How do I tell the next two pages which record to edit?

I'm really new at this, so I don't really know how to go about this.

[Alex C]

In your Table use an ID number as an AutoNumber and use that to specify which record you will be editing and updating. Your best approach would be to edit/update the record after each submit.

Use a query string or a hidden input box to carry the ID number from page to page so you can remember which record you were intending on updating. Then all you need to do is use UPDATE for SQL and it should be pretty straight forward.

If you get a start on it with all your 3 pages created and attempt to do some of the SQL Query's but get stuck just post your code here and we can help you complete it, good luck.


Regards,
Alex

[lmf232s]

I agree with the other post. Write to the database three times. The first time most likely you will be adding a record but the next two you will be updating the record.

Do like a
KeyID
as your primary key and or another unique field that you may have.
Employee num, SS num, but KeyID is pretty good. Its a field that has no meaning to your records or the user. Its just a field that you can use to index and search in your DB.

on the first page do an add
After you are done with the adding of the record do a response.redirect to the next page and send the value(what ever you key is going to be to access the records) in the query string.

Response.redirect "PageTwo.asp?ID=" & ID

Then on the second page you can get the ID value by

Dim ID

ID = request.querystring("ID")

Then hide it in a hidden txt box

<input type=hidden name=ID value=<%=ID%>

Then when you update on this page
ID = request.form("ID")
SQL = "SELECT * FROM MyTable WHERE (ID = '" & ID & "')"

When you are done updating do another

Response.redirect "PageThree.asp?ID=" & ID

And do the third the same as the second.

[heavenly_blue]

sounds good.

lmf232s - once I add the record, how do I define the ID variable as the primary key? I'm using Dreamweaver UltraDev 4 to build my pages, and it made the "insert record" function automatically:



<%
// *** Edit Operations: declare variables

MM_editAction = CStr(Request("URL"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If

// boolean to abort record edit
MM_abortEdit = false

// query string to execute
MM_editQuery = ""
%>

<%
// *** Insert Record: set variables

If (CStr(Request("MM_insert")) <> "") Then

MM_editConnection = MM_test_STRING
MM_editTable = "dbo.Contact"

//here's the response.redirect part. what i need to know is how to define ID as the primary key. i added the '?ID=" & ID' part. originally it just said "/beta/subscribe2.asp"

MM_editRedirectUrl = "/beta/subscribe2.asp?ID=" & ID
MM_fieldsStr = "FirstName|value|LastName|value|CompanyName|value|ContactAddress|value|ContactCity|value|ContactState |value|ContactZip|value|Email|value|Password|value"
MM_columnsStr = "FirstName|',none,''|LastName|',none,''|CompanyName|',none,''|ContactAddress|',none,''|ContactCity|', none,''|ContactState|',none,''|ContactZip|',none,''|Email|',none,''|Password|',none,''"

// create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

// set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next

// append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
%>

<%
// *** Insert Record: construct a sql insert statement and execute it

If (CStr(Request("MM_insert")) <> "") Then

// create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),",")
Delim = MM_typeArray(0)
If (Delim = "none") Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none") Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none") Then EmptyVal = ""
If (FormVal = "") Then
FormVal = EmptyVal
Else
If (AltVal <> "") Then
FormVal = AltVal
ElseIf (Delim = "'") Then // escape quotes
FormVal = "'" & Replace(FormVal,"'","''") & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

If (Not MM_abortEdit) Then
// execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>

[lmf232s]

What database are you using?

You want to define and set up your primary key within the you database table.

Make your first field in the database your primary key.
Something like
KeyID

set this as your primary key and in the details of it set it to increament by 1.

Every record you add will have a different KeyID which will increament
By 1 everytime you add a new record. then everyrecord has a unique ID that you can use to access it.

[heavenly_blue]

im using a SQL database. my coworker set it up for me. unfortunately i have very limited access to it. i know each table has a primary key. for this table, the primary key is called "CustomerID."

my question was: for the record that's currently being inserted, how do i put the value of the CustomerID field into a variable?

is that necessary for the " ?ID= " part of the response.redirect line?

as far as understand so far, the redirect part should look like:


// some how store the value of CustomerID into a variable called CustomerID here
MM_editRedirectUrl = "/beta/subscribe2.asp?ID=" & CustomerID


i might still be going about this all wrong, but this is where i'm at right now.

also, someone else suggested instead of creating a record on the 1st page and editing it again with the other two, maybe try this?:

store all the variables from the 1st two pages in a cookie. on the 3rd page get the variables from the cookie and insert the record using the last form and the info from the cookie.

i'm not really sure how to do it either way though. thanks for the help.

[heavenly_blue]

Ok well I got it to work somewhat on my own.

Right now, after completing the 1st form, the page sets a cookie with the customerID in it. Then on the 2nd and 3rd forms, the customer ID is taken from that cookie, placed in a hidden text field, and is used to determine which record to edit.

I couldn't figure out how to do it with the *.asp?ID= way. I think it has to do with dreamweaver's way of inserting the record. It looks like it puts it's own data at the end of the redirect URL in order to update the database.

Thanks for the help.