September 12, 2008 at 3:06 am
I 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:P)
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?
September 12, 2008 at 4:41 am
I’m not sure that you’ll get an answer from this forum. You are trying to create a table in Excel and this forum is about SQL Server. You’ll have better chance of getting an answer in an Excel forum.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 12, 2008 at 7:42 am
Instead of using the (8000) use the varchar(Max), another suggestion is to use T-SQL and let SQL do it, instead of front end Interfaces.;)
"We never plan to Fail, We just fail to plan":)
September 12, 2008 at 11:11 am
I could be wrong, but I don't think Excel can handle fields that size.
The other possibility that comes to mind is that the row-size has a limitation for total data. I know in SQL 2000, if you create a table with a total possible size larger than the max (a little over 8000 bytes), you'll get an error if you try to fill it all up. Excel might have a similar limitation.
Not being any sort of serious Excel expert, I'm extrapolating, and could easily be wrong, but either one makes sense to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 13, 2008 at 4:41 am
OK people, thanks very much for your suggestions. 🙂
From another place, however, I got the suggestion to use
MEMO(8000)
And that appears to have solved my problem altogether 🙂
September 15, 2008 at 7:07 am
Cool. Glad you got that working.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply