cannot create Excel file with field VARCHAR(8000)

[leosuth]

Couldn't find anywhere more appropriate to Excel, but it is kind of to do with Databases

have read in a lot of places on the web that the max size for the varchar type is 8000 characters

CREATE TABLE table1 (Field1 VARCHAR(8000))

is a perfectly valid statement (assuming I got the above right)

I have an application where I need to create an Excel Files using OleDB - the code follows:

xconno = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & opath & ";Extended Properties=""Excel 8.0;HDR=YES;""")
xconno.Open()
sql = "CREATE TABLE Sheet2 (" & _
"record_type INT, " & _
"type INT, " & _
"consignment_no VARCHAR(16), " & _
"OrderNumber VARCHAR(16), " & _
"order_number_2 VARCHAR(50), " & _
"account_no VARCHAR(16), " & _
"no_of_items INT, " & _
"weight REAL, " & _
"address_line_1 VARCHAR(50), " & _
"address_line_2 VARCHAR(50), " & _
"address_line_3 VARCHAR(50), " & _
"address_line_4 VARCHAR(50), " & _
"post_code VARCHAR(8), " & _
"title_of_customer VARCHAR(8), " & _
"forename_of_customer VARCHAR(50), " & _
"initials VARCHAR(8), " & _
"surname VARCHAR(50), " & _
"telephone_number_of_customer VARCHAR(20), " & _
"2nd_tele_number VARCHAR(20), " & _
"3rd_tele_no VARCHAR(20), " & _
"email_address VARCHAR(50), " & _
"order_date VARCHAR(16), " & _
"delivery_instructions VARCHAR(255), " & _
"product_list VARCHAR(8000))" <<<<<<<<<<<<<<<
xcmdo = New OleDbCommand(sql, xconno)
xcmdo.ExecuteNonQuery()
xconno.Close()

The variable "opath" holds the filepath of the XLS file to be created. The code exist and is run in Visual Studio 2005.

However, when I run the program within VS, I get an error on the line marked <<<<<<<<<<<<<

------------------------------------
OleDbException was unhandled

Size of field 'product_list' is too long.
------------------------------------

In fact, if field product_list is declared any VARCHAR greater than VARCHAR(255), the above error occurs - this seems to fly in the face of waht everything else seems to say. The field 'product_list' must be greater than 255 characters, so the question is, how do I accomplish this?

[Hack]

Welcome to DevX

Run by itself in Enterprise Manager, this line of code ran just fine

CREATE TABLE table1 (Field1 VARCHAR(8000))

In addition, also in Enterprise Manager, this ran just fine except for a Warning

CREATE TABLE Sheet2 (
record_type INT, 
type INT, 
consignment_no VARCHAR(16), 
OrderNumber VARCHAR(16),
order_number_2 VARCHAR(50), 
account_no VARCHAR(16), 
no_of_items INT, 
weight int, 
address_line_1 VARCHAR(50), 
address_line_2 VARCHAR(50), 
address_line_3 VARCHAR(50), 
address_line_4 VARCHAR(50), 
post_code VARCHAR(8), 
title_of_customer VARCHAR(8), 
forename_of_customer VARCHAR(50), 
initials VARCHAR(8), 
surname VARCHAR(50), 
telephone_number_of_customer VARCHAR(20), 
Second_tele_number VARCHAR(20), 
Third_tele_no VARCHAR(20), 
email_address VARCHAR(50), 
order_date VARCHAR(16), 
delivery_instructions VARCHAR(255), 
product_list VARCHAR(8000))

The Warning given was this

Warning: The table 'Sheet2' has been created but its maximum row size (8882) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

[pclement]

If you're using Jet OLEDB then VARCHAR is limited to 255 characters. I seem to remember that Excel had a limitation with respect to column size. This may no longer be the case and if so I would try using MEMO or one of the corresponding data types (LONGTEXT, LONGCHAR, NOTE, NTEXT).

[Hack]

To me varchar says Oracle or SQL Server....it has nothing to do with Excel directly.

[pclement]

Jet SQL DDL (which is what you would use to create an Excel Worksheet in this instance) supports VARCHAR as well. It's a rather common keyword used amongst DBMS, if you want to refer to Excel or Access as a DBMS. ;-)

[Hack]

At the risk of sounding, at best picky, and at worst, snobbish, I do accept Access as a DBMS, but Excel is not a database...it is a spreadsheet...and, IMO, should not be used for anything other than that.

[leosuth]

At the risk of sounding, at best picky, and at worst, snobbish, I do accept Access as a DBMS, but Excel is not a database...it is a spreadsheet...and, IMO, should not be used for anything other than that.

I accept Access as only just a DB never mind a DBMS . I'm not using Excel as a database, the Excel file is just an output file for a report-sort of thing. Don't ask - customer requirement....

Anyhoo, I used MEMO(8000) and Longvarchar(8000) where appropriate - as suggested above by pclement, thank you - and that works just fine.

Now, as to whether I actually need 8000 characters.....

Thanks to all for their input

[Hack]

Out of curiosity, which database you using; Oracle or SQL Server?

[leosuth]

I did say 'kind of to do with databases'

Although this problem is to do with outputting data into an Excel file (the OLEDB kind of bringing in the database element - ie. handling of Sheets as though they were Tables), the source of the data could be anything, including a database.

In this case, what I'm actually doing is inputting from a customer supplied XLS, doing some reformatting and consolidation of the data, and then outputting to another XLS to send back to customers (again, don't ask, they are paying for the service ). This is one 'tool' in a toolkit we use for various functions.

Hopefully, if anyone else needs to do something similar from a database, well, here's the answer.

Normally, I'd be using SQL Server

[jasdf]

I know this is an old post, but if anyone else runs into this, try changing the column type to Text or LongText the OleDbParameter type from OleDbType.Varchar to OleDbType.BSTR.