"String or binary data would be truncated" and field specifications

Hi all,

i have "String or binary data would be truncated" error when i try to execute an insert statment.

can i find witch field is affected by this error? (for return it to the user)

thank's all

If possible it would be far better to truncate the string to the maximum allowable length within the client application (and warn the user if necessary) before passing it to SQL Server for insertion into the database.

For debug purposes you could run SQL Profiler to witness the values of the parameters being passed into the stored procedure then work through the SQL code and locate where the error is being caused.

Chris

|||

this is not possible because client and db musn't be linked (db can be modified). i don't know futur size of these fields.

it's a feature for my users, indicating whitch field is too long

|||

As far as I am aware, there's no way to determine which column's length has been exceeded.

You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary - see the example below.

Again I stress that it would be better to modify the client application's code to either warn the user or to limit the number of characters they can enter into a field.

Chris

Code Snippet

--This batch will fail with the SQL Server error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

GO

--This batch will fail with a custom error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

IF LEN(@.MyParameter) > 10

BEGIN

RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

RETURN

END

ELSE

BEGIN

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

END

GO

"string data right truncation"

Hello everyone,

I'm using CRecordset in order to write into the SQL Server, i have a problem that

everytime i'm trying to write a varchar fields with size X and i put in the CString larger

string then X i get CDBException.

My question is if there is anyway to to tell the CRecordset to truncate automatialy the strings

instead of doing it manually for every record i write.

Thanks

check this

create table #tt(a varchar(5))

insert #tt select '1234567' --fails

insert #tt select left('1234567',5) -- succeeds

insert #tt select substring('1234567',1,5) -- succeeds

Madhu

|||

10x for the answer, but as far as i know this solution can't help me whilst using CRecordset

This is an example code(c++) of how i use the recordset:

---

class CJREventsArchiveRec : public CRecordset {

....

CString m_Bla;

...

}

The table in this example include a single nvarchar field(Bla) with size 5;

m_eventRec.AddNew();

m_Bla = "123456";

m_eventRec.Update(); // This row cause CDBException (string data right truncation).

---

of course in this sample its easy to manually avoid this problem,but its no that easy at my app.

The Solution i'm looking for is somehow to tell the Recordset to automatically truncate the string as much as he need, and stop throwing on me Exceptions

|||

That would mean silent data loss and I don't think the ODBC driver would silently allow data loss. Note that on the way out, it might truncate silently but when data is going to the server, it won't.

Thanks

Waseem