PDA

View Full Version : Update Record - Keeping Hold of Variables


emmagale
08-03-2005, 01:05 PM
I have a problem with the UPDATE RECORD server behaviour this is passing variables to the next page.

The link is:
http://www.s114085044.websitehome.co.uk/Ri...ister/logon.asp ('http://www.s114085044.websitehome.co.uk/RiskRegister/logon.asp')
username: manager password: manager department code: 382

If you go to risks and select the first risk and then select "view actions". Choose the first action on that list which takes you into the detail page where you can modify the current data and press the submit button. What is passed back to the list of actions in the URL, is the department, risk id and action id. I want only the department and risk ID to be saved in the URL - because as you can see if you click another action an error is caused by two IDs being passed to the next page.

I think there are two places which might be the culprit and it is more likely the second:

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

or

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If

I think that it has something to do with the second and third "MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString" above because I think the URL variables are being added to the URL plus next variable. I have fiddled withe the code though and Im not having much luck. Im not a competent programmer by the way.

I hope the above is clear and would be really grateul if someone could take a look and help me get this problem solved.
With best wishes
emma

ranjan
08-03-2005, 03:53 PM
The problem is not on this page, i think. The problem is that the update page (masterActionDetail.asp) is sending back "id". Please post back the code of masterActionDetail.asp

emmagale
08-03-2005, 04:04 PM
Oh ok. Thanks for that. Here is the code:
Kind regards
emma

<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd
Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId
Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Update Record: set variables

If (CStr(Request("MM_update")) = "actionDetail" And CStr(Request("MM_recordId")) <> "") Then

MM_editConnection = MM_conRisk_STRING
MM_editTable = "[Action Unit]"
MM_editColumn = "ID"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = "masterActionList.asp"
MM_fieldsStr = "action|value|status|value|controlMeasures|value|ac tionCompleted|value|DirectorProgressReport|value|D irectorProgressDate|value|SeniorManagerProgressRep ort|value|SeniorManagerDate|value|HODProgressRepor t|value|Comments|value"
MM_columnsStr = "Action|',none,''|StatusID|none,none,NULL|Controlme asuresID|none,none,NULL|Dateactioncompleted|',none ,NULL|DirectorProgressReport|',none,''|DirectorPro gressDate|',none,NULL|SeniorManagerProgressReport| ',none,''|SeniorManagerprogressLastdate|',none,NUL L|HODProgressReport|',none,''|Comments|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
End If
End If
End If
%>
<%
' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then

' create the sql update statement
MM_editQuery = "update " & MM_editTable & " set "
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If

End If
%>
<%
Dim rsControl
Dim rsControl_numRows

Set rsControl = Server.CreateObject("ADODB.Recordset")
rsControl.ActiveConnection = MM_conRisk_STRING
rsControl.Source = "SELECT * FROM [Control Measures]"
rsControl.CursorType = 0
rsControl.CursorLocation = 2
rsControl.LockType = 1
rsControl.Open()

rsControl_numRows = 0
%>
<%
Dim rsAction__firstvariable
rsAction__firstvariable = "1"
If (Request.QueryString("RIDUnit") <> "") Then
rsAction__firstvariable = Request.QueryString("RIDUnit")
End If
%>
<%
Dim rsAction__secondvariable
rsAction__secondvariable = "1"
If (Request.QueryString("ID") <> "") Then
rsAction__secondvariable = Request.QueryString("ID")
End If
%>
<%
Dim rsAction
Dim rsAction_numRows

Set rsAction = Server.CreateObject("ADODB.Recordset")
rsAction.ActiveConnection = MM_conRisk_STRING
rsAction.Source = "SELECT * FROM [Risk Unit] LEFT JOIN [Action Unit] ON [Risk Unit].RIDUnit = [Action Unit].RIDUnit WHERE [Risk Unit].RIDUnit = " + Replace(rsAction__firstvariable, "'", "''") + " AND [Action Unit].ID= " + Replace(rsAction__secondvariable, "'", "''") + ""
rsAction.CursorType = 0
rsAction.CursorLocation = 2
rsAction.LockType = 1
rsAction.Open()

rsAction_numRows = 0
%>
<%
Dim rsStatus
Dim rsStatus_numRows

Set rsStatus = Server.CreateObject("ADODB.Recordset")
rsStatus.ActiveConnection = MM_conRisk_STRING
rsStatus.Source = "SELECT * FROM Status"
rsStatus.CursorType = 0
rsStatus.CursorLocation = 2
rsStatus.LockType = 1
rsStatus.Open()

rsStatus_numRows = 0
%>

ranjan
08-04-2005, 01:07 AM
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString

The above line in the code sends the whole Querystring back. What you need to do is use string functions to remove &id=xxx from this querystring before its sent back using split fnction

http://www.learnasp.com/learn/stringsplit.asp ('http://www.learnasp.com/learn/stringsplit.asp')

emmagale
08-04-2005, 01:27 PM
Thank you very much for your reply and your tutorial.

I apologise for appearing to be extremely thick an I kind of understand what needs to be done from the tutorial and Ive looked at another in an attempt to make it a little clearer .

Its just that I am not a programmer and dont really know where to start.

Can you help me with this?

Kind regards
Emma

ranjan
08-04-2005, 07:38 PM
A URL with query string looks like this

anypage.asp?id=1&xyz=3&abs=2

Your problem is that id gets repeated and needs to be stripped

anypage.asp?id=1&xyz=3&abs=2&id=1

To strip the extra "id" being passed we will have to split the URL and rebuild it without the extra id.

FIrst split the string into 2 the URL and the parameters, seperated by thr "?"

totURL = split(Request.Querystring,"?")

Now "totURL" is an array containing 2 strings

theURL = totURL[1]
theParams = totURL[2]

Now the Params can be further split into an array of parameters seperated by &

theParams = split(theParams,"&")

Now we need to remove duplicated in this array

theParams = RemDups(theParams)

No RemDups is not a standard asp function we have to code it

<%
Function RemDups(ByVal anArray)
Dim d, item, thekeys

Set d = CreateObject("Scripting.Dictionary")
d.removeall
d.CompareMode = 0
For Each item In anArray
If Not d.Exists(item) Then d.Add item, item
Next
thekeys = d.keys
Set d = Nothing
RemDups = thekeys
End Function
%>

Now to reassemble the URL first we reassemble the params

newParams = join(theParams,"&")

Now the last part is to add the URL and the Params

Request.QueryString = theURL + newParams

Thats it. If you stick the above code on top of the document it will convert the uerystring to strip extra id passed


PLS: this is completely extempore, so there may be mistakes, if there are google for the coding errors and try fixing it, if you cannot post here for more help

emmagale
08-05-2005, 11:01 AM
Thank you for your help.

Ok so I have created the function RemDups and placed this in the code above the HTML in its own <% .... %>

<%
Function RemDups(ByVal anArray)
Dim d, item, theKeys

set d = CreateObject("Scripting.Dictionary")
d.removeall
d.compareMode = 0
For Each item in anArray
If Not d.Exists(item) Then d.Add item, item
Next
thekeys = d.keys
set d = Nothing
RemDups = theKeys
End Function
%>

Then I have created the code which creates the arrays and rebuilds also in its own <% .... %> below the function. Do I have to put the code above the HTML in any particular order?

<%
'split the URL string
wholeURL = split(Request.QueryString, "?")
'define the array records
theURL = wholeURL[1]
theParams = wholeURL[2]
'split the params
theParams = split(theParams,"&")
theParams = RemDups(theParams)
'reassemble the URL
newParams = join(theParams, "&")
Request.QueryString = theURL + newParams
%>

emmagale
08-05-2005, 11:04 AM
Posted the above before I had finished!

Do i have to change any of the variables within the code to fit with my page? for instance do i have to know what the name of the QueryString is or the fact that QueryString is being called will be enough?

I currently have an error referring to the array which I am googling for

Expected end of statement
/masterActionDetail.asp, line 88, column 17
theURL = wholeURL[1]

But if you know easily what the error could be I would be grateful to know.

I hope I am following your instructions properly. If I am then I thinki understand what your are trying to tell me.

with best wishes
emma

emmagale
08-05-2005, 01:17 PM
Ok I have been doing a little bit of testing and I know that the array is correct becuase I am writing

<% response.write isarray(totURL) %>

comes back as true.

I have commented out all the remaining code but when i uncomment the next line

theURL = wholeURL[1]

this produces an error.

Am I missing something here?

kind regards
emma

ranjan
08-05-2005, 01:26 PM
theURL = wholeURL[1]

should be

theURL = wholeURL(1)

and the [2] should be (2)

Nothing else need to be changed

ranjan
08-05-2005, 01:33 PM
PS; forgot to mention that the code shoudl be on the top of the page

above

<%
' *** Edit Operations: declare variables

emmagale
08-05-2005, 01:42 PM
Thankyou for your reply.

I did try the () rather than [ ] but it came up with the following error:

Microsoft VBScript runtime (0x800A0009)
Subscript out of range: '[number: 1]'
/masterActionDetail.asp, line 25

I googled for this and it came back with the suggestion: The 'subscript out of range' error means that the index (1) is higher than the upper bound of the array -

Then i thought - ahah - i remembered somewhere about arrays starting at 0 and so i changed the code to:

theURL = wholeURL[0]
theParams = wholeURL[1]

But this produces the same error. any ideas?

I really do appreciate you helping me.
Kind regards
emma

emmagale
08-05-2005, 02:43 PM
For anyone else who may be interested running through the problem, I have managed to solve it.

Thank you for your assitance though RanJan, Creating the array gave me a good starting point though. This is how i solved it in the end:

<%
'split the URL string
totURL=split(Request.QueryString, "&")
'define the array records
newURL = totURL(0) & "&" & totURL(1)
QueryString = newURL
%>

within the original update record:

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And newURL <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And newURL <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & newURL