locked
Modifying tableadapter update query RRS feed

  • Question

  • I need to modify the update query in my tableadapter by eliminating a reference to one of the columns in the where clause. Below is the query that needs modified. I need GenClassDesc to be updated but I don't want to filter by this in my where clause. I tried eliminating the filter in the query builder but then I get errors about missing parameters, something to do with the @IsNull_GenClassDesc parameter. Not sure what this even does.

    Anyways the reason I need to do this is because I'm getting concurrency errors when calling the update function on my tableadapter, and I determined it was because the GenClassDesc value being passed to the parameter is different from what is in the table, but I need it to be updated to the new value. I don't need to filter by this attribute so I would just like the query to ignore this. How can I modify the update command to ignore the GenClassDesc in the where clause?

    UPDATE    tblSubAsblyDet
    SET              SubAsseRecID = @SubAsseRecID, ItemNumber = @ItemNumber, ItemDescription = @ItemDescription, VendorItem = @VendorItem,
                          VendorName = @VendorName, BeginVIN = @BeginVIN, EndVIN = @EndVIN, PCN = @PCN, ClassID = @ClassID, UOM = @UOM, Cost = @Cost,
                          ItemType = @ItemType, GenClassDesc = @GenClassDesc
    WHERE     (RecID = @Original_RecID) AND (SubAsseRecID = @Original_SubAsseRecID) AND (ItemNumber = @Original_ItemNumber) AND
                          (ItemDescription = @Original_ItemDescription) AND (VendorItem = @Original_VendorItem) AND (VendorName = @Original_VendorName) AND
                          (BeginVIN = @Original_BeginVIN) AND (EndVIN = @Original_EndVIN) AND (PCN = @Original_PCN) AND (ClassID = @Original_ClassID) AND
                          (UOM = @Original_UOM) AND (Cost = @Original_Cost) AND (ItemType = @Original_ItemType) AND (@IsNull_GenClassDesc = 1) AND
                          (GenClassDesc IS NULL) OR
                          (RecID = @Original_RecID) AND (SubAsseRecID = @Original_SubAsseRecID) AND (ItemNumber = @Original_ItemNumber) AND
                          (ItemDescription = @Original_ItemDescription) AND (VendorItem = @Original_VendorItem) AND (VendorName = @Original_VendorName) AND
                          (BeginVIN = @Original_BeginVIN) AND (EndVIN = @Original_EndVIN) AND (PCN = @Original_PCN) AND (ClassID = @Original_ClassID) AND
                          (UOM = @Original_UOM) AND (Cost = @Original_Cost) AND (ItemType = @Original_ItemType) AND (GenClassDesc = @Original_GenClassDesc)
    Thursday, August 21, 2008 6:47 PM

Answers

  • Hi Clint,

    This code is serious spaghetti.  You need to study this in the query builder and judge what is needed and what is not.  Where clauses that include description of items, are seriously off base.


    What you basically need is a layout:    ReceiptTable->ReceiptDetail->ItemTable->VendorTable->vendorItemTable

    In the receipt table you can spawn off a receipt detail that has all your items with a vendor code, your item code, and the vendors code, You can pull description from your item table or the vendors item table.

    In your reciept detail you can capture quantiy and cost variables, so that they won't change if your fee structure changes in the future.

    That's the basics, but you got a much bigger problems than than GenClassDescription.

    Best of Luck to you,

    John.
    Visit my web site at: http://RadiolistenersElectricEasel.com/ If a post is helpful please give it a vote of helpful
    • Marked as answer by Xingwei Hu Wednesday, August 27, 2008 5:01 AM
    Thursday, August 21, 2008 11:44 PM

All replies

  • Well I tried something that seems to work but I'm not sure if this is the correct way to do this. I right clicked on the column in the query builder for the update and selected remove filter. Then I got an error that said something like parameterized query expects parameter @IsNull_GenClassDesc which was not supplied. So then I went back to the query builder and deleted the @IsNull_GenClassDesc field from the query builder and now I don't get the error and I've checked the data and everything appears to be correct in DB.
    Thursday, August 21, 2008 8:16 PM
  • Hi Clint,

    This code is serious spaghetti.  You need to study this in the query builder and judge what is needed and what is not.  Where clauses that include description of items, are seriously off base.


    What you basically need is a layout:    ReceiptTable->ReceiptDetail->ItemTable->VendorTable->vendorItemTable

    In the receipt table you can spawn off a receipt detail that has all your items with a vendor code, your item code, and the vendors code, You can pull description from your item table or the vendors item table.

    In your reciept detail you can capture quantiy and cost variables, so that they won't change if your fee structure changes in the future.

    That's the basics, but you got a much bigger problems than than GenClassDescription.

    Best of Luck to you,

    John.
    Visit my web site at: http://RadiolistenersElectricEasel.com/ If a post is helpful please give it a vote of helpful
    • Marked as answer by Xingwei Hu Wednesday, August 27, 2008 5:01 AM
    Thursday, August 21, 2008 11:44 PM