vb.net and access

[Stephen]

Greetings all

I have a form that reads a access memo field. I can query the database to
get the info in the memo field without a problem...but when i try to update
the data i get a exception. The text in the memo field can be large. It
seens to throw the exception only when large amounts of data is being
updated. The following is the update code : rtNotes is a richtext textbox..
any help will be appreciated
Dim mNotes As String = rtNotes.Text & String.Empty ''

sqlStatement = "update master set Cust_Notes = ' " & mNotes & " ' where
str(trim(MasterId)) = " & _

"' " & Trim(glbLookup) & " '"

SetUpDataBase()

Try

cmd.ExecuteNonQuery()

Catch Ex As Exception

MsgBox(Ex.ToString)

End Try

btnSave.Enabled = False

With Label6

..Text = "Locked"

..ForeColor = Color.Black

End With

 

[Bernie Yaeger]

Hi Stephen,

A couple of ideas: try updating the column inside ms access with a string
the same size as one that fails. Also, try saving the data through vb .net
itself, instead of using an update sql statement - one should not work
better than the other, but I'm curious if that would work.

Finally, post the exception and post the exact text that should have updated
the memo field when the exception occurred. I could then test it on my
system to see what I experience.

HTH,

Bernie Yaeger

 

[Stephen]

Hi Bernie

After slamming my head against the desk, i noticed the only time i get a
exception is when a apostrophy is in the text like (didn't) ... there has to
be a way around it

 

[Stephen]

i seemed to be sucessfull with the following:
mNotes = Replace(mNotes, "'", "''")

but im still concerned about the max length of the string vs a memo

 

[Bernie Yaeger]

Hi Stephen,

You can escape it with
this is it's element
this is it''s element

HTH,

Bernie Yaeger

 

[Bernie Yaeger]

Hi Stephen,

Yes, you are essentially escaping it with '' - and the replace method is
precisely the way I go about it also.

Bernie

 

[Stephen]

Thank you ....your help is appreciated

-S-

Hi Stephen,

Yes, you are essentially escaping it with '' - and the replace method is
precisely the way I go about it also.

Bernie