07-15-2004, 05:17 PM
I have to read text from a text area and write it to an excel spreadsheet so I used an ADO recordset, opened the connection and read the Request.form fields to the recordset. One of the text areas can get rather large and an error occurs after 255 characters.
Is there a way to set the field type to something akin to memo? If I just read the text area into a variable the code passes fine. It is definetly the RS field.:mad:
oRS("description") = request.form("Description")
07-15-2004, 05:26 PM
How is oRS("description") defined in your table?
07-15-2004, 06:38 PM
It is not a table. It is an excel spreadsheet. The cells are formatted as general and I can copy and paste the entire contents of the text area into the cell. Since I can open the defined area of the spreadsheet like a database table the column heads become the field names.
07-15-2004, 06:44 PM
Ok - then you are creating a recordset on the fly. When you append the fields to the recordset, you can specify dataype. See :
and also the addnew method of the recordset on that same site.
07-15-2004, 07:30 PM
I attempted to use the append method and define a new field that was sized for the data I wanted to write. I ended up getting an error; "Operation not allowed in this context."
Here is the code for that page - maybe I just have something wrong here.
ExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & chr(34) & vXlsFile & chr(34) & ";" & _
"Extended Properties=" & chr(34) & "Excel 8.0;"& chr(34)
'Open Excel Workbook
Set Conn = Server.CreateObject("ADODB.Connection")
sql = "SELECT * From tblworkrequestlog;"
set OpRS = server.createobject("adodb.recordset")
OpRS("Name") = Request.form("Name")
OpRS("UserID") = Request.form("UserID")
OpRS("Telephone") = Request.form("Telephone")
OpRS("Location") = Request.form("Location")
OpRS("Plant") = Request.form("Plant")
OpRS("TechnologyArea") = Request.form("TechnologyArea")
OpRS("Date") = Request.form("DateOfRequest")
OpRS("NeedDate") = Request.form("NeedDate")
OpRS("Reason") = Request.form("ReasonForRequest")
OpRS("Impact") = Request.form("Impact")
OpRS("Title") = Request.form("Title")
OpRS.Fields.Append "Test", adVariant, 256, adfldupdatable
OpRS("Test") = Request.form("Description")
The error occurs on the append line. Before you ask - we are FORCED to use Excel in this case.
I appreciate you looking at this.
07-16-2004, 12:06 AM
Then it would seem that there is an inherent 255-char limit in Excel cells... I suggest that you consult the appropriate reference documentation (but where/what it is, I unfortunately can't help you with...)
07-16-2004, 12:48 PM
I am able to paste the contents directly into the excel cell. I guess I'll work through and see what can be done to get around this problem. If we didn't have to use Excel this wouldn't be happening.
07-16-2004, 01:45 PM
This is kind of hokey. Since I could paste the contents (no matter how large) into a cell directly, I did just that in a row at the top of the worksheet and hid the row. Then when I ran the form it went perfectly. The large text area was saved as intended. The field size for that field was astronomical. I guess the default field size for a column in Excel is 255. When I opened the sheet with ASP it took the default and I couldn't change it. I was under the impression the ADO had the default field size since I was able to paste large numbers of characters into a cell. But by pasting the large number of characters into the cell I forced Excel cell size to a new level so that now when I opened it with ASP it adopted the field size.
Thanks for your help.