?
Solved

Microsoft OLE DB Provider for SQL Server (0x80040E14)

Posted on 2004-11-08
11
Medium Priority
?
321 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:jaiswalpragya
  • 5
  • 4
  • 2
11 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12528258
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
 

Author Comment

by:jaiswalpragya
ID: 12536847
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12537051
response.end should be on its own line after the response.write

FtB
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jaiswalpragya
ID: 12537182
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12537289
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 12540904
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
 

Author Comment

by:jaiswalpragya
ID: 12542431
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12544918
>>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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12545217
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
 

Author Comment

by:jaiswalpragya
ID: 12547032
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12547096
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question