Database insert string size limit

[PaulHInstincticve]

I am updating a database with details entered into a text area on an ASP web form using the following syntax

Set topicsrs = Server.CreateObject( "ADODB.Recordset" )
topicsrs.ActiveConnection = Con
sqlString = "insert into mbtopics (ctopicref, mmessage) values ('" & lctopicref & "','" & lmmessage & "')"
topicsrs.Open sqlString

Depending on the form contents this might create something like

insert into mbtopics (ctopicref, mmessage) values ('000001','This is my message text')

This works great where the contents of lmmessage are less than 255 characters in length but when I go over that I get the following message even though the field in my database is a Visual FoxPro Memo field capable of storing up to 64,000 characters.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword.

Does anyone know how I can overcome this?

Thanks

Paul

 

[DotNetGnat]

what version of Visual foxpro are you using?

-DNG

 

[emozley]

This is a problem with ODBC

http://support.microsoft.com/kb/q208208/

 

[PaulHInstincticve]

Thanks for that. The kb article sorted me out although it did seem a bit messy at first. For anyone revisiting this thread I got my code working as follows

Set topicsrs = Server.CreateObject( "ADOR.Recordset" )
topicsrs.ActiveConnection = Con
topicsRs.cursortype = 1 'adOpenKeyset
topicsRs.cursorlocation = 2 'adUseServer
topicsRs.locktype = 3 'adLockOptimistic
topicsrs.open "select * from mbtopics where ctopicref = '000000'",con

topicsRs.AddNew

topicsrs.Fields("ctopicref")="000001"
FOR i = 1 to len(lmmessage) step 254
liStartAt = i
liWorkString = mid(lmmessage, liStartAt, 254)
topicsRs.Fields("mmessage").AppendChunk(liWorkString)
NEXT
topicsRs.UPDATE 'Update the recordset

My understanding of this is that I am appending to a buffered record set 254 characters at a time until all the memo field data has been added and then flushing the buffered recordset. It appears you have to create a record set using a select command before you can add a new record so I used a select that would return an empty recordset to make the command as efficient as possible by searching for a reference that does not exist rather than doing a select * as in the Microsoft example.

Thanks again for the help

Paul

 

[PaulHInstincticve]

I have now moved across to what appears to be newer technology OLE DB rather than ODBC. It seems a direct replacement and most of my sql select, insert sql etc are compatible with minor differences. The first reply here said this limit was a problem with ODBC, is it not a problem with OLE DB and can I go back to more straightforward coding of insert sql again? Thanks

 

[PaulHInstincticve]

I am still experiencing the message

[Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword.

under OLE DB when trying to code this as a simple INSERT INTO command. I am therefore assuming that this continues to be a problem with OLE DB as well as ODBC in the light of no further comments here to the contrary and it must therefore be coded as above with the buffering and flushing mechanism if there are more than 255 characters being added to the memo field.

 

[gmmastros]

Do you get this error ONLY when the data contains an apostrophe?

Set topicsrs = Server.CreateObject( "ADODB.Recordset" )
topicsrs.ActiveConnection = Con
sqlString = "insert into mbtopics (ctopicref, mmessage) values ('" & lctopicref & "','" & [!]Replace([/!]lmmessage[/!], "'", "''")[/!] & "')"
topicsrs.Open sqlString

Also... Since you are not returning data from this query, you don't need to use a recordset object, so...

sqlString = "insert into mbtopics (ctopicref, mmessage) values ('" & lctopicref & "','" & [!]Replace([/!]lmmessage[/!], "'", "''")[/!] & "')"
con.Execute sqlString

Please note that by replacing a single apostrophe with 2 of them, only 1 apostrophe will be inserted in to the database, not 2.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

My tags got messed up. Sorry.

sqlString = "insert into mbtopics (ctopicref, mmessage) values ('" & lctopicref & "','" & [!]Replace([/!]lmmessage[!], "'", "''")[/!] & "')"
con.Execute sqlString

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PaulHInstincticve]

George,

Yes thanks for that. I was dealing with that elsewhere in my code but tried to keep it simple here. The problem occurs whether there is a single or double quotes and only comes into play once you reach 254 characters so it is a limitation of the memo field size not the contents. Breaking it up into 254 chunks and appending to a buffered table and then flushing the table worked under ODBC but was long winded and messy. I had hoped it was sorted in OLE DB. It appears it is not when simply using the connection object, however, it can be done by a simpler parameterised command object. For full details see my other open thread at thread1253-1308568. Thanks

Paul