Melfeky
asked on
I want to insert a record of MEMBER_ID into 4 tables when the user registerfor the first time
Hello all experts
i want to insert a record of MEMBER_ID into 4 tables when the user registerfor the first time, though this field is an autonumber in the table that the registeration page inserts values to.
can this be done?
Here is the code of the registration page:
<%
''' check to see whether user clicked "register"
If len(request("register"))>0
''' check to see whether user clicked "register"
Dim connection
Call openConnection(connection)
' declare variables
Dim sTableName, sError
sError = ""
'[step 1] - validate that we've got a valid M_EMAIL address:
Dim sM_EMAIL
sM_EMAIL = request("M_EMAIL")
if len(trim(sM_EMAIL))=0 then
sError = "M_EMAIL Address is a required field.<BR>"
end if
'[step 2] - confirm that the password and the confirmed password match
Dim sPassword, sConfirmPassword
sPassword = request("M_PASSWORD")
sConfirmPassword = request("confirm_password"
if len(trim(sPassword))=0 then
sError = sError&"Password is a required field.<BR>"
end if
if len(trim(sConfirmPassword)
sError = sError&"Password Confirmation is a required field.<BR>"
end if
if (len(trim(sPassword))>0 AND len(trim(sConfirmPassword)
AND sPassword<>sConfirmPasswor
sError = sError&"The password and password confirmations do not match.<BR>"
end if
'[step 3] - make sure the userID doesn't already exist in the DB...
Dim sUserToCheck
' Grab the value of the user name that the user has entered,
' and replace any single quotes with double quotes
' so it doesn't mess up the SQL Statement.
sUserToCheck = replace(request("M_USERNAM
if len(trim(sUserToCheck))=0 then
sError = sError & "User Name is a required field.<BR>"
else
Dim rs, sUserNameFieldName
Dim lCount
set rs = server.CreateObject("adodb
'Change this variable to your table name
sTableName = "FORUM_MEMBERS"
'Change this variable to your "userName" field name in your user table
sUserNameFieldName = "M_USERNAME"
'Please note that you will have to change this rs.open line to use your active connection to your DB.
' that is something that this example doesn't take into account
rs.Open "SELECT COUNT(*) AS CountUsers FROM "&sTableName&" WHERE "&sUserNameFieldName&"='"&
lCount = rs.Fields("CountUsers")
rs.Close
set rs = nothing
if lCount>0 then
sError = sError & "The user name provided is taken. Please try again with a different user name.<BR>"
end if
end if
'[step 4] - If we've made it this far and we don't have anything in our error string,
' then no errors must have occurred! Go ahead and insert our values...
if sError = "" then
Dim rsInsert
sTableName = "FORUM_MEMBERS"
set rsInsert = server.CreateObject("ADODB
rsInsert.Open "SELECT * FROM "&sTableName&" WHERE 1=0", connection, 1, 3, 1
rsInsert.AddNew
'''' please note that you'll have to update this code to match all fields you want to insert
'''' also, note that any fields that you do not require your user to enter, you'll want to make sure
'''' you've got a value first before setting the recordset value equal to it (this can head off
'''' potential errors before they occur)
rsInsert("M_USERNAME") = request("M_USERNAME")
rsInsert("M_PASSWORD") = request("M_PASSWORD")
rsInsert("M_EMAIL") = request("M_EMAIL")
rsInsert("M_CITY") = request("M_CITY")
rsInsert("M_FIRSTNAME") = request("M_FIRSTNAME")
rsInsert("M_LASTNAME") = request("M_LASTNAME")
rsInsert("M_STATE") = request("M_STATE")
rsInsert("M_AGE") = request("M_AGE")
rsInsert("M_COUNTRY") = request("M_COUNTRY")
rsInsert("zipcode") = request("zipcode")
rsInsert("M_SEX") = request("M_SEX")
'''' In our validation, since we didn't require that the user make a valid address entry
'''' we should go ahead and make sure something was entered before we assign the value
rsInsert("ADDRESS") = request("ADDRESS")
''' commit the records to the db
rsInsert.Update
rsInsert.Close
set rsInsert = nothing
'' redirect the page to clear out the form values (this keeps users from hitting "refresh" and accidentally
'' adding multiple entries, and it also allows you to show a confirmation message)
Response.Redirect "login.asp?insertSuccess=1
else
'This means that errors have indeed occurred. Write them out to the browser:
Response.Write "<span style=""color:red;font-wei
" <HR><blockquote>"&sError&"
end if
'[step 5] - Check to see if my insertSuccess querystring argument has any value.
' if so, it means that a user has successfully been entered...
if len(Trim(request("insertSu
Response.Write "You have successfully inserted a record. Congratulations."
end if
Call closeConnection(connection
''' End check to see whether user clicked "register"
End if
''' End check to see whether user clicked "register"
%>
i want to insert a record of MEMBER_ID into 4 tables when the user registerfor the first time, though this field is an autonumber in the table that the registeration page inserts values to.
can this be done?
Here is the code of the registration page:
<%
''' check to see whether user clicked "register"
If len(request("register"))>0
''' check to see whether user clicked "register"
Dim connection
Call openConnection(connection)
' declare variables
Dim sTableName, sError
sError = ""
'[step 1] - validate that we've got a valid M_EMAIL address:
Dim sM_EMAIL
sM_EMAIL = request("M_EMAIL")
if len(trim(sM_EMAIL))=0 then
sError = "M_EMAIL Address is a required field.<BR>"
end if
'[step 2] - confirm that the password and the confirmed password match
Dim sPassword, sConfirmPassword
sPassword = request("M_PASSWORD")
sConfirmPassword = request("confirm_password"
if len(trim(sPassword))=0 then
sError = sError&"Password is a required field.<BR>"
end if
if len(trim(sConfirmPassword)
sError = sError&"Password Confirmation is a required field.<BR>"
end if
if (len(trim(sPassword))>0 AND len(trim(sConfirmPassword)
AND sPassword<>sConfirmPasswor
sError = sError&"The password and password confirmations do not match.<BR>"
end if
'[step 3] - make sure the userID doesn't already exist in the DB...
Dim sUserToCheck
' Grab the value of the user name that the user has entered,
' and replace any single quotes with double quotes
' so it doesn't mess up the SQL Statement.
sUserToCheck = replace(request("M_USERNAM
if len(trim(sUserToCheck))=0 then
sError = sError & "User Name is a required field.<BR>"
else
Dim rs, sUserNameFieldName
Dim lCount
set rs = server.CreateObject("adodb
'Change this variable to your table name
sTableName = "FORUM_MEMBERS"
'Change this variable to your "userName" field name in your user table
sUserNameFieldName = "M_USERNAME"
'Please note that you will have to change this rs.open line to use your active connection to your DB.
' that is something that this example doesn't take into account
rs.Open "SELECT COUNT(*) AS CountUsers FROM "&sTableName&" WHERE "&sUserNameFieldName&"='"&
lCount = rs.Fields("CountUsers")
rs.Close
set rs = nothing
if lCount>0 then
sError = sError & "The user name provided is taken. Please try again with a different user name.<BR>"
end if
end if
'[step 4] - If we've made it this far and we don't have anything in our error string,
' then no errors must have occurred! Go ahead and insert our values...
if sError = "" then
Dim rsInsert
sTableName = "FORUM_MEMBERS"
set rsInsert = server.CreateObject("ADODB
rsInsert.Open "SELECT * FROM "&sTableName&" WHERE 1=0", connection, 1, 3, 1
rsInsert.AddNew
'''' please note that you'll have to update this code to match all fields you want to insert
'''' also, note that any fields that you do not require your user to enter, you'll want to make sure
'''' you've got a value first before setting the recordset value equal to it (this can head off
'''' potential errors before they occur)
rsInsert("M_USERNAME") = request("M_USERNAME")
rsInsert("M_PASSWORD") = request("M_PASSWORD")
rsInsert("M_EMAIL") = request("M_EMAIL")
rsInsert("M_CITY") = request("M_CITY")
rsInsert("M_FIRSTNAME") = request("M_FIRSTNAME")
rsInsert("M_LASTNAME") = request("M_LASTNAME")
rsInsert("M_STATE") = request("M_STATE")
rsInsert("M_AGE") = request("M_AGE")
rsInsert("M_COUNTRY") = request("M_COUNTRY")
rsInsert("zipcode") = request("zipcode")
rsInsert("M_SEX") = request("M_SEX")
'''' In our validation, since we didn't require that the user make a valid address entry
'''' we should go ahead and make sure something was entered before we assign the value
rsInsert("ADDRESS") = request("ADDRESS")
''' commit the records to the db
rsInsert.Update
rsInsert.Close
set rsInsert = nothing
'' redirect the page to clear out the form values (this keeps users from hitting "refresh" and accidentally
'' adding multiple entries, and it also allows you to show a confirmation message)
Response.Redirect "login.asp?insertSuccess=1
else
'This means that errors have indeed occurred. Write them out to the browser:
Response.Write "<span style=""color:red;font-wei
" <HR><blockquote>"&sError&"
end if
'[step 5] - Check to see if my insertSuccess querystring argument has any value.
' if so, it means that a user has successfully been entered...
if len(Trim(request("insertSu
Response.Write "You have successfully inserted a record. Congratulations."
end if
Call closeConnection(connection
''' End check to see whether user clicked "register"
End if
''' End check to see whether user clicked "register"
%>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am a kind of newbie so if u could help me with code that would be greate.
Thanks
Thanks
to get the autonumber field value of the just inserted record,
use rs.open "select @@identity from <tablename>" just after inserting the record
and get the identity value into a variable
then using the variable value insert into other 3 tables
and for atomicity
conn.Open strCon
conn.begintrans
conn.execute("INSERT INTO check(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
' get the identity value by the above idea.
conn.execute("INSERT INTO tax(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
conn.execute("INSERT INTO instructions(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
conn.execute("INSERT INTO last(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
if conn.errors.count = 0 then
conn.committrans
else
conn.rollbacktrans
end if
conn.close
this will make sure that for some reason, if any one of the insert fails, totally fails or totally succeeds
Hope this helps
I can give the entire code, but it would make you lazy
Try with the above idea
it will work
Hope this helps
Cheers
Kaliyappan
use rs.open "select @@identity from <tablename>" just after inserting the record
and get the identity value into a variable
then using the variable value insert into other 3 tables
and for atomicity
conn.Open strCon
conn.begintrans
conn.execute("INSERT INTO check(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
' get the identity value by the above idea.
conn.execute("INSERT INTO tax(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
conn.execute("INSERT INTO instructions(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
conn.execute("INSERT INTO last(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
if conn.errors.count = 0 then
conn.committrans
else
conn.rollbacktrans
end if
conn.close
this will make sure that for some reason, if any one of the insert fails, totally fails or totally succeeds
Hope this helps
I can give the entire code, but it would make you lazy
Try with the above idea
it will work
Hope this helps
Cheers
Kaliyappan
ASKER
I am not sure that i got it,
i am not lazy , i am just trying to figure out how u guys can do it.
If u could provide me with the code that would be realy appreciated.
i am not lazy , i am just trying to figure out how u guys can do it.
If u could provide me with the code that would be realy appreciated.
ASKER
where can i get the member_id when it is inserted into the db from ur code?
rs.open "select @@identity from <tablename>", con, 1, 1
dim member_id
if not rs.eof then
member_id = rs(0)
end if
then use the above member_id for the other 3 tables
would give you the member_id of the just inserted record.
Hope this helps
Cheers
Kaliyappan
dim member_id
if not rs.eof then
member_id = rs(0)
end if
then use the above member_id for the other 3 tables
would give you the member_id of the just inserted record.
Hope this helps
Cheers
Kaliyappan
ASKER
Dim straccessdb, conn, strcon
strAccessDB = "db1.mdb"
Set Conn = Server.CreateObject("ADODB
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" & Server.MapPath(strAccessDB
conn.Open strCon
conn.execute("INSERT INTO check(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
conn.execute("INSERT INTO tax(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
conn.execute("INSERT INTO instructions(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
conn.execute("INSERT INTO last(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
conn.close
I want to do the same but with the MEMBER_ID which is an autonumber that is uniquly stored in the MEMBERS Table.
So all i want to do is when the user registers i take the value that is stored in the MEMBER_ID in the MEMBERS table and insert it in the other 4 tables.