Look at the error: it contains explicit information.
String or binary data would be truncated.
What that means is that one of the fields you are attempting to INSERT to is shorter than the data you are trying to insert.
For example, an NVARCHAR(5) field and the data 'ABCDEF'
Because the string is longer than the space provided, SQL rightly refuses to INSERT the data because it would have to throw away some of it and it doesn't like doing that.
To fix this, first find out what data you are trying to INSERT, and compare that against your table definition to find out which field is showing the problem.
Then either enlarge the field to fit the data, or in your presentation software validate the inputs to ensure they do fit in the existing column.
In practice, you should probably do both!
Quote:
One last thing , can you please tell me the meaning of (@param1 , @param2 .......)
I can understand a little that these are 5 fields of the table which SQL will write.
A basic SQL INSERT is like this:
INSERT INTO MyTable (Column1Name, Column2Name) VALUES (ValueForColumn1, ValueForColumn2)
But that's dangerous when you start sending text from an application because it leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
This is generally considered a Bad Thing. :D
The way to prevent that is to use parameterized queries - or to be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
A parameterized query looks much the same as a basic INSERT:
INSERT INTO MyTable (Column1Name, Column2Name) VALUES (@ValueForColumn1, @ValueForColumn2)
The "@" indicates to SQL what value to insert from which parameter to the query, and the parameters are added via special code which differs from language to language.
For C#:
using (SqlConnection con = new SqlConnection(strConnect))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con))
{
cmd.Parameters.AddWithValue("@C1", myValueForColumn1);
cmd.Parameters.AddWithValue("@C2", myValueForColumn2);
cmd.ExecuteNonQuery();
}
}
And the "@name" parts tie them together.