Update Multiple Tables

[charon]

hi,

I Just want to know how could I update data into multiple tables with one SQL statement???
Below is my example with error:

I have two tables:
Name ID, Name
JOb ID, Job, Department

The Name.ID must = Job.ID
DataPath = Server.MapPath("mydata.mdb")
Set objDC = DbConnectionOpen(DataPath)
sSQL = "SELECT Name.ID, Name.Name, Job.ID, Job.Name, Job.Dprt FROM Name, Job"

Set objRS = RsOpen(sSQL, objDC)
objRS.AddNew
objRS("ID") = "122"
objRS("Name") = "alvin"
objRS("Job") = "Pro"
objRS("Dprt") = "Mis"
objRS.Update
Response.Write ("Record Updated")

'End If
objRS.Close
objDC.Close
Set objRS = Nothing
Set objDC = Nothing

[kalijunfan]

Hi Charon,

1. you´re not updating, you are inserting a record in the database, it´s not the same.
2. you forgot to relate the tables in the sql query
3. you have the same field names in both tables so you have to use alias for each one of them or it wont work.
4. if any of the ID fields is an AutoNumber you don´t have to insert anything on that field, it does it automatically.

You should use something like this:


sSQL = "SELECT Name.ID AS NameID, Name.Name, Job.ID AS JobID, Job.Job, Job.Dprt FROM Name, Job WHERE Name.ID = Job.ID"

'and then use it like this:

Set objRS = RsOpen(sSQL, objDC)
objRS.AddNew
objRS("NameID") = "122"
objRS("Name") = "alvin"
objRS("JobID") = "122"
objRS("Job") = "Pro"
objRS("Dprt") = "Mis"
objRS.Update
Response.Write ("Record Updated")


That should work, give it a try and see what happens. I hope it helps.

Regards

[charon]

hi, kalijunfan,


Thanks so much for your help, it really work great.:))

more questions to ask:

1.) Would it works in INSERT/UPDATE/DELETE SQL statement??for instance: INSERT table(ID, Name) value("122", alvin").This is only one table, but what about Multiple table as mentioned aboved???

2.) Can you help me on my another post: Rollback transaction

[kalijunfan]

Hi Charon,

sorry i didn´t answered you sooner, had too much work to do and no time to check the forums.

1) this will not work with INSERT or UPDATE, in those cases you should do it one table at a time, but i guess it will with a DELETE, i never tried it but it doesn´t have any reason why not to work, the only thing you have to do is pass it a parameter to use to identify the record that it should delete. I would do it like this:


sSQL = "DELETE * FROM Name, Job WHERE Name.ID = Job.ID AND Name.ID = " & Request.querystring("id")


Again, i never actually did this but it should work. Why don´t you give it a try and see what happens (backup your database first, remember that once you delete a record you can´t get it back).

2) i don´t have much experience with transactions in asp, all the transactions that i did where in Visual Basic 6 and they are diferent from the way are done in asp, but i´ll check your post and see what can i do. :)

Regards