SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects

Article
2013-08-31

Question

_{Saturday, August 31, 2013 8:39 AM}

Dear All

i searched on google but did not find exact solution.. pls help to resolve this problem..

I have a procedure and i am using to insert values in table..like below
but i got error like:-
The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

This PROCEDURE

alter PROCEDURE ReadInsert
(
@HSCP_FILE_NAME char(50)
)

as
Begin
declare @PLANT_CD char(10)
declare @NEW_SUPP_CD char(20)
declare @NEW_SUPP_LOC char(10)
declare @SUPP_LOC_PROV_STAT char(10)
declare @SUPP_INVOICE_DT char(20)
declare @NEWINVOICENO char(20)
declare @PART_RCV_DT char(10)

declare @OE_INVOICE_NUM char(20)
declare @FORM31NO char(20)
declare @FINYEAR char(10)

declare @AvgD19 float
declare @SumD16 float
declare @SumD24 float
declare @RoundD24 float
declare @RoundD16 float
declare @ValueSalesTxNw float
declare @RoundD21 float

@OEFILE char(50)=null
@VATFILE char(50)=null

select TOP 1 @PLANT_CD = D4_PLANT_CODE from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @NEW_SUPP_CD = D5_VENDOR_CODE from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @NEW_SUPP_LOC = D6_VENDOR_LOC from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @SUPP_LOC_PROV_STAT = D7_VENDOR_SUPPLY_STATE from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @SUPP_INVOICE_DT = D8_INVOICE_DATE from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @NEWINVOICENO = D26_INVOICE_NO from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @PART_RCV_DT = D14_PART_RECEIVED_DATE_AT_HCIL from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @OE_INVOICE_NUM = D9_INVOICE_NO_OF_THE_SUPPLIER from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @FORM31NO = D28_FORM31_NO from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select TOP 1 @FINYEAR = D27_FIN_YEAR from HSCP_DETAIL_CSV where HSCP_FILE_NAME = @HSCP_FILE_NAME

select @AvgD19 = avg(D16_BASIC_PART_COST)from HSCP_DETAIL_CSV group by D26_INVOICE_NO , HSCP_FILE_NAME having HSCP_FILE_NAME = @HSCP_FILE_NAME

select @SumD16 = sum(D16_BASIC_PART_COST)from HSCP_DETAIL_CSV group by D26_INVOICE_NO , HSCP_FILE_NAME having HSCP_FILE_NAME = @HSCP_FILE_NAME

select @SumD24 = sum(D24_EXCISE_AMOUNT)from HSCP_DETAIL_CSV group by D26_INVOICE_NO , HSCP_FILE_NAME having HSCP_FILE_NAME = @HSCP_FILE_NAME

select @RoundD24 = round (sum(D24_EXCISE_AMOUNT),0) from HSCP_DETAIL_CSV group by D26_INVOICE_NO , HSCP_FILE_NAME having HSCP_FILE_NAME = @HSCP_FILE_NAME

select @RoundD16 = round (sum(D16_BASIC_PART_COST),0) from HSCP_DETAIL_CSV group by D26_INVOICE_NO , HSCP_FILE_NAME having HSCP_FILE_NAME = @HSCP_FILE_NAME

select @RoundD21 = round (sum(D21_PURCHASE_COST),0) from HSCP_DETAIL_CSV group by D26_INVOICE_NO , HSCP_FILE_NAME having HSCP_FILE_NAME = @HSCP_FILE_NAME

INSERT INTO MAIN_OE_HEADER ( PLANT_CD , NEW_SUPP_CD , NEW_SUPP_LOC , SUPP_LOC_PROV_STAT , SUPP_INVOICE_DT , NEWINVOICENO , PART_RCV_DT , PART_DESC , SALES_TAX_RATE ,

INVENTORY , TOTAL_EXCISE , TOTAL_EXCISE1 , INVENTORY_NEW , TOTAL_EXCISE_NEW , SALES_TAX_NEW , TOTAL_INVOICE_AMT , STATUS , OE_INVOICE_NUM , FORM31NO , FINYEAR , OEFILE

, VATFILE) VALUES
(@PLANT_CD , @NEW_SUPP_CD , @NEW_SUPP_LOC , @SUPP_LOC_PROV_STAT , @SUPP_INVOICE_DT , @NEWINVOICENO , @PART_RCV_DT , 'AUTOPARTS' , @AvgD19 , @SumD16 , @SumD24 ,

@RoundD24 , @RoundD16 , @RoundD24 , 10 , @RoundD21 , 0 , @OE_INVOICE_NUM , @FORM31NO , @FINYEAR , @OEFILE , @VATFILE )

End
GO

This Frontant to call the Procedure

con.Open();
cmd=new sqlCammadn();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ReadInsert";
cmd.Parameters.Add("@HSCP_FILE_NAME", SqlDbType.Char,50).Value = FileName;
cmd.ExecuteNonQuery();
con.Close();
sr.Close();

Error Generate is like below:-

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Please help to resolve this problem...

All replies (7)

_{Saturday, August 31, 2013 9:50 AM ✅Answered | 1 vote}

        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

_{Saturday, August 31, 2013 11:39 PM ✅Answered}

Assuming that the FileName variable is a string and has a value (that it's not null), then try this syntax instead for adding your parameter:

cmd.Parameters.AddWithValue("@HSCP_FILE_NAME", FileName);

~~Bonnie Berent DeWitt [C# MVP]

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

_{Tuesday, September 3, 2013 6:50 AM}

1. There is typo in above code in "cmd=new sqlCammadn();" it should be "SqlCommand".

2. You're adding a parameter & assigning value in same line, that incorrect. cmd.Parameters.Add("@HSCP_FILE_NAME", SqlDbType.Char,50).Value = FileName;

Correct code is like:

cmd=new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ReadInsert";
cmd.Parameters.Add("@HSCP_FILE_NAME", SqlDbType.Char,50);
cmd.Parameters["@HSCP_FILE_NAME"].Value = FileName;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
sr.Close()

Pranav Singh

_{Tuesday, September 3, 2013 2:44 PM}

You're adding a parameter & assigning value in same line, that incorrect. cmd.Parameters.Add("@HSCP_FILE_NAME", SqlDbType.Char,50).Value = FileName;

Correct code is like:
cmd=new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ReadInsert";
cmd.Parameters.Add("@HSCP_FILE_NAME", SqlDbType.Char,50);
cmd.Parameters.Add["@HSCP_FILE_NAME"].Value = FileName;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
sr.Close()
Pranav Singh

Bad advice, Pranav. There is nothing wrong with adding a SqlParameter and setting its value at the same time (see my example, AddWithValue ... and even the OP's example was ok too). Your example will not work because you're adding the same parameter twice. Unless you meant your second line to be the following, which is correct:

cmd.Parameters["@HSCP_FILE_NAME"].Value = FileName;

Notice that I removed the .Add, which is probably what you meant to write anyway.

~~Bonnie Berent DeWitt [C# MVP]

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

_{Tuesday, September 3, 2013 3:39 PM | 1 vote}

It is better form to specify the parameter length and type explicitly, rather than derive it from the length of the parameter value, at least for SQL Server. For optimal query plan caching, the parameter type and length should be the same on every invocation, and should match the destination column in type.

SQL Server considers queries that vary only by parameter length to be _different_ queries, and so can't cache and reuse a single query plan.

See, eg Query performance and plan cache issues when parameter length not specified correctly

David

David http://blogs.msdn.com/b/dbrowne/

_{Wednesday, September 4, 2013 4:42 AM}

My bad. I was intended same but miss that. Corrected the answer. Thanks for feedback.

Pranav Singh

_{Tuesday, September 17, 2013 7:10 AM}

1. There is typo in above code in "cmd=new sqlCammadn();" it should be "SqlCommand".

2. You're adding a parameter & assigning value in same line, that incorrect. cmd.Parameters.Add("@HSCP_FILE_NAME", SqlDbType.Char,50).Value = FileName;

Correct code is like:
cmd=new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ReadInsert";
cmd.Parameters.Add("@HSCP_FILE_NAME", SqlDbType.Char,50);
cmd.Parameters["@HSCP_FILE_NAME"].Value = FileName;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
sr.Close()
Pranav Singh

Note that you can avoid an extra collection lookup using:

cmd.Parameters.Add("@HSCP_FILE_NAME", SqlDbType.Char,50).Value = FileName;

ROSCO

Share via

SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects

Question

All replies (7)

Additional resources