asked on

when changing update statement syntax/ receiving errors.

i'm trying to change the update statement:

conn.Execute("UPDATE tblServiceStandards SET Action_Taken = Action_Taken + '" & vbCrLf & dateCompleted & "' + ' " & Action_Taken & "' WHERE ticketnumber = " & ticketnumber & ";")

to this form:

Sub saveVariables

opencon
sql = "SELECT * FROM tblServiceStandards WHERE ticketnumber =" & ticketnumber
set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sql, connectme, 1, 3

if rs.bof = true and rs.eof = true then
response.write ("Record not found!")
else
do until rs.eof = true
if IsDate(dateCompleted) then
Rs.Fields("fldDate") = sfldDate
Rs.Fields("Reported_By").value = sReported_By
Rs.Fields("Severity").value = sSeverity
Rs.Fields("Duration").value = sDuration
Rs.Fields("Status").value = sStatus
Rs.Fields("ServicesAffectedID").value = sServicesAffectedID
Rs.Fields("Problem_Description").value = sProblem_Description
Rs.Fields("Assigned_To").value = sAssigned_To
Rs.Fields("Action_Taken").value = "Action_Taken + '" & vbcrlf & dateCompleted & " ' + ' " & sAction_Taken & " ' "
rs.UpdateBatch adAffectAll
exit do
end if
rs.movenext
Loop
end if
closecon
End Sub

and when i submit the page, i get an error that says: "The function expects a string as input" line 213
Rs.Fields("Action_Taken").value = "Action_Taken + '" & vbcrlf & dateCompleted & " ' + ' " & sAction_Taken & " ' " << line 213

i am would like to insert/append into the field Äction_Taken the values dateCompleted, and sAction_Taken which is appended to whatever contents are already on the field.

can i be advise on how do i need to change this line, so all these values are inserted? thanks for all you help. it is very much appreciated. please tell me if increasing the poin value is neccesary.

regards.

itortu.

amit_g

Change it to ...

Rs.Fields("Action_Taken").value = "'" & Rs.Fields("Action_Taken").value & vbcrlf & dateCompleted & sAction_Taken & " ' "

itortu

ASKER

hi! I followed your suggestion and changed it to:

if rs.bof = true and rs.eof = true then
Response.Write("Record not found!")
else
if lcase(cstr(rs.fields("TicketNumber").value)) = lcase(cstr(sTicketNumber)) then
      Rs.Fields("fldDate") = sfldDate
      Rs.Fields("Reported_By").value = sReported_By
      Rs.Fields("Severity").value = sSeverity
      Rs.Fields("Duration").value = sDuration
      Rs.Fields("Status").value = sStatus
      Rs.Fields("ServicesAffectedID").value = sServicesAffectedID
      Rs.Fields("Problem_Description").value = sProblem_Description
      Rs.Fields("Assigned_To").value = sAssigned_To
      Rs.Fields("Action_Taken").value = "'" & Rs.Fields("Action_Taken").value & vbcrlf & dateCompleted & sAction_Taken & " ' "
      rs.UpdateBatch adAffectAll
end if
end if

but now all it gets inserted is the single quotes at the beginning of the contents already existent and until the end.

' this text was already on this field '

amit_g

Either dateCompleted and sAction_Taken are empty or you are not seeing the update due to a newline in the text. Take out the vbCrLf and make sure dateCompleted and sAction_Taken has somevalue.

Rs.Fields("Action_Taken").value = "'" & Rs.Fields("Action_Taken").value & " - " & dateCompleted & sAction_Taken & " ' "

itortu

ASKER

hi amit_q,
now i can see the other field. i had to add a new field named action_taken_update instead of using action_taken only, otherwise the value were erased let me show you what i am doing ok?

sAction_Taken = Trim(Request.Form("Action_Taken"))
sAction_Taken = replace(Action_Taken,"'","''")
dateCompleted = Server.HTMLEncode(Request.Form("datecompleted"))

if lcase(sPageAction) = "" then
LoadVars
ShowEditor
end if
if lcase(sPageAction) = "save" then
SaveVars
ShowOk
end if

Sub LoadVars
opencon

ChooseTable "tblServiceStandards"
if rs.bof = true and rs.eof = true then
Response.Write("Record not found!")
else
do until rs.eof = true
if lcase(cstr(rs.fields("TicketNumber").value)) = lcase(cstr(sWhat)) then
      sTicketNumber = Rs.Fields("TicketNumber").value
      sfldDate = Rs.Fields("fldDate").value
      sReported_By = Rs.Fields("Reported_By").value
      sSeverity = Rs.Fields("Severity").value
      sDuration = Rs.Fields("Duration").value
      sStatus = Rs.Fields("Status").value
      sServicesAffectedID = Rs.Fields("ServicesAffectedID").value
      sProblem_Description = Rs.Fields("Problem_Description").value
      sAssigned_To = Rs.Fields("Assigned_To").value
      sAction_Taken = Rs.Fields("Action_Taken").value
exit do
end if
rs.movenext
Loop
end if
closecon
End Sub

Sub SaveVars
opencon
strSQL = "SELECT * FROM tblServiceStandards WHERE ticketnumber=" & sTicketNumber
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open strSQL, connectme, 1, 3

if rs.bof = true and rs.eof = true then
Response.Write("Record not found!")
else
if lcase(cstr(rs.fields("TicketNumber").value)) = lcase(cstr(sTicketNumber)) then
      Rs.Fields("fldDate") = sfldDate
      Rs.Fields("Reported_By").value = sReported_By
      Rs.Fields("Severity").value = sSeverity
      Rs.Fields("Duration").value = sDuration
      Rs.Fields("Status").value = sStatus
      Rs.Fields("ServicesAffectedID").value = sServicesAffectedID
      Rs.Fields("Problem_Description").value = sProblem_Description
      Rs.Fields("Assigned_To").value = sAssigned_To
      Rs.Fields("Action_Taken").value = "'" & Rs.Fields("Action_Taken").value & " " & dateCompleted & " " & Request.Form("UpdActionTaken") & " ' "

      rs.UpdateBatch adAffectAll
end if
end if
closecon
End Sub

itortu

ASKER

my fields Reported_by, Severity, Status, ServicesAffectedID, and Assigned_To
are drop down select list boxes.
when the page is load, the sub "LoadVars" is executed and the fields are populated with values from the database. Well, I should say that only the text fields are populated (<TD class="body"><INPUT TYPE="text" NAME="Duration" VALUE="<% Response.WRite(sDuration) %>"></TD>)
since the drop down select lists are hard coded in the page, I don't know how can I place the value from the database, so when the page is load instead of seeing "Selec on please" I see the value from the database, and then the user if neccesary can change it by selecting one of the hard coded values. I don't know if this is possible, at least not the way I am trying to do it, but if I you don't mind, would you please show me how, or explain me how can I achieve this?

Thank you very much.

<TR>
<TD class="head">Reported By</TD>
<TD class="body">
<SELECT NAME="Reported_By">
<OPTION>Select Please</Option>
<OPTION VALUE="DataQC">DataQC</Option>
<OPTION VALUE="Shannon">Shannon</Option>
<OPTION VALUE="Client Services">Client Services</Option>
<OPTION VALUE="Automated Alert">Automated Alert</Option>
<OPTION VALUE="Weather Central">Weather Central</Option>
<OPTION VALUE="Client">Client</Option>
<OPTION VALUE="Other">Other</Option>
</SELECT></TD>
</TR>
<TR>
<TD class="head">Severity</TD>
<TD class="body">
<SELECT NAME="Severity">
<OPTION>Select Please</Option>
<OPTION VALUE="Level 1 - Red" class="uno">Level 1 - Red</Option>
<OPTION VALUE="Level 2 - Orange" class="dos">Level 2 - Orange</Option>
<OPTION VALUE="Level 3 - Yellow" class="tres">Level 3 - Yellow</Option>
</SELECT>
</TD>
</TR>

amit_g

Looks ok but note that the loop in Sub LoadVars is overwritting the variables. So in the end you always get variables populated with last row fetched from the database. That might be ok if you are getting only single row from the database but even in that case you should remove the loop and just have f rs.bof = true and rs.eof = true then so that the reader of the code know what is happening.

itortu

ASKER

i got rid of the loop, but now the page does not have any of its fields populated?

this is how it looks:
Sub LoadVars
opencon

ChooseTable "tblServiceStandards"
if rs.bof = true and rs.eof = true then
if lcase(cstr(rs.fields("TicketNumber").value)) = lcase(cstr(sWhat)) then
      sTicketNumber = Rs.Fields("TicketNumber").value
      sfldDate = Rs.Fields("fldDate").value
      sReported_By = Rs.Fields("Reported_By").value
      sSeverity = Rs.Fields("Severity").value
      sDuration = Rs.Fields("Duration").value
      sStatus = Rs.Fields("Status").value
      sServicesAffectedID = Rs.Fields("ServicesAffectedID").value
      sProblem_Description = Rs.Fields("Problem_Description").value
      sAssigned_To = Rs.Fields("Assigned_To").value
      sAction_Taken = Rs.Fields("Action_Taken").value
end if
end if
closecon
End Sub

ASKER CERTIFIED SOLUTION

amit_g

membership

This solution is only available to members.

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

Start Free Trial

amit_g

>>i got rid of the loop, but now the page does not have any of its fields populated?

What is your select statement. You might have to use a where clause in it.

itortu

ASKER

for this part:

<OPTION VALUE="Level 1 - Red" class="uno" <%if WhateverVariable = "Level1" then response.write "selected"%>>Level 1 - Red</Option>

whatever variable is the name of the record set variable?

thank you.

itortu

ASKER

ok i got it it is the name of the form variable "sSeveirty" : )

itortu

ASKER

i still don't know how to get rid of the single quotes that are inserted with the update of the action taken field?'

SOLUTION

amit_g

membership

This solution is only available to members.

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

Start Free Trial