none
test--- RRS feed

Answers

  • Hi Accesssubform,
    It is recommend to put the insert statement into sql server and find where the error is.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by vanujkpol Thursday, January 2, 2020 5:02 PM
    Tuesday, December 17, 2019 7:16 AM
  •     Or, just count them all … but, better than that … use a Stored Procedure and default all the parameters to NULL or spaces (some of your columns are being set to NULL and some are being set to spaces, it depends on what you want in those columns when nothing is entered). 

    Then, call the Stored Proc and only pass it the parameters with data. You'll have a lot fewer parameters to pass and you'll be less likely to make this mistake again.

    You have 174 input columns!!!  Wow! But only 173 values, so that's what you're missing … but, which value is missing might be difficult to find.

    I don't know how to create Stored Procedures in Access (or how to even call them), but in SQL Server it would be like the following and I imagine that Access would have something similar:

    CREATE PROCEDURE InsertClaimsDetachAuth
        @Project_ID int, 
        @Claim int, 
        @Type CHAR(1) = NULL, -- or larger number of CHARs, whatever is appropriate for your data
        @Total_Lines int = NULL,
        @GRGR_CK int = NULL, 
        @Line_Count int = NULL,
        @Original_Payment int = NULL, 
        @New_Provider VARCHAR(100) = NULL, 
        @L1 int = NULL, 
        @L2 int = NULL, 
        @L3 int = NULL, 
        -- list all the rest of your parameters, defaulting them as appropriate
    AS
    BEGIN
        INSERT INTO dbo.[01_Claims_DetachAuth] 
        (Project_ID, Claim, Type, Total_Lines, GRGR_CK, Line_Count, Original_Payment, New_Provider, 
            L1, L2, L3, -- list all the rest of your column names
        )
        VALUES
        (@Project_ID, @Claim, @Type, @Total_Lines, @GRGR_CK, @Line_Count, @Original_Payment, @New_Provider,
         @L1, @L2, @L3, -- list all the rest of your parameters, in the same order as the column names
        )
    END
    

    Then your insert becomes much simpler, with a call to the new Stored Procedure like this:

    EXEC InsertClaimsDetachAuth 
        @Project_ID = 183, 
        @Claim = '19346Y001900', 
        @Type = 'H', 
        @Total_Lines = 1, 
        @GRGR_CK = 23, 
        @Line_Count = 1, 
        @Original_Payment = 0, 
        @New_Provider = '',
        @L1 = 1,
        @NR1 = 0,
        @Note_Text = '6100 Robot Detach Auth Processing', 
        @Eob_Claim = 'PreAuthorization',
        @Original_Agreement = 905714700

    There you go ... much, *much* easier ... both to write and to read!!!



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by vanujkpol Thursday, January 2, 2020 5:02 PM
    Wednesday, December 25, 2019 5:44 PM

All replies

  • Hi Accesssubform,
    It is recommend to put the insert statement into sql server and find where the error is.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by vanujkpol Thursday, January 2, 2020 5:02 PM
    Tuesday, December 17, 2019 7:16 AM
  •     Or, just count them all … but, better than that … use a Stored Procedure and default all the parameters to NULL or spaces (some of your columns are being set to NULL and some are being set to spaces, it depends on what you want in those columns when nothing is entered). 

    Then, call the Stored Proc and only pass it the parameters with data. You'll have a lot fewer parameters to pass and you'll be less likely to make this mistake again.

    You have 174 input columns!!!  Wow! But only 173 values, so that's what you're missing … but, which value is missing might be difficult to find.

    I don't know how to create Stored Procedures in Access (or how to even call them), but in SQL Server it would be like the following and I imagine that Access would have something similar:

    CREATE PROCEDURE InsertClaimsDetachAuth
        @Project_ID int, 
        @Claim int, 
        @Type CHAR(1) = NULL, -- or larger number of CHARs, whatever is appropriate for your data
        @Total_Lines int = NULL,
        @GRGR_CK int = NULL, 
        @Line_Count int = NULL,
        @Original_Payment int = NULL, 
        @New_Provider VARCHAR(100) = NULL, 
        @L1 int = NULL, 
        @L2 int = NULL, 
        @L3 int = NULL, 
        -- list all the rest of your parameters, defaulting them as appropriate
    AS
    BEGIN
        INSERT INTO dbo.[01_Claims_DetachAuth] 
        (Project_ID, Claim, Type, Total_Lines, GRGR_CK, Line_Count, Original_Payment, New_Provider, 
            L1, L2, L3, -- list all the rest of your column names
        )
        VALUES
        (@Project_ID, @Claim, @Type, @Total_Lines, @GRGR_CK, @Line_Count, @Original_Payment, @New_Provider,
         @L1, @L2, @L3, -- list all the rest of your parameters, in the same order as the column names
        )
    END
    

    Then your insert becomes much simpler, with a call to the new Stored Procedure like this:

    EXEC InsertClaimsDetachAuth 
        @Project_ID = 183, 
        @Claim = '19346Y001900', 
        @Type = 'H', 
        @Total_Lines = 1, 
        @GRGR_CK = 23, 
        @Line_Count = 1, 
        @Original_Payment = 0, 
        @New_Provider = '',
        @L1 = 1,
        @NR1 = 0,
        @Note_Text = '6100 Robot Detach Auth Processing', 
        @Eob_Claim = 'PreAuthorization',
        @Original_Agreement = 905714700

    There you go ... much, *much* easier ... both to write and to read!!!



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by vanujkpol Thursday, January 2, 2020 5:02 PM
    Wednesday, December 25, 2019 5:44 PM

  • Thursday, January 2, 2020 5:03 PM