none
Procedure or function “BillAdd” expects parameter ‘@bi_finald’, which was not supplied. RRS feed

  • Question

  • I am trying to insert a null value into a date table field, but to no avail. Can anyone shed light on this? Thank you in advance, I am new to this.

    This is where I declare the variable xfinald.

            private DateTime? xfinald;

    This is some logic used before the code below.

                    if(MeterRec.me_final == true)
                    {
                        xfinal = true;
                        xfinald = DateTime.Now;
                    }
                    else
                    {
                        xfinald = null;
                    }

    This is my line of code where xfinald is equal to null.

                    BillRec.bi_finald = xfinald;

    If I change the above line to it works fine.

                    //BillRec.bi_finald = DateTime.Today;

    This is where I define the field.

            public DateTime? bi_finald { get; set; }

    Attached is my stored procedure BillAdd.

    CREATE PROCEDURE [dbo].[BillAdd]
    	@bi_status varchar(1),
    	@bi_acct varchar(14),
    	@bi_suffix int,
    	@bi_pread1 int, 
    	@bi_cread1 int, 
    	@bi_use1 int, 
    	@bi_pread2 int, 
    	@bi_cread2 int, 
    	@bi_use2 int, 
    	@bi_pread3 int, 
    	@bi_cread3 int, 
    	@bi_use3 int, 
    	@bi_book varchar(3), 
    	@bi_balfwd numeric(9,2),
    	@bi_chg1 numeric(9,2),
    	@bi_chg2 numeric(9,2),
    	@bi_chg3 numeric(9,2),
    	@bi_chg4 numeric(9,2),
    	@bi_chg5 numeric(9,2),
    	@bi_chg6 numeric(9,2),
    	@bi_chg7 numeric(9,2),
    	@bi_chg8 numeric(9,2),
    	@bi_chg9 numeric(9,2),
    	@bi_chg10 numeric(9,2),
    	@bi_desc1 varchar(10), 
    	@bi_desc2 varchar(10), 
    	@bi_desc3 varchar(10), 
    	@bi_desc4 varchar(10), 
    	@bi_desc5 varchar(10), 
    	@bi_desc6 varchar(10), 
    	@bi_desc7 varchar(10), 
    	@bi_desc8 varchar(10), 
    	@bi_desc9 varchar(10), 
    	@bi_desc10 varchar(10), 
    	@bi_serfrom date,
    	@bi_serto date,
    	@bi_use01 int,
    	@bi_use02 int,
    	@bi_use03 int,
    	@bi_use04 int,
    	@bi_use05 int,
    	@bi_use06 int,
    	@bi_use07 int,
    	@bi_use08 int,
    	@bi_use09 int,
    	@bi_use10 int,
    	@bi_use11 int,
    	@bi_use12 int,
    	@bi_perc01 int,
    	@bi_perc02 int,
    	@bi_perc03 int,
    	@bi_perc04 int,
    	@bi_perc05 int,
    	@bi_perc06 int,
    	@bi_perc07 int,
    	@bi_perc08 int,
    	@bi_perc09 int,
    	@bi_perc10 int,
    	@bi_perc11 int,
    	@bi_perc12 int,
    	@bi_highuse int,
    	@bi_month int,
    	@bi_final bit,
    	@bi_finald date,
    	@bi_serdays int,
    	@bi_code varchar(2),
    	@bi_zip varchar(10),
    	@bi_cacode varchar(2),
    	@bi_caroute int,
    	@bi_cycle int,
    	@bi_date date,
    	@bi_draft bit,
    	@bi_no int,
    	@bi_ap int,
    	@bi_walk int,
    	@bi_totuse int,
    	@bi_totuse1 int,
    	@bi_totuse2 int,
    	@bi_totuse3 int,
    	@bi_deposit numeric(9,2),
    	@bi_pread1t int,
    	@bi_cread1t int,
    	@bi_pread2t int,
    	@bi_cread2t int,
    	@bi_pread3t int,
    	@bi_cread3t int,
    	@bi_factor int,
    	@bi_name varchar(25),
    	@bi_last varchar(20),
    	@bi_chg11 numeric(9,2),
    	@bi_chg12 numeric(9,2),
    	@bi_chg13 numeric(9,2),
    	@bi_chg14 numeric(9,2),
    	@bi_chg15 numeric(9,2),
    	@bi_chg16 numeric(9,2),
    	@bi_chg17 numeric(9,2),
    	@bi_chg18 numeric(9,2),
    	@bi_chg19 numeric(9,2),
    	@bi_chg20 numeric(9,2),
    	@bi_desc11 varchar(10),
    	@bi_desc12 varchar(10),
    	@bi_desc13 varchar(10),
    	@bi_desc14 varchar(10),
    	@bi_desc15 varchar(10),
    	@bi_desc16 varchar(10),
    	@bi_desc17 varchar(10),
    	@bi_desc18 varchar(10),
    	@bi_desc19 varchar(10),
    	@bi_desc20 varchar(10),
    	@me_no11 bit,
    	@me_no12 bit,
    	@me_no13 bit,
    	@me_no14 bit,
    	@me_no15 bit,
    	@me_no16 bit,
    	@me_no17 bit,
    	@me_no18 bit,
    	@me_no19 bit,
    	@me_no20 bit,
    	@bi_est bit,
    	@bi_scanlin varchar(29),
    	@bi_due date
    
    
    
    
    
    AS
    	BEGIN
    	INSERT INTO ub_bills
    	(bi_status, 
    	bi_acct,
    	bi_suffix,
    	bi_pread1, 
    	bi_cread1, 
    	bi_use1, 
    	bi_pread2, 
    	bi_cread2,
    	bi_use2,
    	bi_pread3,
    	bi_cread3,
    	bi_use3,
    	bi_book,
    	bi_balfwd,
    	bi_chg1,
    	bi_chg2,
    	bi_chg3,
    	bi_chg4,
    	bi_chg5,
    	bi_chg6,
    	bi_chg7,
    	bi_chg8,
    	bi_chg9,
    	bi_chg10,
    	bi_desc1,
    	bi_desc2,
    	bi_desc3,
    	bi_desc4,
    	bi_desc5,
    	bi_desc6,
    	bi_desc7,
    	bi_desc8,
    	bi_desc9,
    	bi_desc10,
    	bi_serfrom,
    	bi_serto,
    	bi_use01,
    	bi_use02,
    	bi_use03,
    	bi_use04,
    	bi_use05,
    	bi_use06,
    	bi_use07,
    	bi_use08,
    	bi_use09,
    	bi_use10,
    	bi_use11,
    	bi_use12,
    	bi_perc01,
    	bi_perc02,
    	bi_perc03,
    	bi_perc04,
    	bi_perc05,
    	bi_perc06,
    	bi_perc07,
    	bi_perc08,
    	bi_perc09,
    	bi_perc10,
    	bi_perc11,
    	bi_perc12,
    	bi_highuse,
    	bi_month,
    	bi_final,
    	bi_finald,
    	bi_serdays,
    	bi_code,
    	bi_zip,
    	bi_cacode,
    	bi_caroute,
    	bi_cycle,
    	bi_date,
    	bi_draft,
    	bi_no,
    	bi_ap,
    	bi_walk,
    	bi_totuse,
    	bi_totuse1,
    	bi_totuse2,
    	bi_totuse3,
    	bi_deposit,
    	bi_pread1t,
    	bi_cread1t,
    	bi_pread2t,
    	bi_cread2t,
    	bi_pread3t,
    	bi_cread3t,
    	bi_factor,
    	bi_name,
    	bi_last,
    	bi_chg11,
    	bi_chg12,
    	bi_chg13,
    	bi_chg14,
    	bi_chg15,
    	bi_chg16,
    	bi_chg17,
    	bi_chg18,
    	bi_chg19,
    	bi_chg20,
    	bi_desc11,
    	bi_desc12,
    	bi_desc13,
    	bi_desc14,
    	bi_desc15,
    	bi_desc16,
    	bi_desc17,
    	bi_desc18,
    	bi_desc19,
    	bi_desc20,
    	me_no11,
    	me_no12,
    	me_no13,
    	me_no14,
    	me_no15,
    	me_no16,
    	me_no17,
    	me_no18,
    	me_no19,
    	me_no20,
    	bi_est,
    	bi_scanlin,
    	bi_due
    
    	)
    	VALUES
    	(
    	@bi_status,
    	@bi_acct,
    	@bi_suffix,
    	@bi_pread1, 
    	@bi_cread1, 
    	@bi_use1, 
    	@bi_pread2, 
    	@bi_cread2,
    	@bi_use2,
    	@bi_pread3,
    	@bi_cread3,
    	@bi_use3,
    	@bi_book,
    	@bi_balfwd,
    	@bi_chg1,
    	@bi_chg2,
    	@bi_chg3,
    	@bi_chg4,
    	@bi_chg5,
    	@bi_chg6,
    	@bi_chg7,
    	@bi_chg8,
    	@bi_chg9,
    	@bi_chg10,
    	@bi_desc1,
    	@bi_desc2,
    	@bi_desc3,
    	@bi_desc4,
    	@bi_desc5,
    	@bi_desc6,
    	@bi_desc7,
    	@bi_desc8,
    	@bi_desc9,
    	@bi_desc10,
    	@bi_serfrom,
    	@bi_serto,
    	@bi_use01,
    	@bi_use02,
    	@bi_use03,
    	@bi_use04,
    	@bi_use05,
    	@bi_use06,
    	@bi_use07,
    	@bi_use08,
    	@bi_use09,
    	@bi_use10,
    	@bi_use11,
    	@bi_use12,
    	@bi_perc01,
    	@bi_perc02,
    	@bi_perc03,
    	@bi_perc04,
    	@bi_perc05,
    	@bi_perc06,
    	@bi_perc07,
    	@bi_perc08,
    	@bi_perc09,
    	@bi_perc10,
    	@bi_perc11,
    	@bi_perc12,
    	@bi_highuse,
    	@bi_month,
    	@bi_final,
    	@bi_finald,
    	@bi_serdays,
    	@bi_code,
    	@bi_zip,
    	@bi_cacode,
    	@bi_caroute,
    	@bi_cycle,
    	@bi_date,
    	@bi_draft,
    	@bi_no,
    	@bi_ap,
    	@bi_walk,
    	@bi_totuse,
    	@bi_totuse1,
    	@bi_totuse2,
    	@bi_totuse3,
    	@bi_deposit,
    	@bi_pread1t,
    	@bi_cread1t,
    	@bi_pread2t,
    	@bi_cread2t,
    	@bi_pread3t,
    	@bi_cread3t,
    	@bi_factor,
    	@bi_name,
    	@bi_last,
    	@bi_chg11,
    	@bi_chg12,
    	@bi_chg13,
    	@bi_chg14,
    	@bi_chg15,
    	@bi_chg16,
    	@bi_chg17,
    	@bi_chg18,
    	@bi_chg19,
    	@bi_chg20,
    	@bi_desc11,
    	@bi_desc12,
    	@bi_desc13,
    	@bi_desc14,
    	@bi_desc15,
    	@bi_desc16,
    	@bi_desc17,
    	@bi_desc18,
    	@bi_desc19,
    	@bi_desc20,
    	@me_no11,
    	@me_no12,
    	@me_no13,
    	@me_no14,
    	@me_no15,
    	@me_no16,
    	@me_no17,
    	@me_no18,
    	@me_no19,
    	@me_no20,
    	@bi_est,
    	@bi_scanlin,
    	@bi_due
    
    
    	)
    
    
    
    	END




    DrewT1755




    • Edited by DrewT1755 Tuesday, June 26, 2018 8:21 PM
    Tuesday, June 26, 2018 7:44 PM

Answers

  • Your C# code is fine, this is a runtime error. The issue is your sproc doesn't accept null for any of the parameters. If you want to be able to pass NULL to a sproc call (via ADO.NET) then you need to mark the parameter as nullable.

    CREATE PROCEDURE [dbo].[BillAdd] (
        …
        @bi_finald date = NULL,
        …)
    
    Note that once you use an optional parameter like this all the remaining parameters need to be nullable as well. If this is the only one that can be null then move it to the bottom of the parameter list. In most cases this should be harmless. The only time it wouldn't be is if your code was making an assumption about parameter ordering.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, June 26, 2018 9:07 PM
    Moderator

All replies

  • Your C# code is fine, this is a runtime error. The issue is your sproc doesn't accept null for any of the parameters. If you want to be able to pass NULL to a sproc call (via ADO.NET) then you need to mark the parameter as nullable.

    CREATE PROCEDURE [dbo].[BillAdd] (
        …
        @bi_finald date = NULL,
        …)
    
    Note that once you use an optional parameter like this all the remaining parameters need to be nullable as well. If this is the only one that can be null then move it to the bottom of the parameter list. In most cases this should be harmless. The only time it wouldn't be is if your code was making an assumption about parameter ordering.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, June 26, 2018 9:07 PM
    Moderator
  • Thank you sir!

    DrewT1755

    Wednesday, June 27, 2018 12:32 PM