MS SQL Server - Error 80040e57 - String or binary data would be truncated

Author

Error 80040e57 - String or binary data would be truncated

Nathon Jones

2006-03-05, 8:24 pm

We're receiving the following error on an ASP/VB checkout page which uses
WebAssist e-commerce software to save product and customer information to
our MSSQL database.

This error occurs on submission of the INSERT form:

Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would
be truncated.
/checkout.asp, line 112

Can anyone help us understand this SQL Server error?

Thanks
nath.

Hilary Cotter

2006-03-05, 8:24 pm

It means the data you are trying to insert is to big for the column, i.e.
you are trying to insert 20 byte data into a 10 byte column.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"Nathon Jones" < sales@NOSHPAMtradmus
ic.com> wrote in message
news:u1dAip3PGHA.3872@TK2MSFTNGP15.phx.gbl...
> We're receiving the following error on an ASP/VB checkout page which uses
> WebAssist e-commerce software to save product and customer information to
> our MSSQL database.
>
>
> This error occurs on submission of the INSERT form:
>
>
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would
> be truncated.
> /checkout.asp, line 112
>
>
>
> Can anyone help us understand this SQL Server error?
>
>
>
> Thanks
> nath.
>
>

Nathon Jones

2006-03-05, 8:24 pm

Thank you Hilary.

I've changed the structure of the database and, whilst that error has now
gone, I am now receiving the following:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value
for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
OFF.
/checkout.asp, line 112

Is this a database setting error? How do I set the IDENTITY_INSERT to ON
for the ID column in dbo.tblCustomers?

Thanks.
nath.

"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:OLE9X23PGHA.2080@TK2MSFTNGP09.phx.gbl...
> It means the data you are trying to insert is to big for the column, i.e.
> you are trying to insert 20 byte data into a 10 byte column.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
>
>
> "Nathon Jones" < sales@NOSHPAMtradmus
ic.com> wrote in message
> news:u1dAip3PGHA.3872@TK2MSFTNGP15.phx.gbl...
>
>

Tibor Karaszi

2006-03-05, 8:24 pm

This is bad. Seems you changed the structure somehow, wanting to increase the length for a column.
But that was not all that happened. Something else happened. SQL Server wouldn't start producing
this error message otherwise. Check your production server (I take it that this is a development
server you are working on) and see how the table was defined before you did this change.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Nathon Jones" < sales@NOSHPAMtradmus
ic.com> wrote in message
news:OHl1zX4PGHA.2816@TK2MSFTNGP15.phx.gbl...
> Thank you Hilary.
>
> I've changed the structure of the database and, whilst that error has now gone, I am now receiving
> the following:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value
> for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to OFF.
> /checkout.asp, line 112
>
> Is this a database setting error? How do I set the IDENTITY_INSERT to ON for the ID column in
> dbo.tblCustomers?
>
> Thanks.
> nath.
>
> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
> news:OLE9X23PGHA.2080@TK2MSFTNGP09.phx.gbl...
>
>

Nathon Jones

2006-03-05, 8:24 pm

Hi Tibor,

Sorry, I should explain in a little more detail.
One of the fields in my table dbo.tblCustomer was too short for the data
being inserted by an ASP/VB INSERT form. I changed the field length, and it
seems to have removed that error.

I am sure that the new error, regarding IDENTITY_INSERT is relating to the
fact that I am trying to INSERT a sessionID number into a primary key field
in the MSSQL database, except that it won't allow it because IDENTITY_INSERT
is set to OFF....no?

Hope you can help. Thanks.
Regards
Nath.

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:O3n7QC5PGHA.3672@TK2MSFTNGP14.phx.gbl...
> This is bad. Seems you changed the structure somehow, wanting to increase
> the length for a column. But that was not all that happened. Something
> else happened. SQL Server wouldn't start producing this error message
> otherwise. Check your production server (I take it that this is a
> development server you are working on) and see how the table was defined
> before you did this change.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Nathon Jones" < sales@NOSHPAMtradmus
ic.com> wrote in message
> news:OHl1zX4PGHA.2816@TK2MSFTNGP15.phx.gbl...
>

Tibor Karaszi

2006-03-05, 8:24 pm

The issue here is that it worked before you did the change and now it doesn't work. Is that correct?
If so, you did something more than just changing the length for a column. The error at hand is that
the table has a column with the identity property, and you cannot specify a value for that column in
an insert statement (unless you use SET IDENTITY_INSERT ON9.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Nathon Jones" < sales@NOSHPAMtradmus
ic.com> wrote in message
news:eBaIYX5PGHA.1556@TK2MSFTNGP09.phx.gbl...
> Hi Tibor,
>
> Sorry, I should explain in a little more detail.
> One of the fields in my table dbo.tblCustomer was too short for the data being inserted by an
> ASP/VB INSERT form. I changed the field length, and it seems to have removed that error.
>
> I am sure that the new error, regarding IDENTITY_INSERT is relating to the fact that I am trying
> to INSERT a sessionID number into a primary key field in the MSSQL database, except that it won't
> allow it because IDENTITY_INSERT is set to OFF....no?
>
> Hope you can help. Thanks.
> Regards
> Nath.
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:O3n7QC5PGHA.3672@TK2MSFTNGP14.phx.gbl...
>
>

Nathon Jones

2006-03-05, 8:24 pm

Hi Tibor,

I think I DO need to use the SET IDENTITY_INSERT ON for the ID field in my
dbo.tblCustomer, except I don't know how to in ASP/VB?

I have created an ASP/VB page (checkout.asp), which creates it's own
SessionID to be inserted into the ID field in my Customer table. I think
that I am receiving this error because the IDENTITY_INSERT is set to off so
it will not allow it because it is the primary key field. I do need it to
act as the primary key field though.

Do I insert the SET IDENTITY_INSERT ON thing (sic) into my connection
script, or directly on the asp page? What would it look like?

At the moment, I have this as my connection string:

<%
' FileName=" Connection_ado_conn_
string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
MM_connName_STRING = " PROVIDER=MSDASQL;DRI
VER={SQL
Server};SERVER=123.123.123. 123;UID=username;PWD
=password;DATABASE=d
atabase;"
%>

Sorry, I know these aren't really MSSQL questions, however I'm the error is
down to the setting for that particular table in my MSSQL database.

To answer your first question, I'm not sure whether it was working prior to
the change. I was just receiving a different error about truncation of a
field. My feelings are that perhaps the truncation error appears first, and
the IDENTITY_INSERT was still incorrect, but just not the 1st error?

Hope you can help,
Thanks.
Nath.

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:u2c4Id5PGHA.4900@TK2MSFTNGP09.phx.gbl...
> The issue here is that it worked before you did the change and now it
> doesn't work. Is that correct? If so, you did something more than just
> changing the length for a column. The error at hand is that the table has
> a column with the identity property, and you cannot specify a value for
> that column in an insert statement (unless you use SET IDENTITY_INSERT
> ON9.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Nathon Jones" < sales@NOSHPAMtradmus
ic.com> wrote in message
> news:eBaIYX5PGHA.1556@TK2MSFTNGP09.phx.gbl...
>

Tibor Karaszi

2006-03-05, 8:24 pm

> I think that I am receiving this error because the IDENTITY_INSERT is set to off so it will not
> allow it because it is the primary key field.

No, that is not the reason. You get this error because you have defined one of the columns in the
table (possibly the PK column) with the IDENTITY property. Remove the identity property and you will
be able to define a value for that column.

The scary part is why this occurred after you did some other unrelated change to the table
structure...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Nathon Jones" < sales@NOSHPAMtradmus
ic.com> wrote in message
news:%23%23V7Lo5PGHA
.5296@TK2MSFTNGP09.phx.gbl...
> Hi Tibor,
>
> I think I DO need to use the SET IDENTITY_INSERT ON for the ID field in my dbo.tblCustomer, except
> I don't know how to in ASP/VB?
>
> I have created an ASP/VB page (checkout.asp), which creates it's own SessionID to be inserted into
> the ID field in my Customer table. I think that I am receiving this error because the
> IDENTITY_INSERT is set to off so it will not allow it because it is the primary key field. I do
> need it to act as the primary key field though.
>
> Do I insert the SET IDENTITY_INSERT ON thing (sic) into my connection script, or directly on the
> asp page? What would it look like?
>
> At the moment, I have this as my connection string:
>
> <%
> ' FileName=" Connection_ado_conn_
string.htm"
> ' Type="ADO"
> ' DesigntimeType="ADO"
> ' HTTP="false"
> ' Catalog=""
> ' Schema=""
> MM_connName_STRING = " PROVIDER=MSDASQL;DRI
VER={SQL
> Server};SERVER=123.123.123. 123;UID=username;PWD
=password;DATABASE=d
atabase;"
> %>
>
> Sorry, I know these aren't really MSSQL questions, however I'm the error is down to the setting
> for that particular table in my MSSQL database.
>
> To answer your first question, I'm not sure whether it was working prior to the change. I was
> just receiving a different error about truncation of a field. My feelings are that perhaps the
> truncation error appears first, and the IDENTITY_INSERT was still incorrect, but just not the 1st
> error?
>
> Hope you can help,
> Thanks.
> Nath.
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:u2c4Id5PGHA.4900@TK2MSFTNGP09.phx.gbl...
>
>

Sponsored Links