Problem calling Fill() method

[Mervin Williams]

I have several tables involved in my application, but the two in question
here are the company and address tables. The company table has
business_address_id and mailing_address_id columns, which are both foreign
keys to the address table.

So, the stored procedure to which my SelectCommand points to reads as:

ALTER PROCEDURE dbo.CompanyInfoByCompanyID
(
@companyid int
)
AS
SET NOCOUNT ON
SELECT * FROM Company WHERE company_id = @companyid
SELECT * FROM Address
WHERE (address_id IN (Select business_address_id From
Company Where company_id = @companyid))
OR (address_id IN (Select mailing_address_id From Company
Where company_id = @companyid))
RETURN

Whenever I attempt to add a new row into the Address table using my
daAddress dataset, the update works fine and even enters a new row inside
the Address table. However, when I attempt to refresh my datagrid by
calling the Fill() method on the Address adapter I get the following error:

Exception Details: System.Data.SqlClient.SqlException: UPDATE statement
conflicted with COLUMN FOREIGN KEY constraint 'Address_Company_FK1'. The
conflict occurred in database 'OFS', table 'Address', column 'address_id'.
I placed the Find() call in a try/catch block, and upon closer inspection
discovered this error message:

DataSet errors: dsCompanyInfo

Table: Address

Row Error: Column 'zip' does not allow DBNull.Value.

Now, the zip column is indeed populated upon calling the Update() method,
and it is populated in the database. So I have no idea where this error
message is coming from.

Your assistance would be greatly appreciated,

Mervin Williams

 

[William Ryan eMVP]

Mervin:

The part about the Update statement failing when you call Fill, I'd think
that it would be calling your select statement instead of update statement.
What does the update statement look like, and is this the select command
fill is pointing to.

 

[Mervin Williams]

It is the Select that is failing when I call the Fill() method, and is
causing the errors that I wrote in my earlier message below. That's what is
so puzzling.

The update statement is a straightforward update to the Address table
(actually generated by VS.NET). Here it is:

UPDATE Address
SET address_line1 = @address_line1, address_line2 = @address_line2, city =
@city, state = @state, zip = @zip
WHERE (address_id = @Original_address_id) AND (address_line1 =
@Original_address_line1) AND (address_line2 = @Original_address_line2 OR
@Original_address_line2 IS NULL AND address_line2 IS NULL) AND (city =
@Original_city) AND (state = @Original_state) AND (zip = @Original_zip);
SELECT address_id, address_line1,
address_line2, city, state, zip
FROM Address
WHERE (address_id = @address_id)

Mervin Williams

 

[William Ryan eMVP]

I know that it was fill, but you could use a String for your commandtext.
That string could say "Update Tbl_Whatever" just as easily as "Select From
Tbl_Whatever" I was wondering if the Select Command is pointing to the
right statement and if possibly it's being set to something else. Since you
normally just fire a Select statement with Fill, it seems like something is
pointing incorrectly.

 

[Harish Palaniappan]

the exception states that the update command is executing.. ..
and if the exception occurred when .fill() executed, then u shud check code
logic, whether ur update statement executing on a call to fill() is correct.

anyway.. post some .net code (parts which relate to this err), so that its
easier to understand the problem.

'Harish