SQL Update Problem 1

[ukwebsite]

Im runing a script within an ASP page to update a row in a access database. I write the sql script to the screen, copy and paste it into Access and run it and it works perfectly. But generates an error in the ASP page.

The error is

Microsoft JET Database Engine error '80040e14'
Syntax error in UPDATE statement.

The code is as follows
SqlScript = "UPDATE Data SET "
SqlScript = SqlScript & "REFERENCE = '" & strREFERENCE & "', "
SqlScript = SqlScript & "NAME1 = '" & strNAME1 & "', "
SqlScript = SqlScript & "INT1ST = '" & strINT1ST & "', "
SqlScript = SqlScript & "NAME2 = '" & strNAME2 & "', "
SqlScript = SqlScript & "INT2ND = '" & strINT2ND & "', "
SqlScript = SqlScript & "ADDRESS1 = '" & strADDRESS1 & "', "
SqlScript = SqlScript & "ADDRESS2 = '" & strADDRESS2 & "', "
SqlScript = SqlScript & "ADDRESS3 = '" & strADDRESS3 & "', "
SqlScript = SqlScript & "ADDRESS4 = '" & strADDRESS4 & "', "
SqlScript = SqlScript & "POSTCODE = '" & strPOSTCODE & "', "
SqlScript = SqlScript & "UserID = '" & strUserID & "', "
SqlScript = SqlScript & "TYPEOFLOAN = '" & strTYPEOFLOAN & "', "
SqlScript = SqlScript & "LENDER = '" & strLENDER & "', "
SqlScript = SqlScript & "PURPRICE = '" & strPURPRICE & "', "
SqlScript = SqlScript & "LOAN = '" & strLOAN & "', "
SqlScript = SqlScript & "APP = '" & strAPP & "', "
SqlScript = SqlScript & "VALUATION = '" & strVALUATION & "', "
SqlScript = SqlScript & "VALUATIONMORE = '" & strVALUATIONMORE & "', "
SqlScript = SqlScript & "OFFER = '" & strOFFER & "', "
SqlScript = SqlScript & "OFFERMORE = '" & strOFFERMORE & "', "
SqlScript = SqlScript & "COMPLETION = '" & strCOMPLETION & "', "
SqlScript = SqlScript & "EXCHANGE = '" & strEXCHANGE & "', "
SqlScript = SqlScript & "STATUS = '" & strSTATUS & "', "
SqlScript = SqlScript & "SOLICITORS = '" & strSOLICITORS & "', "
SqlScript = SqlScript & "SOLICITORSMORE = '" & strSOLICITORSMORE & "', "
SqlScript = SqlScript & "COMMENTS = '" & strCOMMENTS & "', "
SqlScript = SqlScript & "AccLastUpdated = '" & strAccLastUpdated & "' "
SqlScript = SqlScript & " WHERE ID = " & strID

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SqlScript, sConnString, , , adCmdText


The SQL statment produced is

UPDATE Users SET Password = 'example', Username = 'example', Company = 'Example UK', ContactFirst = 'Manchester', ContactLast = 'Office', Address1 = 'Example House', Address2 = 'Example Lane', Address3 = 'Example', PostCode = 'EX1 5PL', Phone = '01614554545', Fax = '01614554546', Email = 'mike@apseurope.com', Mobile = '' WHERE ID = 108

Matthew Wilde
matthew@ukwebsite.com

 

[amorous]

Your SQL statement produced is different from actual UPDATE query...

anyways...the update statement looks good...can you do a response.write on it and post the actual query produced...

-VJ

 

[ukwebsite]

I have done a response.write on it and that is shown above. I will include it again (Response.write SqlScript) this is what is displayed

UPDATE Users SET Password = 'example', Username = 'example', Company = 'Example UK', ContactFirst = 'Manchester', ContactLast = 'Office', Address1 = 'Example House', Address2 = 'Example Lane', Address3 = 'Example', PostCode = 'EX1 5PL', Phone = '01614554545', Fax = '01614554546', Email = 'mike@apseurope.com', Mobile = '' WHERE ID = 108

Matthew Wilde
matthew@ukwebsite.com

 

[ChrisHirst]

Matthew,

what VJ is saying is that the SQL statement you supplied was not produced by the code above it.


Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[vbkris]

and avod using field names like Username, they are key words in access, try enclosing all filed names within []
ie
set [Username]='asd'

Known is handfull, Unknown is worldfull

 

[ukwebsite]

Whoops. Oh yes. I must have copied it from the wrong page.

Anyway i added the [] and it worked perfectly. Why is this? The code used to work fine without them, i have never used them before. Mind you im on Win2003 now.

Thank you both very much for your help.



Matthew Wilde
matthew@ukwebsite.com

 

[vbkris]

thats because certain words like Username are keywords that can be used during access programming, to differentiate the keyword from a table's column name [] has to be added...

Known is handfull, Unknown is worldfull

 

[ukwebsite]

Cheers.

I dont know what I would do without Tek-Tips! Someone always has the answer.

Matthew Wilde
matthew@ukwebsite.com