Update field value

ASP Free Forums Sponsor:

September 4th, 2006, 08:06 AM

xara

Registered User

Join Date: Aug 2006

Location: South Africa

Posts: 4

Time spent in forums: 3 m 59 sec
Reputation Power: 0

Update field value

Hi,

I am importing a excell data into a table.

Now the problem is that I have a field called year in the Table and the spreadsheet. When I run the code to insert the data from the spreadsheat I get the following error

"Syntax error in INSERT INTO statement."

Now I know that has to do with the year field beacause when I leave the year field out it works.

How do I get past this problem?

The Code:

Code:

<% 

Set ExcelConn = Server.CreateObject("ADODB.Connection")
Set ExcelRS = Server.CreateObject("ADODB.Recordset")
ExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
ExcelConn.Open "vehicles.xls"

'get data from sheet
sSQL = "SELECT * FROM [Sample$]"
set ExcelRS = ExcelConn.Execute(sSQL)

'loop through each record in Excel and write it to access

Set objConnAccess = Server.CreateObject("ADODB.Connection")
src = Server.MapPath("mydb.mdb")
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
objConnAccess.Open  sConnStr

Do until ExcelRS.EOF 
objConnAccess.Execute("INSERT INTO sampletbl (show_on_website,del,display_title,stock_number,ma  nufacturer,year,price,colour,vin,mileage,features)   VALUES ('" & ExcelRS("show_on_website") & "','" & ExcelRS("del") & "','" & ExcelRS("display_title") & "','" & ExcelRS("stock_number") & "','" & ExcelRS("manufacturer") & "','" & ExcelRS("year") & "','" & ExcelRS("price") & "','" & ExcelRS("colour") & "','" & ExcelRS("vin") & "','" & ExcelRS("mileage") & "','" & ExcelRS("features") & "')")




ExcelRS.MoveNext
Loop

ExcelRS.Close
set ExcelRS = NOTHING
'objExcelConn.Close
set ExcelConn = NOTHING
objConnAccess.close
set objConnAccess = NOTHING




%>

I do not have the option of leaving the year field empty!

Is it possible to use the update statement and how?

Thanx!!

September 4th, 2006, 08:10 AM

micky

Couch Potato Wizard

Join Date: Jan 2005

Location: India

Posts: 10,841

Folding Points: 1480 Folding Title: Novice Folder

Time spent in forums: 4 Months 4 Days 9 h 33 m 44 sec
Reputation Power: 1705

try wrapping such fields it in [], like this

Code:

objConnAccess.Execute("INSERT INTO sampletbl (show_on_website,del,display_title,stock_number,ma    nufacturer,[year],[price],colour,vin,mileage,features)   VALUES 
('" & ExcelRS("show_on_website") & "','" & ExcelRS("del") & "','" & ExcelRS("display_title") & "','" & ExcelRS("stock_number") & "','" & ExcelRS("manufacturer") & "','" & ExcelRS("year") & "','" & ExcelRS("price") & "','" & ExcelRS("colour") & "','" & ExcelRS("vin") & "','" & ExcelRS("mileage") & "','" & ExcelRS("features") & "')")

hope it helps

__________________
Laziness is my religion and Sunday is my God

Get the Mantra!

September 4th, 2006, 08:21 AM