Stupid SQL... Where condition causing: "string or binary data would be truncated.\r\nthe statement has been terminated."

Question

1.00/5 (1 vote)

See more:

i is a row number integer. Where R = 'RowNumber' won't work. gives:
"String or binary data would be truncated.\r\nThe statement has been terminated."

All afternoon.

C#

For(i=0.....)
{
                    string RowNumber = i.ToString();
                    string UpdateCmd = "UPDATE [Monthly Service] SET Location = '" + data.Location + "' , Address = '" + data.Address
                    + "', Name = '" + data.Name + "', Testing = '" + data.Testing + "', Fill_Time = '" + data.FillTime + "', Notes = '" + data.Notes
                    + "', Code_Key = '" + data.CodeKey + "', Phone = '" + data.Phone + "' Where R = 'RowNumber'";  

                    SqlCommand ThisUpdateCommand = new SqlCommand(UpdateCmd, myConnection);
                    try
                    {
                        ThisUpdateCommand.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        
                    }

What I have tried:

Really? - I'm so sick of googling and SO today...

Posted 5-Jul-20 12:15pm

Ron Anders

Updated 5-Jul-20 16:48pm

Patrice T

v2

Add a Solution

Comments

Afzaal Ahmad Zeeshan 5-Jul-20 20:50pm

"String or binary data would be truncated.\r\nThe statement has been terminated."

The error means that your data (in one of these columns) is larger than the column (in the database) allows. Check which column it is.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2020-07-05T12:40:00

C#

string UpdateCmd = "UPDATE [Monthly Service] SET Location = '" + data.Location + "' , Address = '" + data.Address
+ "', Name = '" + data.Name + "', Testing = '" + data.Testing + "', Fill_Time = '" + data.FillTime + "', Notes = '" + data.Notes
+ "', Code_Key = '" + data.CodeKey + "', Phone = '" + data.Phone + "' Where R = 'RowNumber'";

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

MadMyche · Answer 2 · 2020-07-05T15:46:00

Your actual error is coming from WHERE R = 'RowNumber'. This is a hardcoded value, and not the variable you assigned to the variable RowNumber.

Second thing is the SQL Injection issue. It is easy enough to do it the right way the first time. And this has the fix for the aforementioned RowNumber problem as well.

C#

string RowNumber = i.ToString();

string UpdateCmd = "UPDATE [Monthly Service] SET Location=@Lo, Address=@Ad, Name=@Nm, Testing=@Ts, FillTime=@FT, Notes=@Ns, Code_Key=@CK, Phone=@Ph WHERE R=@Row";

SqlCommand ThisUpdateCommand = new SqlCommand(UpdateCmd, myConnection);

ThisUpdateCommand.Parameters.AddWithValue("@Lo", data.Location);
ThisUpdateCommand.Parameters.AddWithValue("@Ad", data.Address);
ThisUpdateCommand.Parameters.AddWithValue("@Nm", data.Name);
ThisUpdateCommand.Parameters.AddWithValue("@Ts", data.Testing);
ThisUpdateCommand.Parameters.AddWithValue("@FT", data.FillTime);
ThisUpdateCommand.Parameters.AddWithValue("@Ns", data.Notes);
ThisUpdateCommand.Parameters.AddWithValue("@CK", data.CodeKey);
ThisUpdateCommand.Parameters.AddWithValue("@Ph", data.Phone);
ThisUpdateCommand.Parameters.AddWithValue("@Row", RowNumber);

I question the database schema, saving INTs as string (R:RowNumber) and several column names which are special/reserved words in SQL Server (Name, Address, Location).

The last thing is that this occurs within a FOR...NEXT. There would be a lot less overhead if you declared the SQL Command before the loop; and then all you need to do is to reassign the parameters within the loop.

C#

string UpdateCmd = "UPDATE [Monthly Service] SET Location=@Lo, Address=@Ad, Name=@Nm, Testing=@Ts, FillTime=@FT, Notes=@Ns, Code_Key=@CK, Phone=@Ph WHERE R=@Row";
SqlCommand ThisUpdateCommand = new SqlCommand(UpdateCmd, myConnection);

For(i=0.....)
{
   ThisUpdateCommand.Parameters.Clear();   
   ThisUpdateCommand.Parameters.AddWithValue("@Lo", data.Location);
   ThisUpdateCommand.Parameters.AddWithValue("@Ad", data.Address);
   ThisUpdateCommand.Parameters.AddWithValue("@Nm", data.Name);
   ThisUpdateCommand.Parameters.AddWithValue("@Ts", data.Testing);
   ThisUpdateCommand.Parameters.AddWithValue("@FT", data.FillTime);
   ThisUpdateCommand.Parameters.AddWithValue("@Ns", data.Notes);
   ThisUpdateCommand.Parameters.AddWithValue("@CK", data.CodeKey);
   ThisUpdateCommand.Parameters.AddWithValue("@Ph", data.Phone);
   ThisUpdateCommand.Parameters.AddWithValue("@Row", i.ToString());

Ron Anders · Answer 3 · 2020-07-05T16:48:00

Solution 3

I "fixed" it. It was syntax.
I'm stupider then sql.

Someone in power please delete this thread it's not worthy of CP.

Posted 5-Jul-20 16:48pm

Ron Anders

Comments

CHill60 6-Jul-20 9:25am

I believe you can delete your own posts - that would take the thread with it.
Alternatively you could share what the syntax issue was to make the thread useful

MadMyche 6-Jul-20 10:00am

Please update this answer with the "fixed" code by using the Improve Solution widget.
While you may think this whole thing was stupid, others may learn from it.

Ron Anders 6-Jul-20 13:34pm

I only got it to not generate and exception. So i haven't posted the "fix". It didn't work, just didn't die.

@ MadMyche

I put your code you posted into play. Very nice and very neat. thank you.
However after it is all said and done WHERE R=@Row generates the same: {"String or binary data would be truncated.\r\nThe statement has been terminated."} error as if we're am trying to assign. I'm not I am only wanting to update the record that matches the Row.

As for Row being a char: I initially had it as an int but the json stringify in the view nulled it so all I got for the row number was "". K, fine just to get this working so I can prove my thinking, be a varchar and we'll cast it over here in the controller for now. if you google this error, Every single return is an assignment issue. This is a comparison! Stupid SQL - which I claim in jest btw. :-)

Stupid SQL... Where condition causing: "string or binary data would be truncated.\r\nthe statement has been terminated."

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0