msberth
asked on
adding data through a form to different tables in the database using ASP
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_nam e")
Quantity = Request.Form("Quantity")
Received_date = Request.Form("Received_dat e")
Order_number = Request.Form("Order_number ")
Order_date = Request.Form("Order_date")
Buying_Price = Request.Form("Buying_Price ")
Selling_Price = Request.Form("Selling_Pric e")
Set MyConn = Server.CreateObject("ADODB .Connectio n")
MyConn.Mode = adModeReadWrite
MyConn.Open "PROVIDER=Microsoft.Jet.OL EDB.4.0;DA TA SOURCE=" & dbpath
Set RS=Server.CreateObject("AD ODB.Record Set")
RS.Open "Select Items.Item_name,Suppliers. Supplier_n ame,ItemSu pply.Quant ity,ItemSu pply.Recei ved_date,I temSupply. Order_numb er,ItemSup ply.Order_ date, "&_
"ItemSupply.Buying_Price,I temSupply. Selling_Pr ice "
"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_dat e
RS("Buying_Price")=Buying_ Price
RS("Selling_Price")=Sellin g_Price
RS.Update
RS.Close
MyConn.Close
The 3 tables are Items, ItemSupply and Suppliers.Code is below:
Item_name = Request.Form("Item_name")
Supplier_name = Request.Form("Supplier_nam
Quantity = Request.Form("Quantity")
Received_date = Request.Form("Received_dat
Order_number = Request.Form("Order_number
Order_date = Request.Form("Order_date")
Buying_Price = Request.Form("Buying_Price
Selling_Price = Request.Form("Selling_Pric
Set MyConn = Server.CreateObject("ADODB
MyConn.Mode = adModeReadWrite
MyConn.Open "PROVIDER=Microsoft.Jet.OL
Set RS=Server.CreateObject("AD
RS.Open "Select Items.Item_name,Suppliers.
"ItemSupply.Buying_Price,I
"From Items,ItemSupply,Suppliers
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_dat
RS("Buying_Price")=Buying_
RS("Selling_Price")=Sellin
RS.Update
RS.Close
MyConn.Close
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.