• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

DataSet Population Problem

I have three tables in an Access 2000 Database:

Owners
=====
ID (AutoNumber)
File_Num
Forenames
Surname
Title_ID (Number)
ID_Num
Physical_Street
Physical_Suburb
Physical_City
Postal_Street
Postal_Suburb
Postal_City
Postal_Code
Phone_Home
Phone_Work
Mobile
Fax
EMail
Next_Of_Kin_Name
Next_Of_Kin_Phone
Comments
Picture_Path

Tenants
=====
ID (AutoNumber)
File_Num
Forenames
Surname
Title_ID (Number)
ID_Num
Physical_Street
Physical_Suburb
Physical_City
Postal_Street
Postal_Suburb
Postal_City
Postal_Code
Phone_Home
Phone_Work
Mobile
Fax
EMail
Cooccupant_Name
Cooccupant_Phone
Handed_Over (Yes/No)
Comments
Picture_Path

Properties
======

File_Num
Unit_Street_Num
Complex
Street
Suburb
City

File_Num is the linking field between the 3 tables.Except for "ID", "Title_ID" and "Handed_Over", all fields are Text. As you can see, the Tenants and Owners tables are nearly identical - there are only 3 fields differing between the two.

Now I have two DataAdapters in VB.NET 2003 - one for the Tenants table and one for the Owners table - which fills two tables in a DataSet. The Tenants CoomandText of the DataAdapter's SelectCommand looks as follows:

SELECT
    Tenants.File_Num AS File,
    Tenants.Forenames + ' ' + Tenants.Surname AS Name,
    Tenants.Post_Statement_To_Postal AS Postal,
    Tenants.Post_Statement_To_Physical AS Physical,
    Tenants.EMail_Statement AS EMail,
    Tenants.Fax_Statement AS Fax,
    Tenants.EMail AS [E-Mail Address],
    Tenants.Fax AS [Fax No],
    Tenants.Mobile,
    Tenants.Phone_Home AS [Home Phone],
    Tenants.Phone_Work AS [Work Phone],
    Tenants.Postal_Street + ' ' + Tenants.Postal_Suburb + ' ' + Tenants.Postal_City + ' ' + Tenants.Postal_Code AS [Postal Address],
    Tenants.Physical_Street + ' ' + Tenants.Physical_Suburb + ' ' + Tenants.Physical_City AS [Physical Address],
    Properties.Unit_Street_Num + ' ' + Properties.Complex + ' ' + Properties.Street + ' ' + Properties.Suburb + ' ' + Properties.City AS Property
FROM
    (Tenants INNER
JOIN
    Properties ON
        Tenants.File_Num = Properties.File_Num) ORDER BY Tenants.File_Num


And the CommandText of the Owners DataAdapter's SelectCommand looks the same but with different table names:

SELECT
    Owners.File_Num AS File,
    Owners.Forenames + ' ' + Owners.Surname AS Name,
    Owners.Post_Statement_To_Postal AS Postal,
    Owners.Post_Statement_To_Physical AS Physical,
    Owners.EMail_Statement AS EMail,
    Owners.Fax_Statement AS Fax,
    Owners.EMail AS [E-Mail Address],
    Owners.Fax AS [Fax No],
    Owners.Mobile,
    Owners.Phone_Home AS [Home Phone],
    Owners.Phone_Work AS [Work Phone],
    Owners.Postal_Street + ' ' + Owners.Postal_Suburb + ' ' + Owners.Postal_City + ' ' + Owners.Postal_Code AS [Postal Address],
    Owners.Physical_Street + ' ' + Owners.Physical_Suburb + ' ' + Owners.Physical_City AS [Physical Address],
    Properties.Unit_Street_Num + ' ' + Properties.Complex + ' ' + Properties.Street + ' ' + Properties.Suburb + ' ' + Properties.City AS Property
FROM
    (Owners INNER
JOIN
    Properties ON
        Owners.File_Num = Properties.File_Num) ORDER BY Owners.File_Num

There is no InsertCommand, UpdateCommand or DeleteCommand for either of these 2 DataAdapters. Now for some reason, when I fill the tables in the DataSet with these DataAdapters, the Tenants DataAdapter fills the Tenants table in the DataSet without a problem, but the Owners DataAdapter gives this error when filling the Owners table in the DataSet:

"No value given for one or more required parameters"

This happens exactly at the moment the Fill command of the DataAdapter is executed. Here is the code:

daTenants.Fill(DsCorrespondence1, "Tenants") 'Executes perfectly
daOwners.Fill(DsCorrespondence1, "Owners") 'Bombs out

I checked the Parameters collection of the SelectCommand of both DataAdapters and both are empty. I don't know what could be causing the problem...
0
SETP
Asked:
SETP
  • 2
1 Solution
 
Bob LearnedCommented:
I don't see the two in the Owners table (or anywhere for that matter):

Owners.Post_Statement_To_Postal AS Postal
Owners.Post_Statement_To_Physical AS Physical

Bob
0
 
iboutchkineCommented:
Properties might be reserved word. Eithr use [Properties] or change the table name. Also check other fields if they use reserved words
0
 
SETPAuthor Commented:
Damn! Can't believe I missed that! Thanks!
0
 
SETPAuthor Commented:
It's still weird though how the Data Adapter Configuration Wizard let me generate that SQL query in the first place!!!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now