identity fields and addnew with joined tables

[cyberbiker]

I am using VB6 ADO and SQL Server 7

Most of my tables are set up pretty much the same with
with columns like ID,name , address, etc.

ID is an intendity field

Then I have tables where the rows are like ID,notes or similar.

I am creating recordsets with select statements similar to
Select tblA *, tblB.notes from tblA left outer join tblB
on tblB.id = tblA.id

When I use the recordset.addnew method my update fails. It appears to me that the identity field is not actually created in SQL Server when adding new records until the update occurs since I am trying to insert a null value into tblB's ID field thus causing update to fail.

As I expected if I attempt to requery the recordset, the requery failsI Additionally a RS.Move 0 fails

Thes recordsets may often be empty except for the new record that I am adding

I am using client side cursors and bound controls

Any ideas will be appreciated.


Terry (cyberbiker)

 

[SemperFiDownUnda]

You can not create a new record on a SQL statement that is joined. You have to create the parent record first then they child. Even if these 2 tables are not physically joined via DRI a SQL statement of SELECT ... FROM ... JOIN ... will fail

You are diving into the limitations of databinding etc.
You could try creating a view of the query but with SQL 7 I think you will have the same problem as Views while updatable only allow you to update data from one table at a time.

Databinding is great for displaying data but I personally never use it for updating.

 

[cyberbiker]

Thank you for the explanation. I was reaching the same conclusion.

I am uncertain what you are refering to when you use the term 'DRI'. I have not yet found that in any of the books I have here

I need to work out a way to fix all this in my code.

I have a lot of code that will need reworked and need to get thinking on how to solve this with the least changes since time is running out.

Terry (cyberbiker)

 

[vb5prgrmr]

Easiest way to solve would be to use two insert/update statements (addnew/edit). You would add the parent record first then the child.

Good Luck

 

[cyberbiker]

Thanks
I thought of that also. I have made various changes depending on exactly what I am doing at the time.
Most of my joins where for a "note" field that the large majority of records will have while others will not
Some of these now fill the form from seperate recordsets "the old fashioned way" Others (where it is almost 100% certain to have data in the notes field) I have merged the tables.
This is the last time I use bound controls.
I have spent much more time trying to make the binding work than I would have just filling the values from the recordset.

Terry (cyberbiker)