none
sql procedure - LINQ error RRS feed

  • Question

  • Hi I have a problem with my app, I have a procedure in database that get values

    USE [sn]
    GO
    /****** Object:  StoredProcedure [dbo].[SprawozdanieDzienneDodaj]    Script Date: 2014-07-08 19:50:46 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[SprawozdanieDzienneDodaj]
    	-- Add the parameters for the stored procedure here
    	@IdPracownika as int,
    	@DataWprowadzenia as datetime,
    	@IdOkresu as int,
    	@OdGodziny as decimal,
    	@DoGodziny as decimal,
    	@Suma as decimal,
    	@IdBrygadzisty as int,
    	@IdBudowy as int,
    	@Uwaga as varchar(550)
    AS
    BEGIN
    
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    DECLARE @IdSprawozdania as int
        -- POBIERANIE ID O ILE ISTNIEJE WPIS
    	SET @IdSprawozdania = (SELECT IdSprawozdania FROM SprawozdanieDzienne WHERE IdPracownika=@IdPracownika AND IdOkresu=@IdOkresu AND Data=@DataWprowadzenia)
    	
    
    	IF @IdSprawozdania = NULL
    	BEGIN
    	--JEŚLI NIE MA TO WRZUCA I POBIERA ID WRZUCONEGO ELEMENTU PÓXNIEJ WRZUCA DO CZĘŚCIOWEGO WARTOŚCI
    	INSERT INTO SprawozdanieDzienne (Data, IdOkresu, IdPracownika) VALUES (@DataWprowadzenia,@IdOkresu,@IdPracownika)
    	SET @IdSprawozdania = (SELECT IdSprawozdania FROM SprawozdanieDzienne WHERE Data=@DataWprowadzenia AND IdOkresu=@IdOkresu AND IdPracownika=@IdPracownika)
    	INSERT INTO SprawozdanieCzesciowe (Od,Do,Suma,IdBudowy,IdBrygadzisty,IdSprawozdaniaDziennego,Uwaga)VALUES(@OdGodziny, @DoGodziny, @Suma, @IdBudowy,@IdBrygadzisty,@IdSprawozdania,@Uwaga)
    	END
    	ELSE
    	BEGIN
    	INSERT INTO SprawozdanieCzesciowe (Od,Do,Suma,IdBudowy,IdBrygadzisty,IdSprawozdaniaDziennego,Uwaga)VALUES(@OdGodziny, @DoGodziny, @Suma, @IdBudowy,@IdBrygadzisty,@IdSprawozdania,@Uwaga)
    	END
    	--execute SprawozdanieCzescioweDodaj 1, '2014-06-06'
    END
    

    The value

    @IdSprawozdania

    is a not null forgin key.

    When i call the procedure i get a erroe in VS that i muss send the value 

    @IdSprawozdania

    Can You help me?

    Tuesday, July 8, 2014 8:05 PM

All replies

  • Hello,

    >>When i call the procedure i get a erroe in VS that i muss send the value

    What is the error message? Please share it with us.

    >>The value @IdSprawozdania is a not null forgin key.

    From your stored procedure, if this variable is not null, the insert operation would complete successfully, do you have a try to debug your stored procedure to check whether the IdSprawozdania is really not null? With your SP, I made a small demo to test it, however, it could work fine.

    For how to debug the SP:

    http://msdn.microsoft.com/en-us/library/hh272701(v=vs.103).aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 9, 2014 8:09 AM
    Moderator
  • in sql i get 

    Cannot insert the value NULL into column 'IdSprawozdaniaDziennego', table 'sn.dbo.SprawozdanieCzesciowe'; column does not allow nulls. INSERT fails.

    but there is 

    	SET @IdSprawozdania = (SELECT IdSprawozdania FROM SprawozdanieDzienne WHERE Data=@DataWprowadzenia AND IdOkresu=@IdOkresu AND IdPracownika=@IdPracownika)
    

    that gets value from db

    Thursday, July 10, 2014 6:22 AM
  • Hello,

    From the error message, it shows clearly that the variable 'IdSprawozdaniaDziennego' is null.

    Do you have a try to debug this stored procedure to check which line throws this error message? If you not, please try it.

    >> but there is SET @IdSprawozdania = (SELECT IdSprawozdania FROM SprawozdanieDzienne WHERE Data=@DataWprowadzenia AND IdOkresu=@IdOkresu AND IdPracownika=@IdPracownika) that gets value from db

    There are two same select statement above, which one do you mean?

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 11, 2014 5:56 AM
    Moderator