Memo field longer than 255 Char

[ChuckG]

I've got a form for a internal site that is supposed to be for the user to put notes about the process they are working on. But it appears that SQL has a problem when an INSERT value is longer than 255 characters.

Unluckily, 255 isn't enough most of the time.

I've re-written my ASP page to take the value of that field, and if it's more than 255 chop it into multiple variables of 255 characters.

So I have chop1 - chop4, lets say. Each one contains 255 (last one may be less) characters.

I can perform the INSERT using chop1 for that memo field, but can't append the chop2-4 to it. An UPDATE just over writes it and I havn't seen a method yet for just appending a variable to the end of the data in a field.

Still looking through some SQL/ASP books, but if anyone has any suggestions, that would be great.

FYI inserting into a Foxpro Table.

ChuckG

 

[lebisol]

http://msdn.microsoft.com/library/d.../en-us/dv_foxhelp/html/conmemo_field_type.asp

> need more info?
:: don't click HERE ::

 

[ChuckG]

lebisol,

Not sure what you where referencing in that link. I know the foxpro memo field can handle the data due to the fact that I can go into foxpro and perform an insert from there and it allows as much data as I want.

If I have a memo field longer than 255, a SELECT command from a ASP page can retrieve the entire string and display it on screen. It's just the INSERT that apparently has a problem.

 

[lebisol]

assuming that your know about differences between Text and Memo...in that case....
WHY are you doing the following?

"I've re-written my ASP page to take the value of that field, and if it's more than 255 chop it into multiple variables of 255 characters"
if user types 2 chars then u save those 2...if they type more then the same...why chop it up?
what is the error that you are getting from ASP?

Insert works just fine (sorry I do not have Foxpro handy just Access)....much like these post we are making into text area...make sure that your form input is not limited by some JS or other validation...
I am assuming your are dumping your info into some text area

<form name="form1" method="post" action="my_insert_page.asp">
  <textarea name="textarea" cols="60" rows="10" wrap="VIRTUAL"></textarea>
  <br>
  <input type="submit" name="Submit" value="Submit">
</form>

sorry but perhaps you can post your ASP code if the DB field is defined correctly I see no reason to why you would have problems....
All the best!

> need more info?
:: don't click HERE ::

 

[ChuckG]

This is code from a test page I'm working on.

The table TEST has the fields

Field Type
one character
two character
three character
code numeric
test_date character
notes memo


one = CStr (Request.Form ("one"))
one = UCase(one)
two = CStr (Request.Form("two"))
two = UCase(two)
three = CStr (Request.Form("three"))
three = UCase(three)
code = CStr (Request.Form("code"))
Test_date = CStr (date)
notes = CStr (Request.form ("notes"))
notes = UCase(notes)

strSQL = "INSERT into test (One, Two, Three, Code, Test_Date, Notes) values ('" & One & "', '"& Two & "', '"& Three &"', "& code &", '"& test_date &"', '"+ notes +"')"

Response.Write(strSQL)
ObjRS.Open strSQL, objConn


I've verified that the notes field is set as MEMO, the form we're typing the data into is set as a scrolling text box (only limitation on it is theForm.notes.value.length > 1000 so that it can't be more than 1000 characters)

The response.write(strSQL) I issue before trying to use the INSERT gives this back

INSERT into test (One, Two, Three, Code, Test_Date, Notes) values ('FIRST', 'SECOND', 'THIRD', 444444, '4/8/04', '555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555')

And then I get this code when it tries to INSERT

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

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

The above sample INSERT has 300 characters in the notes field.

If I reduce the notes to 255 or less, it inserts fine.

If I open TEST up in Foxpro, and drop more than 255 characters into notes, it takes it.
And if I do a SELECT on a record that has more than 255 chars it displays it on the ASP page.

 

[lebisol]

strange....I would try deleting the filed 'notes' and recreating it...check to see if the field's MAX value isn't set to 255 (although it is defined as memo).....

and if it all fails then consider your "chop" approach.
I think that, from ASP/VBscript synthax perspective,variables can not hold more than 255 chars.
So u would need some VBscript or JS scripts to count chars in your textarea and then chop it up- do your insert of 255 chars then Update the field with the rest of the chars...so u were on the right track ( assuming that we treat DB as "good")
also u might want to read though:

http://www.experts-exchange.com/Databases/FoxPro/Q_20487951.html

So, to recap I guess the "key" would be in the script that determines the lenght of chars entered into ASP form.
JS version of it:

http://www.shiningstar.net/articles/articles/javascript/dynamictextareacounter.asp?ID=AW

I hope this is enough of info to get your through...
All the best!

> need more info?
:: don't click HERE ::

 

[ChuckG]

I'll try recreating the field, but I'm pretty sure it's not going to correct it, as this table I'm using is just a test one. I was writing the code for the project on the live page and was getting the exact same results from a completely different table (same type of memo field though)

This is what I wrote to "chop" it at the moment.

note_len = len(notes)

if note_len > 255 then
chop1 = mid(notes,1,255)
chop2 = mid(notes,256,255)
chop3 = mid(notes,511,255)
chop4 = mid(notes,766,255)

Which handles up to 1020 characters.

The big issue is, I can INSERT the record with chop1, but if I do an UPDATE to the note field with chop2, it overwrites notes with just the value of chop2.

I'll take a look at that thread you posted and see what can be had

Thanks for the help thus far.

ChuckG

 

[lebisol]

can u Upade more than 255 chars to an empty field?
IF u can then perhaps u could:
Page1.asp
-INSERT Command =all fields except the 'Notes' field and then
-Filter the the current/last record inserted and then UPDATE Command for only the field 'Notes' that was empty so far


OR
use a "joined vars" approach of
var1= 1/4 of the notes (direct VARIBLES CAN NOT hold more than 255 chars)
var2= 2/4 of the notes
MEGA_VAR=var1 & var2 (varibale holding the 2 variables NOT the chars)
I think this was in the line of the discussion on the eXperts-exchange.....
keep at it, I am sure u will find the solution...ur close!
All the best!


> need more info?
:: don't click HERE ::

 

[lovejaeeun]

Try replacing: ObjRS.Open strSQL, objConn
With: objConn.Execute strSQL

I do all my INSERTS with the Execute method and I don't get errors.

 

[lovejaeeun]

PS - what does the '+' do when around notes? +notes+

 

[jen89]

When you try your insert directly to the database instead of through ASP, are you retyping, or copying directly from the response.write output? I would make sure you are copying it, because if you are retyping, you may be unconsiously fixing a syntax error. I'm not sure about FoxPro specific syntax, but it's worth a try. Also, I'd try renaming the column in case you have inadvertantly hit upon a reserved word. Sometimes the behavior when that happens is down right strange - things work sometimes and not others, and things can be very unpredictable.

 

[ChuckG]

lovejaeeun,

The + around notes is the same as using &, it's simply a holdover from when I was doing Javascript coding. I've been trying to use & in VBS but once in a while accidently revert. The + does work in VBS though.