Problem calling Fill() method

Discussion in 'ASP .Net Datagrid Control' started by Mervin Williams, Apr 23, 2004.

  1. 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
     
    Mervin Williams, Apr 23, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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, Apr 23, 2004
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > 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
    > >
    > >

    >
    >
     
    Mervin Williams, Apr 23, 2004
    #3
  4. 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.
    "Mervin Williams" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > > news:...
    > > > 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, Apr 23, 2004
    #4
  5. 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

    "Mervin Williams" <> wrote in message
    news:...
    > 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
    >
    >
     
    Harish Palaniappan, Apr 26, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mervin Williams

    Problem calling Fill() method

    Mervin Williams, Apr 23, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    1,454
    Harish Palaniappan
    Apr 26, 2004
  2. =?Utf-8?B?UGF1bA==?=

    trying to use fill method with SQL select statement

    =?Utf-8?B?UGF1bA==?=, Apr 28, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    460
    =?Utf-8?B?UGF1bA==?=
    Apr 29, 2004
  3. Pawel Blochowiak

    method Fill - problem

    Pawel Blochowiak, Mar 15, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    386
    Patrick Olurotimi Ige
    Mar 16, 2005
  4. Nathan Sokalski
    Replies:
    0
    Views:
    870
    Nathan Sokalski
    Jun 3, 2007
  5. why-em-jay

    Calling a method of the calling object ...

    why-em-jay, Sep 15, 2005, in forum: Perl Misc
    Replies:
    3
    Views:
    188
    Anno Siegel
    Sep 15, 2005
Loading...

Share This Page