 |
babyg
Level: Scholar
Registered: 15-10-2003
Posts: 32
|
Adding Data in a table with relationships using ADO
Hi guys,
how do i add data in a related table, i have tblSubject and a tblStudent which have a relationship with subjectID.
tnx.
____________________________
help... im a fish.
|
|
05-01-2005 at 02:27 PM |
|
|
Goran
Level: Moderator
Registered: 16-05-2002
Posts: 1681
|
Re: Adding Data in a table with relationships using ADO
conn.Execute "INSERT INTO tblSubject {fld1,fld2,fld3) VALUES (.... , ..... , .......)"
' retrieve id of newly inserted record
set rdID=conn.ececute("SELECT @@Identity FROM tblSubject" ' JET 4.o provider
set rdID=conn.ececute("SELECT max(id) FROM tblSubject" ' previous JET versions
now use rsID(0) in next SQL query
conn.execute "INSERT INTO tblStudent (...) VALUES (....)"
____________________________
If you find the answer helpful, please mark this topic as solved.
|
|
06-01-2005 at 01:41 AM |
|
|
babyg
Level: Scholar
Registered: 15-10-2003
Posts: 32
|
Re: Adding Data in a table with relationships using ADO
oh my.... im not sure i understand.. but, i'll try to figure it out anyway. tnx, mr. Goran.
____________________________
help... im a fish.
|
|
06-01-2005 at 02:01 AM |
|
|
babyg
Level: Scholar
Registered: 15-10-2003
Posts: 32
|
Re: Adding Data in a table with relationships using ADO
ok.. i admit.. i lack the knowlege in ADO... im lost.. please, could you explain this clearly.i need the code.
thank you.
[Edited by babyg on 06-01-2005 at 09:58 AM GMT]
____________________________
help... im a fish.
|
|
06-01-2005 at 03:27 AM |
|
|
Goran
Level: Moderator
Registered: 16-05-2002
Posts: 1681
|
Re: Adding Data in a table with relationships using ADO
Do you have MSDN installed on your computer)? It is a must to have it, it will make your life much esaier. If you do, you select some word(s) with mouse and then press F1, and it will display help for some command. This way you can find more about INSERT INTO statement.
conn - connection object
Execute - method to execute SQL queries (like SELECT, UPDATE, INSERT INTO, DELETE)
{fld1,fld2,fld3) - name of the fields
VALUES (.... , ..... , .......) ' values to be inserted in new record
number of fields and number of values must be the same (in this case it is 3)
conn.Execute "INSERT INTO tblSubject {fld1,fld2,fld3) VALUES ('sometext', somenumber , #somedate#),loRecordsaffected,adexecutenorecords
this command will insert one record in table Subject.
fld1= 'some text
fld2=somenumber
fld3=#somedate#
Here I made an example for different data types of fields.
fld1 - text type (value must be between ' ' )
fld2 - numeric type
fld3 - date type (value must be betwen # # )
You didnt specify if you want to add new record to table Subject and then want to use its autonumber field to store it in table student (in it's SibjectID field). If yes, then you need to retrieve its autobumber with either @@Identity or MAX(id). First one works only on Jet.OleDB.4.0 provider (acces2K and newer), and the second one works on all versions. Then, after you retrieve this number, you can store it in SubjectID of Student table.
If you dont want to add record in subject table, but only want to retrieve ID of a record that already exists, the tehnique can be different, but usualy Subject data are displayed somewhere on the form. Like for instance if you displayed Subject name in ComboBox, you can store its ID in ItemData property of combobox. This way you can easily access it.
____________________________
If you find the answer helpful, please mark this topic as solved.
|
|
06-01-2005 at 12:24 PM |
|
|
Goran
Level: Moderator
Registered: 16-05-2002
Posts: 1681
|
Re: Adding Data in a table with relationships using ADO
Yes ##Identity is a reserved word. And yes, (2) will return SubjectID. It will be places in rs(0).Value. so you need to insert this retrieved value in tblStudent
(3) CN.Execute "INSERT INTO tblstudent (frname,frLname,subjectID) values ('" _
& strfrstname & "','" _
& strlastname & "'," _
& rsid(0).value & ")"
____________________________
If you find the answer helpful, please mark this topic as solved.
|
|
07-01-2005 at 11:59 PM |
|
|
babyg
Level: Scholar
Registered: 15-10-2003
Posts: 32
|
Re: Adding Data in a table with relationships using ADO
hmmmm... sorry.., i have another problem again..
your solution works fine mr. Goran, however. how can i go about adding additional students per subject , i.e. subject =English has 5 students = stud1,stud2,stud3,stud4,stud5. your code adds new data for both tables.
i used
Set RSadd = CN.Execute("SELECT @@Identity FROM tblSubject where subjectID = '" & txtaUser & "'"
CN.Execute "INSERT INTO tblstudent (fld1,fld2,fld3) values (...,...,...) |
the error went back to my first problem,
| You cannot add or change a record because a related record is required in table 'tblsubject' |
help again needed.
____________________________
help... im a fish.
|
|
10-01-2005 at 10:02 AM |
|
|
Goran
Level: Moderator
Registered: 16-05-2002
Posts: 1681
|
Re: Adding Data in a table with relationships using ADO
babyg, this error message tells you that you cannot add record to student table untill you provide a value for subjectid, which is a related record. So, if you do set subject id to some value when inserting record then you wont receive this message.
@identity is used AFTER insert into statement, and is used for retreiving ID for a record that you JUST inserted. You cannot use it in select statement.
So, as I said
quote:
If you dont want to add record in subject table, but only want to retrieve ID of a record that already exists, the tehnique can be different, but usualy Subject data are displayed somewhere on the form. Like for instance if you displayed Subject name in ComboBox, you can store its ID in ItemData property of combobox. This way you can easily access it.
For instance, the code to populate combobox
dim rs as adodb.recordset
set rs=new adodb.recordset
rs.open "SELECT name,id FROM tblSubject",conn,adopenstatic,adopenforwardonly
do while not rs.eof
combo1.additem rs(0) ' subject name
combo1.itemdata(combo1.listcount-1)=rs(1) ' subject id
rs.movenext
loop |
Then after user picks some subject name from combobox, you can retrieve this subject id from ItemData property to use it in insert into statement to add record to student table
| subjectid=combo1.itemdata(combo1.listindex) |
____________________________
If you find the answer helpful, please mark this topic as solved.
|
|
10-01-2005 at 11:11 PM |
|
|
|
|
 |
|
