My code only update records in the db, is there any way to also insert into the db.

What do you want to insert?

well i don't know exactly, i want the record to be inserted even if there is no record for that user before in the db.

oh ok, do this:

if request.form("iSaveAll") = "1" then
      iExists = 0
      set rsExists = conn.execute("SELECT M_USERNAME FORM instructions WHERE M_USERNAME = '" & session("user") & "'"
      while not rsExists.EOF
            iExists = 1
      rsExists.movenext
      wend
      rsExists.close
      set rsExists = nothing
      if iExists = 0 then
            strSQL = "INSERT INTO instructions(Business_Name,todate,toby) VALUES('" & fQueryFriendly(request.form("Business_Name")) & "',"
            strSQL = strSQL & "'" & fQueryFriendly(request.form("todate")) & "',"
            strSQL = strSQL & "'" & fQueryFriendly(request.form("toby")) & "')"
      else
            strSQL = "UPDATE instructions SET "
            strSQL = strSQL & "Business_Name = '" & fQueryFriendly(request.form("Business_Name")) & "'"
            strSQL = strSQL & ",todate = '" & fQueryFriendly(request.form("todate")) & "'"
            strSQL = strSQL & ",toby = '" & fQueryFriendly(request.form("toby")) & "'"
            strSQL = strSQL & " WHERE M_USERNAME = '" & session("user") & "'"
      end if
    conn.execute(strSQL)
End If

sry, syntax error:

if request.form("iSaveAll") = "1" then
      iExists = 0
      set rsExists = conn.execute("SELECT M_USERNAME FORM instructions WHERE M_USERNAME = '" & session("user") & "'")
      while not rsExists.EOF
            iExists = 1
      rsExists.movenext
      wend
      rsExists.close
      set rsExists = nothing
      if iExists = 0 then
            strSQL = "INSERT INTO instructions(Business_Name,todate,toby) VALUES('" & fQueryFriendly(request.form("Business_Name")) & "',"
            strSQL = strSQL & "'" & fQueryFriendly(request.form("todate")) & "',"
            strSQL = strSQL & "'" & fQueryFriendly(request.form("toby")) & "')"
      else
            strSQL = "UPDATE instructions SET "
            strSQL = strSQL & "Business_Name = '" & fQueryFriendly(request.form("Business_Name")) & "'"
            strSQL = strSQL & ",todate = '" & fQueryFriendly(request.form("todate")) & "'"
            strSQL = strSQL & ",toby = '" & fQueryFriendly(request.form("toby")) & "'"
            strSQL = strSQL & " WHERE M_USERNAME = '" & session("user") & "'"
      end if
    conn.execute(strSQL)
End If

When i used this code , everytime i run this form a new record is inserted into the db,an it dosn't update the old record , a new record is saved instead.
The fields don't display the values in the db as it were before.
Is there anyway that i can do this without using the INSERT statment, as i have got lots of fields here like 100 for every form that i have  and it would be a lot of work to do this in the insert and the update statment.
There is something here that came into my mind:
I guess if there is a way, we could do something like this:
We can insert in all the tables( that my forms saves their values to  )when the user register a record for him with his username, i guess by doing this we could have already a record for every user and just can use the update statment like the example in my question.
I hope that u got my idea

sry, there was a typo

set rsExists = conn.execute("SELECT M_USERNAME FROM instructions WHERE M_USERNAME = '" & session("user") & "'")

And no, unfortunately you cannot insert fields into the database without using the INSERT statement

I'm not sure what you meant by your proposed solution. Can you try to re-explain?

I have got 4 tables , for each table there is a form that saves data into.
WHen i use my first posted code,it saves the data only if their is a value in the M_USERNAME, so there is no need to insert all the values of the fields in that form.
So my suggestion is:
When the user login for the first time or even after he register, a record is placed for him in all  this 4 tables with a value in the M_USERNAME field, and leave all the other fields empty as it is.Then when the user opens any form like the one that i have above ,
i can just use the update statment which will just add the values in the form in the record that was created before (after the user registered) WHERE M_USERNAME = '" & session("user")

That would make a lot of sense. Probably the easiest route at this point considering your explained structure.

So do u think that we can do this?

What does your register page look like (code)? also post the design of the tables you need the M_USERNAME inserted into

<%
''' check to see whether user clicked "register"
If len(request("register"))>0 then
''' 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))=0 then
    sError = sError&"Password Confirmation is a required field.<BR>"
  end if
  if (len(trim(sPassword))>0 AND len(trim(sConfirmPassword))>0) _
      AND sPassword<>sConfirmPassword then
    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_USERNAME"), "'", "''")
  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.recordset")
    '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&"='"&sUserToCheck&"'", connection, 1, 3, 1
    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.Recordset")
    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-weight: bold"">Errors have occurred:" & _
                   " <HR><blockquote>"&sError&"</blockquote><span><BR>Please check your entries and try again."
  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("insertSuccess")))>0 then
    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"
%>

Try placing this code:

      Dim straccessdb, conn, strcon
       strAccessDB = "db1.mdb"
       Set Conn = Server.CreateObject("ADODB.Connection")
       strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" & Server.MapPath(strAccessDB)
       conn.Open strCon
      conn.execute("INSERT INTO Table1(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
      conn.execute("INSERT INTO Table2(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
      conn.execute("INSERT INTO Table3(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
      conn.execute("INSERT INTO Table4(M_USERNAME) VALUES('" & request("M_USERNAME") & "')")
      conn.close

Below this:


    ''' 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)

as for the table names:
check
tax
instructions
last

i cann't write u all the fields in those tables, but the thing u should know is M_USERNAME is in all the tables and it is text datatype.

let me know if u need something else.
thanks

Of course you'll need to enter the correct table name for Table1-4 in the above code

ignore my last post.
i will try ur code and let u know

Yes, It works.
You are the best.;)
This will save lots of time writting code, i guess.

Glad to help!

Hello agentsmith,
i have to change the field that needs to be inserted in those 4 tables to MEMBER_ID which is an autonumber.
So using the code above while changing the M_USERNAME to MEMBER_ID is not working.
Though i have added this field in all the 4 tables.
Can this be done using the MEMBER_ID?
Its so important to do this for my application to work in the right way.
If u want me to add this in a new topic ket me know.
Thanks

I want this to be added in the same form of registration  here is the code again:
<%
''' check to see whether user clicked "register"
If len(request("register"))>0 then
''' 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))=0 then
    sError = sError&"Password Confirmation is a required field.<BR>"
  end if
  if (len(trim(sPassword))>0 AND len(trim(sConfirmPassword))>0) _
      AND sPassword<>sConfirmPassword then
    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_USERNAME"), "'", "''")
  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.recordset")
    '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&"='"&sUserToCheck&"'", connection, 1, 3, 1
    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.Recordset")
    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-weight: bold"">Errors have occurred:" & _
                   " <HR><blockquote>"&sError&"</blockquote><span><BR>Please check your entries and try again."
  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("insertSuccess")))>0 then
    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"
%>

Here is the link for the new question
https://www.experts-exchange.com/questions/21230334/I-want-to-insert-a-record-of-MEMBER-ID-into-4-tables-when-the-user-registerfor-the-first-time.html