Another "text" field problem

[jimny]

This might be lengthy, but I was reading CoolCoders' Post & Slr22s' post, but could not apply those responses to my problem. I am loading a table weekly from a .txt file generated by an application. It is on an ASP page, but does not necessarily have to stay there. I get through 5 inserts before it throws this errorThe bolded line at the bottom is where the error gets thrown)

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 't'.

/loadtable2.asp, line 70

The code is as follows(ugly as it is):

sqlDt = "Drop table [dbo].[tblADS]" & _
"CREATE TABLE [dbo].[tblADS] (" & _
"[ID] [int] IDENTITY (1, 1) NOT NULL ," & _
"[category] [varchar] (50) NOT NULL ," & _
"[sdate] [varchar] (15) NULL ," & _
"[endDate] [varchar] (15) NULL ," & _
"[Ayear] [varchar] (15) NULL ," & _
"[Amake] [varchar] (50) NULL ," & _
"[Amodel] [varchar] (50) NULL ," & _
"[price] [varchar] (50) NULL ," & _
"[location] [varchar] (50) NULL ," & _
"[objtext] [text] NULL ," & _
"[adtext] [text] NULL )"

conn.Execute(sqlDT)

Set fs=Server.CreateObject("Scripting.FileSystemObject&quot
Set f=fs.OpenTextFile(Server.MapPath("penny.txt&quot, 1)
do while f.AtEndOfStream = false
ad= f.ReadLine
ad= replace(ad,"|,|","^&quot
ad= replace(ad,"|","&quot
aryTxt = split(ad,"^&quot
cat= aryTxt(0)
sdate= aryTxt(1)
edate= aryTxt(2)
year= aryTxt(3)
make= aryTxt(4)
model= aryTxt(5)
price= aryTxt(6)
loc= aryTxt(7)
objT= aryTxt(8)
adT= aryTxt(9)
'---------------- SQL Insert ----------------
sqlINS= "INSERT INTO tblADS (category,sdate,endDate,Ayear,Amake,Amodel,price,location,objtext,adtext)" & _
"VALUES ('"& cat &"','"& sdate &"','"& edate &"','"& year & "','" & make &"'," & _
"'"& model &"','"& price & "','" & loc &"','"& objT &"','"& adT &"')"
conn.Execute(sqlINS)

the first few lines of the .txt file are:

|Antiques & Auctions|,|11/28/2001|,|04/30/2003|,||,||,||,||,||,||,|<B>CLOCKS, ANTIQUE & NEW CLOCK REPAIR. </B> Grandfather clock specialist. Visit our large showroom. Call Clock Shop.|,||
|Antiques & Auctions|,|03/12/2003|,|03/12/2003|,||,||,||,||,||,||,|<B>ANTIQUES PURCHASED </B> - Highest cash prices paid for all types of antiques & vintage collectibles. Call or visit the ANTIQUES CENTER on Rte.& 1 mile north Rte.. Open 11:00am-5:00pm, closed Tuesdays 123-123-1234.|,||
|Antiques & Auctions|,|03/12/2003|,|03/12/2003|,||,||,||,||,||,||,|<B>ANTIQUE AUCTION- </B> March 15, 5pm. Preview, Friday 3-9pm & Saturday 3pm. 800+ lots: Furniture, 100 Oriental Rugs, China, Glass & Sliver, Art Pottery, Coins, Books, Motor Home, Vehicles, etc. Listing & photos |,||
|Antiques & Auctions|,|03/12/2003|,|03/12/2003|,||,||,||,||,||,||,|<B>ANTIQUE/OLD CLOCKS BOUGHT/REPAIRED - </B> Clock Books, Clock Tools. Please Call Steven,123-123-1234 . |,||
|Automobile|,|06/20/2001|,|04/30/2003|,||,||,||,||,||,||,|<B>ATTENTION AUTOMOBILE ADVERTISERS/CUSTOMERS: </B> All classified ads placed in the are automatically placed on the Web Site for 1 week. For an additional $6 (Business/Commercial Accounts $10), you get 3 more weeks on our Internet Web Site. If you do not wish to have your ad on the internet, it is YOUR responsibility to notify the Classified Customer Service Rep. |,||
|Automobile|,|06/27/2001|,|04/30/2003|,||,||,||,||,||,||,|<B>WANT TO SELL YOUR HOUSE? YOUR CAR? FAST? </B> The Pr will help you do it. A Classified Sales Representative will be happy to talk to you, give you rates and help you with your ad. Just call the office nearest you: 123-123-1234. Don't wait, Call now! |,||

Thanks for any replys(if there are any)

 

[SQLSister]

That section of text which will go to one field has an apostrophe in it. Look at set quoted_identifier in SQL Books on line.

Personally I'd do any data imports with DTS and not with an ASP page, but it's up to you. Depending on how many records you have doing an insert like this one line at a time would be slow. It's also better not to just drop the table. Truncate it instead. That isn't logged and will save on your transaction log and won't lose all your indices, constraints etc.