|
|
Posted by John on 12/26/06 11:46
Hi Everyone,
I have a question about dynamically changing the length of a varchar(n)
field, in case the value I'm trying to insert is too big and will give
a "truncated" error, but before the error is given! i.e. Is there some
kind of a way to "test" the length of the field while Inserting the
value into it, and to have it automatically increase its length to the
length of the value being inserted, in case the value is too big?
I've been able to do this in a "primitive" way, simply by identifying
the specific error number in case the value is being truncated, and
then increasing the length of the varchar(n) field by using the ALTER
command, and then duplicating the insert statement, but is there a
standard (shorter) way of doing this?
Here is my code (I'm working in an ASP environment):
<%
var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"
sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"
On Error Resume Next
conn.Execute sql
If err = -2147217833 Then
Response.Write "Error Recognized Successfully!<br /><br />"
sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &
Len(var_txt) &
") NOT NULL"
On Error Resume Next
conn.Execute sql
If err<>0 Then
Response.Write "Error while trying to alter Column:<br
/>" & err & "
= " & err.description & "<br />"
Else
Response.Write "Column altered successfully to: " &
Len(var_txt) &
"<br />"
sql = "Insert Into Table1 (text) Values ('" & var_txt &
"')"
On Error Resume Next
conn.Execute sql
If err<>0 Then
Response.Write "<br />Error number 2:<br />" &
err.description &
"<br />"
Else
Response.Write "Now it was added successfully!
HaHa!<br />"
End If
End If
Else
Response.Write "Success."
End If
%>
Thanks in advance!
Navigation:
[Reply to this message]
|