Results 1 to 1 of 1

Thread: [RESOLVED] TableAdapter query does not work when called with VB code...??? Need help!

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2010
    Posts
    110

    Resolved [RESOLVED] TableAdapter query does not work when called with VB code...??? Need help!

    Hi all!

    I have a connection between SQL Server 2008 R2 and Visual Studio 2008. The databese connection works perfectly and all seems to be in order...
    I have an UPDATE query in my DataSet TableAdapter. The UPDATE works perfectly when I execute it in the "Query Builder" "Execute Query". I just add the needed value @ProjNo and the whole action runs great.

    But when I add the query in the VB code of the table, it does nothing...?


    The bigger mystery is, that when I change the query in the TableAdapter (remove the part: " AND (LisätyötunnitDuunarit.LisätyöNro = @ProjNo)" ) and run it again in VB code it ACTUALLY does something.
    --> So, I know the connection between my VB code query and the TableAdapter is working correctly.
    The big question in my mind is WHY does the query stop working from the VB-code standpoint after I add: " AND (LisätyötunnitDuunarit.LisätyöNro = @ProjNo) " , but is still working when I execute the query in the "Execute Query" of the "Query Builder" in the DataSet TableAdapter????????????




    Below is some code. First the one where I haven't removed the part of code, and then the one where the part of code has been removed and the query executes some updates...



    1 : This code is written in the TableAdapter as my update query (UPDATE_Lisätyö). The last line of the code is the one I removed in part 2.
    Code:
    UPDATE    HenkTunnit
    SET              LisätyöNro = @ProjNo
    FROM         LisätyötunnitDuunarit INNER JOIN
                          HenkTunnit ON LisätyötunnitDuunarit.[Pvm.] = HenkTunnit.[Pvm.] AND LisätyötunnitDuunarit.ProjHenkID = HenkTunnit.ProjHenkID
    WHERE     (HenkTunnit.LisätyöNro2 = 0) AND (HenkTunnit.LisätyöNro3 = 0) AND (HenkTunnit.LisätyöNro = 0) AND (HenkTunnit.LisätyöNro <> @ProjNo) AND 
                          (HenkTunnit.LisätyöNro2 <> @ProjNo) AND (HenkTunnit.LisätyöNro3 <> @ProjNo) AND (LisätyötunnitDuunarit.LisätyöNro = @ProjNo)
    1 : This code is written in my Form's VB code part.
    Code:
    HenkTunnitTableAdapter.UPDATE_Lisätyö(ProjNo:=LsiätyöNroTextBox.Text)
    (So, basically I just call the TableAdapter and the query in it. Then add the variable that is connected to the value of the TextBox.)





    2 : This code is written in the TableAdapter as my update query (UPDATE_Lisätyö). The last code part has now been removed and the query runs like normal..... The action executes and updates the "LisätyöNro" -column from the table "HenkTunnit" and adds the value "@ProjNo" (that is written in my TextBox) into the specific row. But it has an error in the logic and needs the part of code I removed....
    Code:
    UPDATE    HenkTunnit
    SET              LisätyöNro = @ProjNo
    FROM         LisätyötunnitDuunarit INNER JOIN
                          HenkTunnit ON LisätyötunnitDuunarit.[Pvm.] = HenkTunnit.[Pvm.] AND LisätyötunnitDuunarit.ProjHenkID = HenkTunnit.ProjHenkID
    WHERE     (HenkTunnit.LisätyöNro2 = 0) AND (HenkTunnit.LisätyöNro3 = 0) AND (HenkTunnit.LisätyöNro = 0) AND (HenkTunnit.LisätyöNro <> @ProjNo) AND 
                          (HenkTunnit.LisätyöNro2 <> @ProjNo) AND (HenkTunnit.LisätyöNro3 <> @ProjNo)
    2 : This code is written in my Form's VB code part.
    Code:
    HenkTunnitTableAdapter.UPDATE_Lisätyö(ProjNo:=LsiätyöNroTextBox.Text)







    So, in conclusion:
    Does anyone have any idea why the UPDATE-query stops working in VB after I add a piece of code
    to the SQL-query, but still continues to work when it is executed in the "Execute Query"????


    Below is a picture of the two tables in SQL Server I'm working with... (Just for clarification...)
    Attached Images Attached Images  

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width