[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-28
23
Medium Priority
?
229 Views
Last Modified: 2006-11-17
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
%>
0
Comment
Question by:Melfeky
  • 12
  • 11
23 Comments
 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691013
What do you want to insert?
0
 

Author Comment

by:Melfeky
ID: 12691027
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
 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691081
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691084
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
 

Author Comment

by:Melfeky
ID: 12691158
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
 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691167
sry, there was a typo

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

Expert Comment

by:AgentSmith007
ID: 12691187
And no, unfortunately you cannot insert fields into the database without using the INSERT statement
0
 
LVL 9

Expert Comment

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

Author Comment

by:Melfeky
ID: 12691216
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
 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691224
That would make a lot of sense. Probably the easiest route at this point considering your explained structure.
0
 

Author Comment

by:Melfeky
ID: 12691248
So do u think that we can do this?
0
 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691289
What does your register page look like (code)? also post the design of the tables you need the M_USERNAME inserted into
0
 

Author Comment

by:Melfeky
ID: 12691418
<%
''' 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
 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691456
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
 

Author Comment

by:Melfeky
ID: 12691457
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
 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691458
Of course you'll need to enter the correct table name for Table1-4 in the above code
0
 
LVL 9

Accepted Solution

by:
AgentSmith007 earned 2000 total points
ID: 12691470
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
 

Author Comment

by:Melfeky
ID: 12691471
ignore my last post.
i will try ur code and let u know
0
 

Author Comment

by:Melfeky
ID: 12691548
Yes, It works.
You are the best.;)
This will save lots of time writting code, i guess.
0
 
LVL 9

Expert Comment

by:AgentSmith007
ID: 12691556
Glad to help!
0
 

Author Comment

by:Melfeky
ID: 12744745
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
 

Author Comment

by:Melfeky
ID: 12744752
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
 

Author Comment

by:Melfeky
ID: 12744767
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month19 days, 2 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question