how to use autonombers in ODBC
-
Hi there, I'm using Access 2000 (mdb file) and connect it to my app. through ODBC Driver. My problem is that in Access DB there is a field which is autonumber (which means that each new entry this fields gets : Last Entry value + 1). How can i use this functionality in MFC ODBC when i add a new Record, ie : m_pSet->AddNew (); and then ... ???? Thanks in advanced, Ariel.
-
Hi there, I'm using Access 2000 (mdb file) and connect it to my app. through ODBC Driver. My problem is that in Access DB there is a field which is autonumber (which means that each new entry this fields gets : Last Entry value + 1). How can i use this functionality in MFC ODBC when i add a new Record, ie : m_pSet->AddNew (); and then ... ???? Thanks in advanced, Ariel.
you just insert a new record and provide values and column names for each column in the table that requires one ... some are set to allow null while others are not (this is your choice as db designer) ... the autonumber field you don't even have to mention in the add statement as the db will do that for you ... you might need to read it back out after the add if you are updating multiple tables as part of the same action :suss: "every year we invent better idiot proof systems and every year they invent better idiots"
-
you just insert a new record and provide values and column names for each column in the table that requires one ... some are set to allow null while others are not (this is your choice as db designer) ... the autonumber field you don't even have to mention in the add statement as the db will do that for you ... you might need to read it back out after the add if you are updating multiple tables as part of the same action :suss: "every year we invent better idiot proof systems and every year they invent better idiots"
First, Thank you very much for taking the time and answer my question - i really apritiate it ! Well, i just found that this was my error (linked a control to such autonumber field). Second, I have another question if you can : I have a table linked to another table by 1 to many, i cant insert a new row to the table because of this link - how can i link the data from the other table to the current one (i managed to fill the combo with all the other table entries but its still not connected) - how do i do it. Thank you again, Ariel.
-
First, Thank you very much for taking the time and answer my question - i really apritiate it ! Well, i just found that this was my error (linked a control to such autonumber field). Second, I have another question if you can : I have a table linked to another table by 1 to many, i cant insert a new row to the table because of this link - how can i link the data from the other table to the current one (i managed to fill the combo with all the other table entries but its still not connected) - how do i do it. Thank you again, Ariel.
the table on the one side of the one-to-many must be written to first so that the foreign key field (the many) in the other table can relate to that value ... this is called referentail integrity and ensures that you don't get orphaned records in one table that don't have a partner in a related table for example, you have a customer record that has cust_id as a primary key and you have another table containing, say, physical address details that are linked to the customer table by the cust_id field ... in the address table cust_id is a foreign key ... the db won't allow you to insert an address record for a customer (cust_id) that doesn't exist in the customer table unless you turn off the checking of course ... which is a BAD THING generally :suss: "every year we invent better idiot proof systems and every year they invent better idiots"
-
the table on the one side of the one-to-many must be written to first so that the foreign key field (the many) in the other table can relate to that value ... this is called referentail integrity and ensures that you don't get orphaned records in one table that don't have a partner in a related table for example, you have a customer record that has cust_id as a primary key and you have another table containing, say, physical address details that are linked to the customer table by the cust_id field ... in the address table cust_id is a foreign key ... the db won't allow you to insert an address record for a customer (cust_id) that doesn't exist in the customer table unless you turn off the checking of course ... which is a BAD THING generally :suss: "every year we invent better idiot proof systems and every year they invent better idiots"
Hi there, First thank you for the answer. Actually i know about the referentail integrity needed in a 1-to-many connection. The thing is i don't know how to use it in actual user interface of MFC. Lets Say as you said i have : Table1.ID - connected in m_T1_ID linked to a CEdit Value : m_edtVal Table1.Table2ID - connected in m_T1_T2_ID linked to a CComboBox Value : m_cmbVal How can i put all Table2 Entries in m_cmbVal and put the Actual Table2.ID inside Table1.Table2ID ? m_pSet->AddNew(); // Somwhere in here i should reference the 1-to-many, but how ??? Thank you for your answer, Ariel.