[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Update Query says unterminated string constant

HERE IT IS EXPERTS:
dim EHSID, PIName, Office, Phone, UMC, Email, selectCollege, Building, Room
      
EHSID = trim(replace(request("EHSID"),"'","''"))
PIName = trim(replace(request("PIName"),"'","''"))
Office = trim(replace(request("Office"),"'","''"))
Phone = trim(replace(request("Phone"),"'","''"))
UMC = trim(replace(request("UMC"),"'","''"))
Email = trim(replace(request("Email"),"'","''"))
selectCollege = trim(replace(request("selectCollege"),"'","''"))
Building = trim(replace(request("Building"),"'","''"))
Room = trim(replace(request("Room"),"'","''"))
      
'sql update to update pi information
sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "'_
      & Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & "'_
      & WHERE GeneratorNum = " & EHSID & ")"
      oConn.Execute(sql)

I get the following on the sql statement:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant

ANY IDEAS?      
0
jhmoen
Asked:
jhmoen
  • 17
  • 15
  • 9
  • +1
2 Solutions
 
hongjunCommented:
try this


sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "'_
     & Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & "'_
     & " WHERE GeneratorNum = " & EHSID & ")"
0
 
hongjunCommented:
You are short of one " before the WHERE
0
 
hongjunCommented:
I assume all your Request(...) return something :)
0
Industry Leaders: 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!

 
jhmoenAuthor Commented:
I am testing now to see if the all return something.
It is not the extra " before where....my editor is throwing off the colors and I tested it, still same error.

0
 
davidlars99Commented:
I think problem occurs when replacing single quote with two single quotes


0
 
davidlars99Commented:
just for the test do not use "replace(..)" function for a moment and try it
0
 
davidlars99Commented:
or do not pass single (') quotes
0
 
davidlars99Commented:
before you change anything use "Response.Write(sql)" instead of "oConn.Execute(sql)" and post the output
0
 
jhmoenAuthor Commented:
you mean in my dim declarations: EHSID = trim(replace(request("EHSID"),"'","''"))

or in my sql?
0
 
jhmoenAuthor Commented:
They are returning values
0
 
davidlars99Commented:
just use Trim(request("EHSID")) for a moment and do not use (') anywhere on your form
0
 
jhmoenAuthor Commented:
Still get error:
Here is what I have:
EHSID = trim(request("EHSID"))
PIName = trim(request("PIName"))
Office = trim(request("Office"))
Phone = trim(request("Phone"))
UMC = trim(request("UMC"))
Email = trim(request("Email"))
selectCollege = trim(request("selectCollege"))
Building = trim(request("Building"))
Room = trim(request("Room"))
      
'sql update to update pi information
sql = "UPDATE ehspeople SET (Name=" & PIName & ", Office=" & Office & ",Phone=" & Phone & ",UMC=" & UMC & "_
     & Email=" & Email & ",College=" & selectCollege & ",BuildingName=" & Building & ",RoomID=" & Room & "_
     &  WHERE GeneratorNum = " & EHSID & ")"
      oConn.Execute(sql)

ERROR IS:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
ON THE SQL STATEMENT???????
0
 
jhmoenAuthor Commented:
Error is on the sql statement.
I have raised the points.  I am desperate for this to work.
0
 
davidlars99Commented:
I said remove (') only in the FORM and, which means DO NOT PASS (') thru the form and post SQL statement
0
 
davidlars99Commented:
NOT IN SQL!!!
0
 
jhmoenAuthor Commented:
I put it all on one line:sql = "UPDATE ehspeople SET Name=" & PIName & ", Office=" & Office & ",Phone=" & Phone & ",UMC=" & UMC & ",Email=" & Email & ",College=" & selectCollege & ",BuildingName=" & Building & ",RoomID=" & Room & " WHERE GeneratorNum = " & EHSID & ""

Now I get: Syntax error in UPDATE statement.

Something is not right with the WHERE i think

??
0
 
davidlars99Commented:
ok do this and do not pass (') thru the form for a moment

EHSID = trim(request("EHSID"))
PIName = trim(request("PIName"))
Office = trim(request("Office"))
Phone = trim(request("Phone"))
UMC = trim(request("UMC"))
Email = trim(request("Email"))
selectCollege = trim(request("selectCollege"))
Building = trim(request("Building"))
Room = trim(request("Room"))
     
'sql update to update pi information
sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "'_
     & Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & "'_
     & WHERE GeneratorNum = " & EHSID & ")"
     oConn.Execute(sql)
0
 
jhmoenAuthor Commented:
I have not been passing (') through the form at all

THIS IS THE ERROR WITH YOUR CODE ABOVE:
Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/testhazform/pickupInsertDB.asp, line 43, column 116
sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "'_
-------------------------------------------------------------------------------------------------------------------^
0
 
davidlars99Commented:
try this

sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "' Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & "' WHERE GeneratorNum = " & EHSID & ")"

oConn.Execute(sql)
0
 
jhmoenAuthor Commented:
ERROR:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.

I tried putting it all on one line before and got the same thing.

UUUUggggghhhh.
0
 
davidlars99Commented:
> I tried putting it all on one line before and got the same thing....

it doesn't matter either it's one line or 100 lines as long as it's correct, so keep it all in one line so far until we're finished

> Syntax error in UPDATE statement....

that's because you are trying to put "string" type value into the field of type "integer" or the other way around, I'm conserned about --> RoomID='" & Room & "'  make it --> RoomID=" & Room & "
0
 
hongjunCommented:
Try this

sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "', Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & "' WHERE GeneratorNum = " & EHSID & ")"



hongjun
0
 
hongjunCommented:
You are short of 1 comma
0
 
hongjunCommented:
Would you like to response.write your sql statement?

sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "', Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & "' WHERE GeneratorNum = " & EHSID & ")"
response.write sql
response.end



Post here your sql statement.
0
 
davidlars99Commented:
yeah... right after --> UMC='" & UMC & "'
0
 
jhmoenAuthor Commented:
Okay, We are getting closer...I really appreciate both of your help on this.
I have corrected the comma, but still go the syntax error in Update statement error.  All of my fields are TEXT format in the db, except for EHSID.  So I am sure the Update statement as we have it is good.

I output my sql statement as hongjun stated above.
It output to another page called "pickupProc.asp" which is some type of validation page for another INSERT statement on the same page as my UPDATE.  I did not write this page and am not sure why it is affecting my UPDATE.  Basically it is validating the textfields for this other INSERT statement.  I have no idea how it knows to reference this page because there are no references to it from my page.  (I am working on pages that are not mine)

The output is as follows, which looks good: (output on this strange "pickupProc.asp" page)
UPDATE ehspeople SET (Name='Chen, Adams',Office='fdfadsf',Phone='dasdf',UMC='asdf',Email='dddd',College='Food Services',BuildingName='AG SCIENCE',RoomID='002' WHERE GeneratorNum = 937)  THIS LOOKS GOOD.

NOW, here is the entire code for this pickupProc.asp page:
<%@ Language = "VBSCRIPT"%>
<%
      OPTION EXPLICIT

  Response.Buffer = True
  Response.ExpiresAbsolute = Now() - 1
  Response.Expires = 0
  Response.CacheControl = "no-cache"


      

      
      Function EnglishName(str)
            'Check to see if there is an exclamation point
            'if so, hack off all contents of the string
            'to the right of the exclamation point
            
            If InStr(1,str,"!") > 0 then
                  EnglishName = Left(str,Instr(1,str,"!") - 1)
            End If
      End Function
      
      'Determine what collection to use
      Dim strCollection
      strCollection = Request("Collection")
      
      'create a reference to the property Request Collection
      Dim colRequestCol
      
      If UCase(strCollection) = "QUERYSTRING" then
            Set colRequestCol = Request.QueryString
      Else
            Set colRequestCol = Request.Form
      End if
      
      
      Dim strItem, strErrors, objRegExp,strErrorLog
      
      Set objRegExp = new RegExp
      objRegExp.IgnoreCase = True
      objRegExp.Global = True
      
      'Iterate through each of the form elements
      For Each strItem in colRequestCol
            'See if there is an exclamation point
            'If there is, then we need to perform form validation
            If InStr(1,strItem,"!") > 0 then
                  If trim(replace(Request(strItem),"'","''")) = "" then
                        'Response.Write strItem
                        strErrorLog = strErrorLog & "<br>Invalid input for " & EnglishName(strItem)
                        'Response.Write(strErrorLog)
                        'Response.End
                  End If
            End If
      Next
      
      'Are there any errors?
      If Len(strErrorLog) > 0 then
            session("errorText") = ("<b>The following errors occurred:</b>" & strErrorLog)
            server.transfer("pickup.asp")
      Else
            'No form validation errors occurred
            'Use server.transfer to send user to the proper form validation script
            'If the user didn't specify a redirect, raise an error
            
            If Len(Request("REDIRECT")) = 0 then
                  err.Raise 1010,"Validation Error", "Redirect not specified"
            Else
                  server.transfer request("Redirect")
                  'Response.Write("no errors")
            End If
      End If
      
      Set colRequestCol = nothing
      Set objRegExp = nothing

%>

What I would like is to just avoid this page for my UPDATE, but use it for the INSERT - but I am not sure how it is referenced.???

Any Ideas?

0
 
jhmoenAuthor Commented:
Okay,
It it a bunch to do with session variables.  I will have to change some code.
I will keep you posted.  As far as the UPDATE we worked on I think it works okay.

WIll be in touch.
JOHN
0
 
davidlars99Commented:
can you get rid of these  -->   )(
0
 
hongjunCommented:
I got it..
Should be this


sql = "UPDATE ehspeople SET Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "', Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & "' WHERE GeneratorNum = " & EHSID



hongjun
0
 
hongjunCommented:
@davidlars99

Sorry for the duplicate post.
Great minds think alike.


hongjun
0
 
davidlars99Commented:
don't even think about "sorry", you're cool..!  :)
0
 
jhmoenAuthor Commented:
Took out the parethases....do they have anything to do with the pickupProc file I posted.
Still yields same result.
JOHN
0
 
davidlars99Commented:
what database it it..  Access or SQL Server?
0
 
davidlars99Commented:
> do they have anything to do with the pickupProc....

I don't even know what's going on there...  
0
 
jhmoenAuthor Commented:
Its Access.
Let me try a few things.  Basically it is a validation script of sorts.  The fields from a previous page are stored as session variables for ease in passing between pages, and anything with a ! in the name of the textfield gets validated...at least thats what I am seeing.

I will let you know...You "great minds" have been very helpful.

JOHN
0
 
hongjunCommented:
so is the problem solved?
Or now it is another issue?
0
 
jhmoenAuthor Commented:
I will be out the rest of the weekend.
I won't know until i resolve this issue.
JOHN
0
 
jhmoenAuthor Commented:
I have got it solved.
My validation script is now working.

I decided to split the points between you two.  I hope you are okay with that.
My SQL seems to be working great now, thank you for getting me there, I had several things going on that I did not see.

Great work you two.

JOHN
0
 
ThaSmartUnoCommented:
I know this question is already answered ... but just to let you know what was originally wrong was this here ...
sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & "'_
-----------------------------------------------------------------------------------------------------------------------------------------^(a)
     & Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & "'_
-----^(call this c)    -----------------------------------------------------------------------------------------------------------^(d)
     & WHERE GeneratorNum = " & EHSID & ")"
-----^(e)

so where a is, there is "' _ ... yes the _ is a concatenator operator(making this word up im sure) except that it is contained inside a string so to rewrite these lines it would be like

sql = "UPDATE ehspeople SET (Name='" & PIName & "', Office='" & Office & "',Phone='" & Phone & "',UMC='" & UMC & _
     & "',Email='" & Email & "',College='" & selectCollege & "',BuildingName='" & Building & "',RoomID='" & Room & _
     & "' WHERE GeneratorNum = " & EHSID & ")"

please note if there were errors in the sql, I did not change them =)
0
 
jhmoenAuthor Commented:
Thank you ThaSmartOne,
Where did you come from!?

Thanks again, goog info for future.
0
 
ThaSmartUnoCommented:
just browsing the forum now =)
0
 
jhmoenAuthor Commented:
ThaSmartUNo,

How would it look for this:
ssql = "SELECT FloorNum, RoomDesc, UMC, ContactName, ContactUSUPhone, ContactHomePhone, ContactEmail, AltContactName_
            & ,AltContactUSUPhone, AltContactHOmePhone, FireExtLocation, NumFumeHoods, NumBioCabinets, FlamCombustMaxLiq_
            & ,FlamCombustMaxSolid, CorrosiveMaxLiq, CorrosiveMaxSolid, PeroxidesMaxLiq, PeroxidesMaxSolid, WaterReactiveMaxLiq_
            & ,WaterReactiveMaxSolid, UnstableReactiveMaxLiq, UnstableReactiveMaxSolid, AcuteToxicMaxLiq, AcuteToxicMaxSolid_
            & ,CRToxinsMaxLiq, CTToxinsMaxSolid, Isotopes, InfectiousBio, Power, Other, LastUpdated_
            & FROM EmergecyResponse WHERE BuildingName ='" + Request.Form(Building, "'", "''") + "' AND RoomID = '" + Request.Form(Room, "'", "''")+ "'"
            
set requestRS = oConn.Execute(ssql)
0
 
ThaSmartUnoCommented:
ssql = "SELECT FloorNum, RoomDesc, UMC, ContactName, ContactUSUPhone, ContactHomePhone, ContactEmail, AltContactName"_
          & ",AltContactUSUPhone, AltContactHOmePhone, FireExtLocation, NumFumeHoods, NumBioCabinets, FlamCombustMaxLiq"_
          & ",FlamCombustMaxSolid, CorrosiveMaxLiq, CorrosiveMaxSolid, PeroxidesMaxLiq, PeroxidesMaxSolid, WaterReactiveMaxLiq"_
          & ",WaterReactiveMaxSolid, UnstableReactiveMaxLiq, UnstableReactiveMaxSolid, AcuteToxicMaxLiq, AcuteToxicMaxSolid"_
          & ",CRToxinsMaxLiq, CTToxinsMaxSolid, Isotopes, InfectiousBio, Power, Other, LastUpdated"_
          & " FROM EmergecyResponse WHERE BuildingName ='" + Request.Form(Building, "'", "''") + "' AND RoomID = '" + Request.Form(Room, "'", "''")+ "'"

you will notice every time you have to goto the next line you have to end the quote and begin the quote again on a new line
0
 
jhmoenAuthor Commented:
Ahhhhh, gotcha.
Its working.  Now that this question is closed, not sure how to award you points?!
0
 
ThaSmartUnoCommented:
dont worry about it
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 17
  • 15
  • 9
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now