Handle error on edit 1

[hamish75]

I have a field named [Information text] which is a type varchar(8000).

I edit the field in a SQL database here:

str_SQL = ""
str_SQL = str_SQL & " IF EXISTS (SELECT 1 FROM [System_Information] WHERE SystemID = '" & id & "') "
str_SQL = str_SQL & "   UPDATE a "
str_SQL = str_SQL & "   SET    a.[Information Text] = '" & EditInformationText & "'"
str_SQL = str_SQL & "   FROM   [System_Information] a "
str_SQL = str_SQL & "   WHERE  [SystemID] = '" & id & "'"
str_SQL = str_SQL & " ELSE "
str_SQL = str_SQL & "   INSERT INTO [System_Information] ([SystemID], [Information text] )"
str_SQL = str_SQL & "   VALUES "
str_SQL = str_SQL & "   ('" & id  & "', '" & EditInformationText & "')"

Set obj_RS1 = obj_CN.Execute(str_SQL, adBoolean)

If the character code is more than 8000 I get the following error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E57)
String or binary data would be truncated.

I don’t want to store more than 8000 characters.

Can anyone help me handle this error?

Thanks.


Ah dinnae ken...

 

[gmmastros]

str_SQL = ""
str_SQL = str_SQL & " IF EXISTS (SELECT 1 FROM [System_Information] WHERE SystemID = '" & id & "') "
str_SQL = str_SQL & "   UPDATE a "
str_SQL = str_SQL & "   SET    a.[Information Text] = '" & [!]Left([/!]EditInformationText[!],8000)[/!] & "'"
str_SQL = str_SQL & "   FROM   [System_Information] a "
str_SQL = str_SQL & "   WHERE  [SystemID] = '" & id & "'"
str_SQL = str_SQL & " ELSE "
str_SQL = str_SQL & "   INSERT INTO [System_Information] ([SystemID], [Information text] )"
str_SQL = str_SQL & "   VALUES "
str_SQL = str_SQL & "   ('" & id  & "', '" & [!]Left([/!]EditInformationText[!], 8000)[/!] & "')"

Set obj_RS1 = obj_CN.Execute(str_SQL, adBoolean)

This will truncate the data on the ASP side before sending the query to the database. If the length of the text is less than 8000, taking the Left(Data,8000) will have no effect.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hamish75]

Big thanks for that.

Works just great!

Ah dinnae ken...

 

[hamish75]

The edit that gmmastros made works great.

Just wondering how I could make this inform the user that the field has been clipped if the user enters more than 8000 characters.

At the moment below the code posted above I have the following line:

Response.Redirect("systems.asp?e=System Edited&id=" & id & "")

Thanks.

Ah dinnae ken...

 

[gmmastros]

There is a Len function you can use to determine the length of a string.

Ex:

Response.Write Len(EditInformationText)

By the way, what version of SQL Server are you using? If you are using SQL Server 2005 or 2008, then you could just change the data type of your column to varchar(max) instead of varchar(8000). This will allow you to store approximately 2 billion characters in the column instead of 8,000.

If you are still using SQL2000 and you want to limit the amount of text, you are probably better off putting that limit on the control you use to get the data from the user.

For example, if you are using an input type="text" box, you can limit the amount of data like this:

<input name="blah" type="text" maxlength="8000" />

Worst case, use the Len function to check the length of the string and alert the user when it exceeds 8000. In my opinion, it's better to...

1. Remove the restriction if you can.
2. Prevent the user from entering too much data.
3. Warn the user prior to saving so they can modify the text to be shorter.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom