UpdateBatch on left join 
Author Message
 UpdateBatch on left join

I am trying to use the UpdateBatch method for a disconnected recordset, sql
sample:

select a.PKA, b.PKB, b.Col1
from a
left join b on b.PKB = a.PKA

I edit the update the recordset on a row that has no record in table b (null
values) but makes sure that the primary key is set on the table:

rs("PKB") = rs("PKA")
rs("Col1) = "abcde"

The UpdateBatch method reports an error "Row cannot be located for
updating...". I assume this is because the recordset don't understand that
it should use "insert", ie it uses "update" and get 0 updated records.

Is there a way to make the recordset object to insert these records?

/Tommy



Mon, 01 Dec 2003 00:05:48 GMT  
 UpdateBatch on left join
Tommy,

I created a sample project with 3 tables and a VB testing project to
demonstrate how to insert a record against a LEFT join. I tested it at my
side and it worked great. I believe this is what we've been looking for.
Please follow the steps below.

1. Create 3 sample tables. They are: Family, Individual and Attendance. I
have attached the SQL script to generate the 3 tables with necessary PK and
FK constraints. However, I'd like to explain more about them.

Family:                 it has a PK: FamilyID and another unimportant column: FamilyName;
Individual:     It has a PK: IndividualID and a FK: FamilyID, that is
referenced in the Family table. Also, it contains LastName and FirstName
column.
Attendance:     It contains a PK: IndividualID, that is used to generated the
LEFT JOIN afterwards and an unimportant column: data.

2. Populate some data in those tables and I attached the snapshots of the
data I used in them.
3. Use this SQL SELECT statement to do the LEFT JOIN.

SELECT [Individual].IndividualID as IID,
        [Individual].FamilyID,
        [Individual].FirstName,  
        [Individual].LastName,
        [Attendance].IndividualID as AID,
        [Attendance].data
FROM [Individual]
LEFT JOIN [Attendance]
ON  [Individual].IndividualID = [Attendance].IndividualID

4. Create a brand new Standard EXE project in VB and place an instance of
ADODC and a DataGrid control onto the form.
5. Set the ADODC to correctly point to the SQL server database and the
SELECT statement to generate the recordset.
6. Bind DataGrid control with ADODC by specifying its DataSource property
to ADODC.
7. Before running the project, please make sure that the
DataGrid.AllowAddNew is True.
8. Run the project and you'll see the Grid is populated with the data
retrieved with that LEFT JOIN.
9. Try to insert a new record.

This sample works perfectly at my side. Please note that if the Column in
one of the LEFT JOIN'd tables requires NOT NULL, PK or FK, please make sure
it is included in the SELECT and you put correct data into the Grid. In
short, you have to SELECT those columns and let the Grid to display them
because they cannot be ignored when inserting or updating a record.

Furthermore, I'd like to explain more about this solution.
As we know, a table in SQL server may have PK, FK and columns that allows
NULL and disallows NULL. When the columns are NOT allowing NULL, you will
have to give them a correct value when inserting and updating a record.

When you do a LEFT OUTER JOIN against the tables, you may not have included
those columns that are NOT allowing NULL. This will result in an error when
you try to insert a new record since some such columns will have no value
to store in the table.

When you do an INSERT, ADODC and the DataGrid will generate, for this
particular case, 2 INSERT SQL statements to be executed on the SQL server.
Obviously, if you don't give it *all* the necessary info for both tables,
the INSERT will fail.

TABLE.SQL
====================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Individual_Family]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Individual] DROP CONSTRAINT FK_Individual_Family
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Attendance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Attendance]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Family]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Family]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Individual]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Individual]
GO

CREATE TABLE [dbo].[Attendance] (
        [IndividualID] [int] NOT NULL ,
        [data] [char] (10) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Family] (
        [FamilyID] [int] NOT NULL ,
        [FamilyName] [char] (10) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Individual] (
        [IndividualID] [int] NOT NULL ,
        [FamilyID] [int] NOT NULL ,
        [FirstName] [char] (10) NOT NULL ,
        [LastName] [char] (10) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Attendance] WITH NOCHECK ADD
        CONSTRAINT [PK_Attendance] PRIMARY KEY  CLUSTERED
        (
                [IndividualID]
        )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Family] WITH NOCHECK ADD
        CONSTRAINT [PK_Family] PRIMARY KEY  CLUSTERED
        (
                [FamilyID]
        )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Individual] WITH NOCHECK ADD
        CONSTRAINT [PK_Individual] PRIMARY KEY  CLUSTERED
        (
                [IndividualID]
        )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Individual] ADD
        CONSTRAINT [FK_Individual_Family] FOREIGN KEY
        (
                [FamilyID]
        ) REFERENCES [dbo].[Family] (
                [FamilyID]
        )
GO

HTH,

Peter



Tue, 02 Dec 2003 15:54:54 GMT  
 UpdateBatch on left join
Thanks, but I still have the same problem. I didn't try your sample with the
ADODC control, i created the recordset in code.

Adding a complete record or adding just the the individual is fine. The
error occur when have an existing individual that is to become an attendee,
this is what the recordset executes in sql:

exec sp_executesql N'UPDATE "Test".."Attendance" SET


I put the VB code bellow.

/Tommy

Private mrs As Recordset
Private mstrConnection As String

Private Sub Form_Load()
    mstrConnection = "DSN=test"
    LoadRS
End Sub

Private Sub LoadRS()
    Dim strSQL As String

    strSQL = "SELECT [Individual].IndividualID as IID, " & _
             "[Individual].FamilyID, " & _
             "[Individual].FirstName, " & _
             "[Individual].LastName, " & _
             "[Attendance].IndividualID as AID, " & _
             "[Attendance].Data " & _
             "From [Individual] " & _
             "LEFT JOIN [Attendance] " & _
             "ON  [Individual].IndividualID = [Attendance].IndividualID"

    Set mrs = New Recordset

    With mrs
        .ActiveConnection = mstrConnection
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        .Source = strSQL
        .Open
        Set .ActiveConnection = Nothing
    End With

    Set DataGrid1.DataSource = mrs
End Sub

Private Sub cmdSave_Click()
    mrs.ActiveConnection = mstrConnection
    mrs.UpdateBatch

    Set mrs = Nothing

    LoadRS
End Sub


Quote:
> Tommy,

> I created a sample project with 3 tables and a VB testing project to
> demonstrate how to insert a record against a LEFT join. I tested it at my
> side and it worked great. I believe this is what we've been looking for.
> Please follow the steps below.

> 1. Create 3 sample tables. They are: Family, Individual and Attendance. I
> have attached the SQL script to generate the 3 tables with necessary PK
and
> FK constraints. However, I'd like to explain more about them.

> Family: it has a PK: FamilyID and another unimportant column: FamilyName;
> Individual: It has a PK: IndividualID and a FK: FamilyID, that is
> referenced in the Family table. Also, it contains LastName and FirstName
> column.
> Attendance: It contains a PK: IndividualID, that is used to generated the
> LEFT JOIN afterwards and an unimportant column: data.

> 2. Populate some data in those tables and I attached the snapshots of the
> data I used in them.
> 3. Use this SQL SELECT statement to do the LEFT JOIN.

> SELECT [Individual].IndividualID as IID,
> [Individual].FamilyID,
> [Individual].FirstName,
> [Individual].LastName,
> [Attendance].IndividualID as AID,
> [Attendance].data
> FROM [Individual]
> LEFT JOIN [Attendance]
> ON  [Individual].IndividualID = [Attendance].IndividualID

> 4. Create a brand new Standard EXE project in VB and place an instance of
> ADODC and a DataGrid control onto the form.
> 5. Set the ADODC to correctly point to the SQL server database and the
> SELECT statement to generate the recordset.
> 6. Bind DataGrid control with ADODC by specifying its DataSource property
> to ADODC.
> 7. Before running the project, please make sure that the
> DataGrid.AllowAddNew is True.
> 8. Run the project and you'll see the Grid is populated with the data
> retrieved with that LEFT JOIN.
> 9. Try to insert a new record.

> This sample works perfectly at my side. Please note that if the Column in
> one of the LEFT JOIN'd tables requires NOT NULL, PK or FK, please make
sure
> it is included in the SELECT and you put correct data into the Grid. In
> short, you have to SELECT those columns and let the Grid to display them
> because they cannot be ignored when inserting or updating a record.

> Furthermore, I'd like to explain more about this solution.
> As we know, a table in SQL server may have PK, FK and columns that allows
> NULL and disallows NULL. When the columns are NOT allowing NULL, you will
> have to give them a correct value when inserting and updating a record.

> When you do a LEFT OUTER JOIN against the tables, you may not have
included
> those columns that are NOT allowing NULL. This will result in an error
when
> you try to insert a new record since some such columns will have no value
> to store in the table.

> When you do an INSERT, ADODC and the DataGrid will generate, for this
> particular case, 2 INSERT SQL statements to be executed on the SQL server.
> Obviously, if you don't give it *all* the necessary info for both tables,
> the INSERT will fail.

> TABLE.SQL
> ====================
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Individual_Family]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Individual] DROP CONSTRAINT FK_Individual_Family
> GO

> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Attendance]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Attendance]
> GO

> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Family]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Family]
> GO

> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Individual]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Individual]
> GO

> CREATE TABLE [dbo].[Attendance] (
> [IndividualID] [int] NOT NULL ,
> [data] [char] (10) NOT NULL
> ) ON [PRIMARY]
> GO

> CREATE TABLE [dbo].[Family] (
> [FamilyID] [int] NOT NULL ,
> [FamilyName] [char] (10) NOT NULL
> ) ON [PRIMARY]
> GO

> CREATE TABLE [dbo].[Individual] (
> [IndividualID] [int] NOT NULL ,
> [FamilyID] [int] NOT NULL ,
> [FirstName] [char] (10) NOT NULL ,
> [LastName] [char] (10) NOT NULL
> ) ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[Attendance] WITH NOCHECK ADD
> CONSTRAINT [PK_Attendance] PRIMARY KEY  CLUSTERED
> (
> [IndividualID]
> )  ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[Family] WITH NOCHECK ADD
> CONSTRAINT [PK_Family] PRIMARY KEY  CLUSTERED
> (
> [FamilyID]
> )  ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[Individual] WITH NOCHECK ADD
> CONSTRAINT [PK_Individual] PRIMARY KEY  CLUSTERED
> (
> [IndividualID]
> )  ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[Individual] ADD
> CONSTRAINT [FK_Individual_Family] FOREIGN KEY
> (
> [FamilyID]
> ) REFERENCES [dbo].[Family] (
> [FamilyID]
> )
> GO

> HTH,

> Peter



Tue, 02 Dec 2003 20:13:22 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. PRB: Left join behaves like a inner join.

2. UpdateBatch, Inserts, on joined tables

3. Can't UpdateBatch with JOINed recordsets - Error

4. UpdateBatch on Joined Table Query?

5. Left join Query error

6. Query Left Join Causes Access to crash only in Windows 2000

7. Left joins from code?

8. Setting Left Join

9. LEFT JOIN does not work in query

10. Deletion from LEFT JOINed tables - very basic question

11. updatable? Jet Recordset with LEFT JOIN

12. How to Left join more than two table ?

 

 
Powered by phpBB® Forum Software