inserting data into "CLOB" field returning ORA-01704: string literal too long

D

DJ DIGI

Hello Everyone,

I'm attempting to design a small web app to obtain customer feedback.
The issue is when a user inputs >4000 characters in the COMMENTS
section I get the following Err Msg: ORA-01704: string literal too
long.

I am using Visual Studio.Net 2003 (vb as language), SQL+ to create
tables in Oracle 9i, and Access to just link and veiw the tables.

Used SQL+ to create the table

sql> create table tab1
(NAME varchar2(100), COMMENTS CLOB);

My webform has 2 textboxes, 1 button and 1 label

Code looks like so:

Imports System.Data
Imports System.Data.OleDB
_________________________

Private ConnectString as String
....
ConnectString = "Yada, Yada, Yada..."
....
Dim con As New OleDBConnection(ConnectString)

________________________________

The following is my input statement:

Private Sub btnSubmit_Click.........

TRY

Dim insertSQL, Name, Comments As String
Dim Added as Integer

NAME = " ' " & txtName.text & " ' "
COMMENTS = " ' " & txtComments.text & " ' "

insertSQL = "INSERT INTO TAB1(NAME, COMMENTS)" & _
"VALUES (" & NAME & ", " & COMMENTS & ")"

DIM cmd As New OleDBCommand(insertSQL, con)
con,open()
added = cmd.ExecuteNonQuery
con.close()

'gives me some kind of confirmation record was submitted
If added > 0 Then
lblErr.text = "Record Submitted"
End If

Catch ex As Exception
con.close()
lblErr.text = ex.Message
End Try
End Sub
__________________________________________

This is as detailed as I can get. Do I need to declare COMMENTS as
something other than string? Is there some other way I need to write
the insert statement? Am I way off and should hang it up as a beginner
..Net developer? I have read through tons of docs, websites, and groups
and can't come up with any definitive answers. This is my first time
posting. Willing to help anyone and any help would be MUCH appreciated.
 
D

DJ DIGI

You can't use oledb, I added the Oracle reference and used Imports
Oracle.DataAccess.Client and Oracle.DataAccess.Types, I changed some
code around and on day 2 got it working. I would like to post a couple
of links that assisted me in completing my task and hope it well help
others with my same situation. This applies to all LOBs.

'this will go over the setup of tables and code needed to read the
clob, this must be done first, before you can write to a LOB field.
Yes, you have to read before you can write. The link will further
explain.
http://www.oracle.com/technology/pub/articles/price_dbtrans_dotnet.html


'this one will explain how to write to a LOB field
http://www.oracle.com/technology/pub/articles/price_dotnet2.html

Thank you all.
 
Top