Start Free Trial

asked on

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?

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 & ")"

You are short of one " before the WHERE

I assume all your Request(...) return something :)

ASKER

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.

I think problem occurs when replacing single quote with two single quotes

just for the test do not use "replace(..)" function for a moment and try it

or do not pass single (') quotes

before you change anything use "Response.Write(sql)" instead of "oConn.Execute(sql)" and post the output

ASKER

you mean in my dim declarations: EHSID = trim(replace(request("EHSID"),"'","''"))

or in my sql?

ASKER

They are returning values

just use Trim(request("EHSID")) for a moment and do not use (') anywhere on your form

ASKER

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???????

ASKER

Error is on the sql statement.
I have raised the points. I am desperate for this to work.

I said remove (') only in the FORM and, which means DO NOT PASS (') thru the form and post SQL statement

NOT IN SQL!!!

ASKER

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

??

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)

ASKER

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 & "'_
-------------------------------------------------------------------------------------------------------------------^

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)

ASKER

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.

> 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 & "

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

You are short of 1 comma

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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?

ASKER

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

can you get rid of these --> )(

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

@davidlars99

Sorry for the duplicate post.
Great minds think alike.

hongjun

don't even think about "sorry", you're cool..! :)

ASKER

Took out the parethases....do they have anything to do with the pickupProc file I posted.
Still yields same result.
JOHN

what database it it.. Access or SQL Server?

> do they have anything to do with the pickupProc....

I don't even know what's going on there...

ASKER

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

so is the problem solved?
Or now it is another issue?

ASKER

I will be out the rest of the weekend.
I won't know until i resolve this issue.
JOHN

ASKER

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

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 =)

ASKER

Thank you ThaSmartOne,
Where did you come from!?

Thanks again, goog info for future.

just browsing the forum now =)

ASKER

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)

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

ASKER

Ahhhhh, gotcha.
Its working. Now that this question is closed, not sure how to award you points?!

dont worry about it