Exception : The length of the parameter exceeds the limit of 128 characters in C#

Question

I m trying to insert the values in SQLServer using WCF Service. there are 5 rows to be inserted.

its giving exception :

The length of the parameter exceeds the limit of 128 characters

yes, its length is more then 128 characters.But i have declare the size NVarChar(4000).

I've searched this site, and other sites to understand and get rid of this exception

MedicineTestName = "1,crocin,2,aspirin,2,naproxen,3,ibuprofen,3,Ketaprofen";

code:

public static string InsertHistory(string PatientId, string MedicineTestName, string CreateBy)
    {
       DataSet objdata;
       object objSubjectReader = new object();
        try
        {
               StringBuilder sb = new StringBuilder();

            string[] wordswithcomma = MedicineTestName.Split(',');

            for (int i = 0; i < wordswithcomma.Length -1 ; i=i+2)
            {

                string MedicineType = wordswithcomma[i];
                string MedicineName = wordswithcomma[i + 1];

                sb.Append("insert into tblMedicineHistory values(" + PatientId + "," + "'" + MedicineName + "'" + "," + MedicineType + ",GETDATE()," + "'" + CreateBy + "'" + ",GETDATE(),0);");

            }
            string sbo = sb.ToString();

            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter(sbo, System.Data.SqlDbType.NVarChar, 4000);
            param[0].Value = sbo;

           objdata = SqlHelper.ExecuteDataset(ConString, CommandType.StoredProcedure, "SP_MedicineHistory", param);


           return JsonConvert.SerializeObject(objdata, Newtonsoft.Json.Formatting.Indented);
            //return sbo;
        }
        catch (SqlException ex)
        {

            return objSubjectReader.ToString();
        }

    }

thank you.

Marc Gravell · Accepted Answer · 2015-03-25T11:11:17.280

3

I think it is complaining about the name of the parameter; you are passing sbo as the parameter name, but sbo is the fully composed TSQL, which will be... long. Note: your SQL is thoroughly unsafe - that is not how one parameterizes SQL!

Correctly parameterized SQL for a single row would be something like:

const string sql = @"
insert into tblMedicineHistory (PatientID, MedicineName, MedicineType, ...)
values(@patientId, @medicineName, @medicineType, ...)";

var args = new[] {
    new SqlParameter("@patientId", PatientId),
    new SqlParameter("@medicineName", MedicineName),
    new SqlParameter("@medicineType", MedicineType),
    ...
}

edited Mar 25 '15 at 11:11

answered Mar 25 '15 at 11:05

Marc Gravell

1,026,079
266
2,566
2,900

thank you,both answer were helpful and i m new to this.can you provide any link to learn properly. – the_mahasagar Mar 25 '15 at 11:21
@SClearner actually, a good starting point might be [my other answer from this morning](http://stackoverflow.com/a/29253209/23354) - from which you can also learn the semi-comforting fact: it isn't uncommon to get this wrong ;p – Marc Gravell Mar 25 '15 at 11:25

Exception : The length of the parameter exceeds the limit of 128 characters in C#

1 Answers1