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...)