Solved

adding data through a form to different tables in the database using ASP

Posted on 2004-03-29
4
321 Views
Last Modified: 2010-04-17
I want to add some data through a form to  3 different tables in a database, but i seem to be getting some errors when running the asp program. i can only add to a single table . can u tell me where im going wrong?
The 3 tables are Items, ItemSupply and Suppliers.Code is below:

Item_name = Request.Form("Item_name")
Supplier_name = Request.Form("Supplier_name")
Quantity = Request.Form("Quantity")
Received_date = Request.Form("Received_date")
Order_number = Request.Form("Order_number")
Order_date = Request.Form("Order_date")
Buying_Price = Request.Form("Buying_Price")
Selling_Price = Request.Form("Selling_Price")

Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Mode = adModeReadWrite
MyConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & dbpath

Set RS=Server.CreateObject("ADODB.RecordSet")

RS.Open "Select Items.Item_name,Suppliers.Supplier_name,ItemSupply.Quantity,ItemSupply.Received_date,ItemSupply.Order_number,ItemSupply.Order_date, "&_
        "ItemSupply.Buying_Price,ItemSupply.Selling_Price "
            "From Items,ItemSupply,Suppliers", MyConn, adOpenDynamic, adLockPessimistic, adCMDText
             
RS.AddNew

  RS("Item_name")= Item_name
  RS("Supplier_name")= Supplier_name
  RS("Quantity")= Quantity
  RS("Received_date") = Received_date
  RS("Order_number")= Order_number
  RS("Order_date")=Order_date
  RS("Buying_Price")=Buying_Price
  RS("Selling_Price")=Selling_Price
 
RS.Update

RS.Close
MyConn.Close                                  
0
Comment
Question by:msberth
4 Comments
 
LVL 7

Expert Comment

by:ramesh12
ID: 10710109
msberth

           First of all you the query you wrote doesnt make any sense. Type the same query at the database (Query Analyser or Queries in Access whatever you are using) and find out if you get the results that you intended.

Second, I think we cannot update tables if you try to pull the data from different tables based on joins.
0
 

Expert Comment

by:slross
ID: 10713018
msberth,

Try creating three separate recordsets which update the database tables independently.  (using SQL Server? - Have you tried putting the processes in an SP or 3?).

Good luck!
0
 

Accepted Solution

by:
imranlakhani earned 20 total points
ID: 10716856
HI
either u r not updating each n every primary key field of ur tables
or in ur code, replace below code with

 RS("Item_name")= Item_name
  RS("Supplier_name")= Supplier_name
  RS("Quantity")= Quantity
  RS("Received_date") = Received_date
  RS("Order_number")= Order_number
  RS("Order_date")=Order_date
  RS("Buying_Price")=Buying_Price
  RS("Selling_Price")=Selling_Price

after replacing it will

  RS("Items.Item_name")= Item_name
  RS("Suppliers.Supplier_name")= Supplier_name
  RS("ItemSupply.Quantity")= Quantity
  RS("ItemSupply.Received_date") = Received_date
  RS("ItemSupply.Order_number")= Order_number
  RS("ItemSupply.Order_date")=Order_date
  RS("ItemSupply.Buying_Price")=Buying_Price
  RS("ItemSupply.Selling_Price")=Selling_Price

or if Begin.trans/ commit.trans is used in ASP, then use begin.trans property before addnew command, and commit.trans before/after update.

good luck
0
 
LVL 1

Expert Comment

by:ajnabitou
ID: 10718476
Using 3 recorsets would certainly help.
Now when you said you were able to add to only one table, did you mean to say that the code as you put it is running with errors and also adding a record to one table but not the other.
Or did you mean that the code is running if  you put something like
RS.Open "Select Items.Item_name From Items", MyConn, adOpenDynamic, adLockPessimistic, adCMDText ......
and not running when you include the other tables.
And one more thing, can I know why you have 3 seperate tables with apparantely no connection between them?
More information like table structure would certainly help us solve your problem.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now