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

Hello all experts,
I know that this is weird , but i am going to ask , so if someone knows a solution that would be greate.
I have got a form with lots of fields to insert into the db.
The code that i am using only updates the records for every user, which means that there should always be a record in the db
to add the data to, which i don't have.
So is there anyway that i can insert and update records in the db without using the insert statment.
Here is the form that i have , it only contains update statment
 
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

'Function to make SQL statements not fail w/ special character(s)
function fQueryFriendly(strVar)
     strTemp = strVar
     strTemp = Replace(strTemp,"'","''")
     fQueryFriendly = strTemp
end function

'Set current user
dim M_USERNAME
M_USERNAME = session("user")
       
if request.form("iSaveAll") = "1" then


       'Saving entire form
     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") & "'"
    'Response.Write strSQL
'Response.End
    conn.execute(strSQL)
End If


'read from db
strSQL ="SELECT * FROM instructions WHERE M_USERNAME = '" & Session("user") & "'"
Set oRS = conn.Execute(strSQL)
while not oRS.Eof
     'get the data
     Business_Name = oRS("Business_Name")
     todate = oRS("todate")
     toby = oRS("toby")
        
        oRS.MoveNext
wend

oRS.close
set oRS = nothing
%>
MelfekyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AgentSmith007Commented:
What do you want to insert?
0
MelfekyAuthor Commented:
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.
0
AgentSmith007Commented:
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
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

AgentSmith007Commented:
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
0
MelfekyAuthor Commented:
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
0
AgentSmith007Commented:
sry, there was a typo

set rsExists = conn.execute("SELECT M_USERNAME FROM instructions WHERE M_USERNAME = '" & session("user") & "'")
0
AgentSmith007Commented:
And no, unfortunately you cannot insert fields into the database without using the INSERT statement
0
AgentSmith007Commented:
I'm not sure what you meant by your proposed solution. Can you try to re-explain?
0
MelfekyAuthor Commented:
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")
0
AgentSmith007Commented:
That would make a lot of sense. Probably the easiest route at this point considering your explained structure.
0
MelfekyAuthor Commented:
So do u think that we can do this?
0
AgentSmith007Commented:
What does your register page look like (code)? also post the design of the tables you need the M_USERNAME inserted into
0
MelfekyAuthor Commented:
<%
''' 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"
%>
0
AgentSmith007Commented:
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)
0
MelfekyAuthor Commented:
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
0
AgentSmith007Commented:
Of course you'll need to enter the correct table name for Table1-4 in the above code
0
AgentSmith007Commented:
ok, so use this:

     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 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MelfekyAuthor Commented:
ignore my last post.
i will try ur code and let u know
0
MelfekyAuthor Commented:
Yes, It works.
You are the best.;)
This will save lots of time writting code, i guess.
0
AgentSmith007Commented:
Glad to help!
0
MelfekyAuthor Commented:
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
0
MelfekyAuthor Commented:
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"
%>
0
MelfekyAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.