Why do I get this exception when I perform this simple insert query?

Question

I have a problem with this code that perform an insert query (the problem is that when it try to execute the query an exception is thrown):

_strSQL = "INSERT INTO Cpe ( DateAdded,  [Cpe] ";
strSQLParametri = " VALUES ( GETDATE(), @CPE ";
addParameter(command, "@CPE ", cpe.Cpe);


// [SourceId] insertion on the DB:
if (cpe.SourceId != null)
{
                _strSQL += ",[SourceId] ";
                strSQLParametri += ", @SOURCEID ";
                addParameter(command, "@SOURCEID ", cpe.SourceId);
}

// [vendor_id] insertion on the DB:
if (cpe.VendorId != null)
{
                _strSQL += ",[vendor_id] ";
                strSQLParametri += ", @VENDORID ";
                addParameter(command, "@VENDORID ", cpe.VendorId);
}

// [Title] insertion on the DB:
if (cpe.Title != null)
{
                _strSQL += ",[Title] ";
                strSQLParametri += ", @TITLE ";
                addParameter(command, "@TITLE ", cpe.Title);
}

// [part] insertion on the DB:
if (cpe.Part != null)
{
                _strSQL += ",[part] ";
                strSQLParametri += ", @PART ";
                addParameter(command, "@PART ", cpe.Part.ToString());
}

// [product_id] insertion on the DB:
if (cpe.ProductId != null)
{
                _strSQL += ",[product_id] ";
                strSQLParametri += ", @PRODUCTID";
                addParameter(command, "@PRODUCTID ", cpe.ProductId);
}

// [version] insertion on the DB:
if (cpe.Version != null)
{
                _strSQL += ",[version] ";
                strSQLParametri += ", @VERSION";
                addParameter(command, "@VERSION ", cpe.Version);
}

// [revision] insertion on the DB:
if (cpe.Revision != null)
{
               _strSQL += ",[revision] ";
                strSQLParametri += ", @REVISION";
                addParameter(command, "@REVISION ", cpe.Revision);
}

// [edition] insertion on the DB:
if (cpe.Edition != null)
{
                _strSQL += ",[edition] ";
                strSQLParametri += ", @EDITION";
                addParameter(command, "@EDITION ", cpe.Edition);
}

.... and so on ......

query = _strSQL + " ) " + strSQLParametri + " );";
command.CommandText = query;
_executeNoQuery(command);

newId = _getIdentity();
//Debug.WriteLine("Id: " + newId);
#endregion

This is the obtained query see bay the debugger what (there are not all the previous fields because someone are null and so they are skipped):

INSERT INTO Cpe (DateAdded, [Cpe], [SourceId], [vendor_id], [Title], [part], [product_id], [version])  
VALUES (GETDATE(), @CPE, @SOURCEID, @VENDORID, @TITLE, @PART, @PRODUCTID, @VERSION);

And this is the obtained exception that happen when the previous query is executed:

ExecuteNonQuery terminato con errori. \r\nINSERT INTO Cpe ( DateAdded, [Cpe] ,[SourceId] ,[vendor_id] ,[Title] ,[part] ,[product_id] ,[version] ) VALUES ( GETDATE(), @CPE , @SOURCEID , @VENDORID , @TITLE , @PART , @PRODUCTID, @VERSION );
String or binary data would be truncated.

What could be the problem? Do you have some idea about how can I solve?

Check if there is any inner exception that might give you the exact error. I am assuming that you are probably not adding all the required parameters. — Habib, Mar 25 '14 at 16:31
What is the inner working of addParameter? Did you account for the datatypes of the parameters and for the length of text fields? — Steve, Mar 25 '14 at 16:33
possible duplicate of [error, string or binary data would be truncated when trying to insert](http://stackoverflow.com/questions/5591473/error-string-or-binary-data-would-be-truncated-when-trying-to-insert) — Mahe, Jan 30 '15 at 13:21

score 2 · Answer 1 · answered Mar 25 '14 at 16:33

This part of the error message String or binary data would be truncated. suggests that you're trying to insert data into a field that is too small for it.

Check the length of any text data you're inserting, then check the size of the fields you're trying to insert it into, make sure it'll fit.

score 0 · Answer 2 · answered Mar 25 '14 at 16:33

0

Check the length of each string value you are passing in. One of them is almost certainly longer than the length of the column into which you are inserting the data.

answered Mar 25 '14 at 16:33

Chris Ballard

3,771
4
28
40

Why do I get this exception when I perform this simple insert query?

2 Answers2