form to database

Results 1 to 2 of 2

Thread: form to database

  1. #1
    Join Date
    Dec 1969

    Default form to database

    Hi I am using the following code to enter data from an asp form into an access database. The problem is that the 2 tables I<BR> refer to (orders and companies) are connected by company ID which is an autonumber. The join is 1 to many with referential<BR> Integrity enforced. With that enforced will not allow me to send the information. I think I may need to add a join in it before I<BR> send the information to the database. Any ideas how to do this? The company ID in Company is an autonumber, the one in<BR> Orders is just a number. Maybe I should pass the Company ID as a hidden field?<BR> Thanks if you can help<BR><BR> &#060;!--#include file=""--&#062; <BR> &#060;%<BR> dim rsUsers<BR> set rsUsers = Server.CreateObject("ADODB.Recordset")<BR> rsUsers.Open "Orders", db, adopenstatic, adlockoptimistic<BR> rsUsers.AddNew<BR> rsUsers("Office") = Request.Form("office")<BR> rsUsers("Order Date") = Request.Form("purchased")<BR> rsUsers("Payment Date") = Request.Form("paid")<BR> rsUsers("FormatID") = Request.Form("format")<BR> rsUsers("Title") = Request.Form("title")<BR> rsUsers("Value") = Request.Form("amount")<BR> rsUsers("Comments") = Request.Form("comments")<BR> rsUsers.Update<BR> rsUsers.close<BR> set rsUsers=nothing %&#062;<BR><BR> &#060;%<BR> set rsUsers = Server.CreateObject("ADODB.Recordset")<BR> rsUsers.Open "Companies", db, adopenstatic, adlockoptimistic<BR> rsUsers.AddNew<BR> rsUsers("Company Name") = Request.Form("company")<BR> rsUsers("Contact First Name") = Request.Form("contract")<BR> rsUsers("Billing Address") = Request.Form("address")<BR> rsUsers("City") = Request.Form("city")<BR> rsUsers("State/Province") = Request.Form("state")<BR> rsUsers("Postal Code") = Request.Form("postal")<BR> rsUsers("Country") = Request.Form("country")<BR> rsUsers("Email") = Request.Form("email")<BR> rsUsers("Phone Number") = Request.Form("telephone")<BR> rsUsers.Update <BR> rsUsers.close<BR> set rsUsers=nothing %&#062;

  2. #2
    Join Date
    Dec 1969

    Default RE: form to database

    I think the code below should fix the problem - if I understood the problem correctly in the first place.<BR>if you are doing this on one page (it makes sense to) you only need to create the recordset objec once. You can safely open and close it to get new information. When you are done with it you set it to nothing at the end of the page.<BR>Another good practice is to use the trim() functino to remove extraneous garbage from your values. Believe me, using it will save MANY headaches.<BR>Also, I used the company name to requery the db for the record that we just entered. One potential problem with this is if the company name has an quotes or sigle quotes in it. The SQL statment will blow up. Try to query fields that do not have these characters in them. This is one reason it is good to have autonumber fields on table. They are easy to search for.<BR>Good luck,<BR>todd.<BR>&#060;%<BR>set rsUsers = Server.CreateObject("ADODB.Recordset")<BR>rsUsers. Open "Companies", db, adopenstatic, adlockoptimistic<BR>rsUsers.AddNew<BR>rsUsers("Com pany Name") = Request.Form("company")<BR>rsUsers("Contact First Name") = Request.Form("contract")<BR>rsUsers("Billing Address") = Request.Form("address")<BR>rsUsers("City") = Request.Form("city")<BR>rsUsers("State/Province") = Request.Form("state")<BR>rsUsers("Postal Code") = Request.Form("postal")<BR>rsUsers("Country") = Request.Form("country")<BR>rsUsers("Email") = Request.Form("email")<BR>rsUsers("Phone Number") = Request.Form("telephone")<BR>rsUsers.Update <BR>rsUsers.close<BR><BR>sSQL="SELECT * FROM COMPANIES WHERE COMPANIES.CompanyName=&#039" & trim(request("company")) & "&#039"<BR>rsUsers.Open sSQL, db<BR>id=rsUsers("CompanyID") &#039get id for record we just added<BR>rsUsers.close<BR><BR>rsUsers.Open "Orders", db, adopenstatic, adlockoptimistic<BR>rsUsers.AddNew<BR>rsUsers("Com apnyID")=id &#039set the id field in this table<BR>rsUsers("Office") = Request.Form("office")<BR>rsUsers("Order Date") = Request.Form("purchased")<BR>rsUsers("Payment Date") = Request.Form("paid")<BR>rsUsers("FormatID") = Request.Form("format")<BR>rsUsers("Title") = Request.Form("title")<BR>rsUsers("Value") = Request.Form("amount")<BR>rsUsers("Comments") = Request.Form("comments")<BR>rsUsers.Update<BR>rsUs ers.close<BR>set rsUsers=nothing %&#062;<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts