Microsoft OLE DB Provider for SQL Server (0x80040E14)

Hi,

I am working to maintain a website. This website works fine generally. The problem arised when we had to edit the data in the database (MS SQL Server).Using an Update command from an ASP page to update the database.This update works fone for some records and gives error for some records. The error encountered is as follows:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Line 1: Incorrect syntax near 'S'.
/shpo/enterdat/enterdata2.asp, line 71


Line 71 in the program is:

<% sss=conn.execute("update NHSDATA set [property name]='"+FixQuotes(t1)+"',Description_of_Significance='"+FixQuotes(t2)+"',[Current Name]='"+FixQuotes(t4)+"',[Project Name]='"+t5+"',Address='"+FixQuotes(t6)+"',City='"+FixQuotes(t7)+"',County='"+FixQuotes(t9)+"',[County Fips]='"+FixQuotes(t10)+"',LOT='"+FixQuotes(t11)+"',Block='"+FixQuotes(t12)+"',section='"+FixQuotes(t13)+"',township='"+FixQuotes(s1)+"',Range='"+FixQuotes(s2)+"',Type='"+FixQuotes(t16)+"',[Historic Function]='"+FixQuotes(t17)+"',[Current Function]='"+FixQuotes(t18)+"',[Area Significance 1]='"+FixQuotes(t20)+"',[Area Significance 2]='"+FixQuotes(t21)+"',[Date Prepared]='"+FixQuotes(t23)+"',[Year Built]='"+FixQuotes(t24)+"',[Orignal Site]='"+FixQuotes(t25)+"',[Architect]='"+FixQuotes(t26)+"',[Architectural Style]='"+FixQuotes(s3)+"',[Roof Material]='"+FixQuotes(s4)+"',[Wall Material 1]='"+FixQuotes(t27)+"',[Window Material]='"+FixQuotes(t271)+"',[Door Material]='"+FixQuotes(s5)+"',[Decorative Details]='"+FixQuotes(s6)+"',Condition='"+FixQuotes(s7)+"' where ID_FIELD='"+h1+"'" )%>

Kindly let me know what has to be done. I thought of assigning some default values to the fields in the database, and assigned "Null" as the default value but that does not seem to be working.

Please give me a solution.

Thanks
Pragya.
jaiswalpragyaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fritz_the_blankCommented:
Well, if you can recreate the error with certain updates, then try this:

response.write("update NHSDATA set [property name]='"+FixQuotes(t1)+"',Description_of_Significance='"+FixQuotes(t2)+"',[Current Name]='"+FixQuotes(t4)+"',[Project Name]='"+t5+"',Address='"+FixQuotes(t6)+"',City='"+FixQuotes(t7)+"',County='"+FixQuotes(t9)+"',[County Fips]='"+FixQuotes(t10)+"',LOT='"+FixQuotes(t11)+"',Block='"+FixQuotes(t12)+"',section='"+FixQuotes(t13)+"',township='"+FixQuotes(s1)+"',Range='"+FixQuotes(s2)+"',Type='"+FixQuotes(t16)+"',[Historic Function]='"+FixQuotes(t17)+"',[Current Function]='"+FixQuotes(t18)+"',[Area Significance 1]='"+FixQuotes(t20)+"',[Area Significance 2]='"+FixQuotes(t21)+"',[Date Prepared]='"+FixQuotes(t23)+"',[Year Built]='"+FixQuotes(t24)+"',[Orignal Site]='"+FixQuotes(t25)+"',[Architect]='"+FixQuotes(t26)+"',[Architectural Style]='"+FixQuotes(s3)+"',[Roof Material]='"+FixQuotes(s4)+"',[Wall Material 1]='"+FixQuotes(t27)+"',[Window Material]='"+FixQuotes(t271)+"',[Door Material]='"+FixQuotes(s5)+"',[Decorative Details]='"+FixQuotes(s6)+"',Condition='"+FixQuotes(s7)+"' where ID_FIELD='"+h1+"'" )

response.end

Now we can look at the resulting SQL statement and track down the error.

FtB
0
jaiswalpragyaAuthor Commented:
I tried this statement and it is giving me this error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/SHPO/ENTERDAT/enterdata2.asp, line 71, column 1052
response.write("update NHSDATA set [property name]='"+FixQuotes(t1)+"',Description_of_Significance='"+FixQuotes(t2)+"',[Current Name]='"+FixQuotes(t4)+"',[Project Name]='"+t5+"',Address='"+FixQuotes(t6)+"',City='"+FixQuotes(t7)+"',County='"+FixQuotes(t9)+"',[County Fips]='"+FixQuotes(t10)+"',LOT='"+FixQuotes(t11)+"',Block='"+FixQuotes(t12)+"',section='"+FixQuotes(t13)+"',township='"+FixQuotes(s1)+"',Range='"+FixQuotes(s2)+"',Type='"+FixQuotes(t16)+"',[Historic Function]='"+FixQuotes(t17)+"',[Current Function]='"+FixQuotes(t18)+"',[Area Significance 1]='"+FixQuotes(t20)+"',[Area Significance 2]='"+FixQuotes(t21)+"',[Date Prepared]='"+FixQuotes(t23)+"',[Year Built]='"+FixQuotes(t24)+"',[Orignal Site]='"+FixQuotes(t25)+"',[Architect]='"+FixQuotes(t26)+"',[Architectural Style]='"+FixQuotes(s3)+"',[Roof Material]='"+FixQuotes(s4)+"',[Wall Material 1]='"+FixQuotes(t27)+"',[Window Material]='"+FixQuotes(t271)+"',[Door Material]='"+FixQuotes(s5)+"',[Decorative Details]='"+FixQuotes(s6)+"',Condition='"+FixQuotes(s7)+"' where ID_FIELD='"+h1+"'" )response.end
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 1.0.3705)

Page:
POST 685 bytes to /SHPO/ENTERDAT/enterdata2.asp

POST Data:
T1=CLOVER+HOTEL&T2=ESTABLISHED+IN+1912+TO+SERVE+THE+TRANSIENT+POPULATION+THAT+CAME+TO+LAVERNE+WITH+THE+CONSTRUCTION+OF+THE+RAILROAD+AND+THE+ESTABLISHMENT++OF+THE+TOWN.++LISTED+IN+THE+NATIONAL+REGISTER . . .


The actual code is like this, its seems to work fine on updating most of the records, getting error on only a few records.The code is:

<%set conn=Server.CreateObject("ADODB.Connection")
set mycon=Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=(local);User ID=sa;Password=albus;Database=shipo;"
%>
<%
objectid=request("objectid")
h1=request("h1")
t1=request("t1")
t2=request("t2")
t4=request("t4")
t5=request("t5")
t6=request("t6")
t7=request("t7")
t8=request("t8")
t9=request("t9")
t10=request("t10")
t11=request("t11")
t12=request("t12")
t13=request("t13")
s1=request("s1")
s2=request("s2")
t16=request("t16")
t17=request("t17")
t18=request("t18")
t20=request("t20")
t21=request("t21")
t22=request("t22")
t23=request("t23")
t24=request("t24")
t25=request("t25")
t26=request("t26")
s3=request("s3")
s4=request("s4")
t27=request("t27")
t271=request("t271")

s5=request("s5")
s6=request("s6")
s7=request("s7")


Function FixQuotes (memo)
      memo2 = ""
      For i = 1 to Len(memo)
                  If Mid(memo,i,1) = """" Then
                        memo2 = memo2 & "&amp;quot;"
                  Else

                  If Mid(memo,i,1) = "'" Then
                        memo2 = memo2 & "&amp;#39;"
                  Else
                        memo2 = memo2 & Mid(memo,i,1)
                  End If
                  End if
                  
                  Next
      FixQuotes = memo2
End Function%>

<%response.write("update NHSDATA set [property name]='"+FixQuotes(t1)+"',Description_of_Significance='"+FixQuotes(t2)+"',[Current Name]='"+FixQuotes(t4)+"',[Project Name]='"+t5+"',Address='"+FixQuotes(t6)+"',City='"+FixQuotes(t7)+"',County='"+FixQuotes(t9)+"',[County Fips]='"+FixQuotes(t10)+"',LOT='"+FixQuotes(t11)+"',Block='"+FixQuotes(t12)+"',section='"+FixQuotes(t13)+"',township='"+FixQuotes(s1)+"',Range='"+FixQuotes(s2)+"',Type='"+FixQuotes(t16)+"',[Historic Function]='"+FixQuotes(t17)+"',[Current Function]='"+FixQuotes(t18)+"',[Area Significance 1]='"+FixQuotes(t20)+"',[Area Significance 2]='"+FixQuotes(t21)+"',[Date Prepared]='"+FixQuotes(t23)+"',[Year Built]='"+FixQuotes(t24)+"',[Orignal Site]='"+FixQuotes(t25)+"',[Architect]='"+FixQuotes(t26)+"',[Architectural Style]='"+FixQuotes(s3)+"',[Roof Material]='"+FixQuotes(s4)+"',[Wall Material 1]='"+FixQuotes(t27)+"',[Window Material]='"+FixQuotes(t271)+"',[Door Material]='"+FixQuotes(s5)+"',[Decorative Details]='"+FixQuotes(s6)+"',Condition='"+FixQuotes(s7)+"' where ID_FIELD='"+h1+"'" )response.end%>

<% sss=conn.execute("update NHSDATA set [property name]='"+FixQuotes(t1)+"',Description_of_Significance='"+FixQuotes(t2)+"',[Current Name]='"+FixQuotes(t4)+"',[Project Name]='"+t5+"',Address='"+FixQuotes(t6)+"',City='"+FixQuotes(t7)+"',County='"+FixQuotes(t9)+"',[County Fips]='"+FixQuotes(t10)+"',LOT='"+FixQuotes(t11)+"',Block='"+FixQuotes(t12)+"',section='"+FixQuotes(t13)+"',township='"+FixQuotes(s1)+"',Range='"+FixQuotes(s2)+"',Type='"+FixQuotes(t16)+"',[Historic Function]='"+FixQuotes(t17)+"',[Current Function]='"+FixQuotes(t18)+"',[Area Significance 1]='"+FixQuotes(t20)+"',[Area Significance 2]='"+FixQuotes(t21)+"',[Date Prepared]='"+FixQuotes(t23)+"',[Year Built]='"+FixQuotes(t24)+"',[Orignal Site]='"+FixQuotes(t25)+"',[Architect]='"+FixQuotes(t26)+"',[Architectural Style]='"+FixQuotes(s3)+"',[Roof Material]='"+FixQuotes(s4)+"',[Wall Material 1]='"+FixQuotes(t27)+"',[Window Material]='"+FixQuotes(t271)+"',[Door Material]='"+FixQuotes(s5)+"',[Decorative Details]='"+FixQuotes(s6)+"',Condition='"+FixQuotes(s7)+"' where ID_FIELD='"+h1+"'" )%>

0
fritz_the_blankCommented:
response.end should be on its own line after the response.write

FtB
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

jaiswalpragyaAuthor Commented:
I got this as the output:

update NHSDATA set [property name]='CLOVER HOTEL',Description_of_Significance='ESTABLISHED IN 1912 TO SERVE THE TRANSIENT POPULATION THAT CAME TO LAVERNE WITH THE CONSTRUCTION OF THE RAILROAD AND THE ESTABLISHMENT OF THE TOWN. LISTED IN THE NATIONAL REGISTER OF HISTORIC PLACES, 3/8/1984.abc',[Current Name]='CLOVER INN RESTAURANT',[Project Name]='HISTORIC PROPERTIES RELATING TO LAVERNE'S EARLY COMMERCIAL DEVELOPMENT',Address='',City='LAVERNE',County='HARPER',[County Fips]='059',LOT='1 - 4',Block='46',section='',township='',Range='',Type='B BUILDING',[Historic Function]='01D HOTEL',[Current Function]='02G RESTAURANT',[Area Significance 1]='030 ARCHITECTURE',[Area Significance 2]='050 COMMERCE',[Date Prepared]='8/1982',[Year Built]='1912',[Orignal Site]='YES',[Architect]='',[Architectural Style]='99 UNCOLLECTED',[Roof Material]='67 CERAMIC TILE',[Wall Material 1]='61 STUCCO',[Window Material]='20 WOOD',[Door Material]='20 WOOD',[Decorative Details]=' WOOD FRAMED DOORWAYS WITH MOLDED ENTABLATURES. ',Condition='02 GOOD' where ID_FIELD='84003034'

do you think the error is because of the fields which dont get any values?I can see the update I made here, so I wonder what the problem is?
Thanks.
0
fritz_the_blankCommented:
Okay, so this looks good I think. What happens if you paste this into your query analyzer? Does it update the record?

As far as the error goes, if you can paste the above into your query analyzer and it works, then you know that you syntax is okay and that the issue lies elsewhere. A few possibilities:

1) apostrophes or some other illegal character inserted into the field
2) data string too long for field size
3) inserting an empty value into fields that do not allow 0-length values

Do you know how to investigate these?

Also, if you know an entry that causes the failure, that would be good to know about.

FtB
0
Anthony PerkinsCommented:
It is failing because there is an apostophe in the [Project Name] value.  

Change this:
[Project Name]='HISTORIC PROPERTIES RELATING TO LAVERNE'S EARLY COMMERCIAL DEVELOPMENT'
To:
[Project Name]='HISTORIC PROPERTIES RELATING TO LAVERNE''S EARLY COMMERCIAL DEVELOPMENT',
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jaiswalpragyaAuthor Commented:
Hey, that seems to have worked!!!
Thanks a lot...I would never have got that error :)

Thanks Ftb and acperkins....This worked and solved my problem.
0
Anthony PerkinsCommented:
>>Thanks Ftb and acperkins....<<
In future, in cases like this you can do the following:
More than one Expert helped solve my problem. What do I do?
http://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi69
0
fritz_the_blankCommented:
Yeah, that is a bummer since I said:

1) apostrophes or some other illegal character inserted into the field
2) data string too long for field size
3) inserting an empty value into fields that do not allow 0-length values


which was my first point.

More important, however, is you need to put something in place to keep this from happening to you again. The next time that someone inserts a value with an apostrophe, your page will crash.,

FtB
0
jaiswalpragyaAuthor Commented:
I am sorry, I am a new member to this site (just joined 2 days back) and did not know this.I'll keep in mind the next time.

And about keeping it from happening again, what could be the possible solution?

Thanks.
0
fritz_the_blankCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.