Hello! Have found where the Update behavior needs to be modified to give it a variable containing the column. Hopefully this is the correct part of the code but not sure of the syntax to use to get this to work.

The column name is in a session variable which could be put into a local variable if necessary.

Here is the statement that needed to be changed.

MM_columnsStr = "Week17|',none,''|DateUpdated|',none,NULL"

Would like to change the value of column name Week17 above to a session variable name which would resolve to a column name for any week.

The session variable is svWeekNumber which was created and valued at logon. How could this be put into the above statement so that it will resolve to Week1 or whatever so that the rest of the Update function will work correctly?

Possibly a better place to change the code is after the split function which will put all Update column names in an array to build the SQL statement needed but not sure yet.


The full code block generated from the UD4 Update behavior follows.
Open Percent
' *** Update Record: set variables

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

MM_editConnection = MM_FPoolconn_STRING
MM_editTable = "Losers_Table"
MM_editColumn = "LoserID"
MM_recordId = "" + Request.Form("MM_recordId" + ""
MM_editRedirectUrl = "lose_change_weeklypick_confirm.asp"
MM_fieldsStr = "select|value|Date Updated|value"
MM_columnsStr = "Week17|',none,''|DateUpdated|',none,NULL"

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

' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(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
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If

End If
Close Percent
Open Percent
' *** 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 i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),","
Delim = MM_typeArray(0)
If (Delim = "none" Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none" Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none" Then EmptyVal = ""
If (FormVal = "" Then
FormVal = EmptyVal
Else
If (AltVal <> "" Then
FormVal = AltVal
ElseIf (Delim = "'" Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''" & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(i) & " = " & FormVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

' Was using this session variables to check
' the values created and sent to the redirect page

Session("svHoldsql" = MM_editQuery
Session("svHoldeditcolumn"=MM_editColumn


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
Close Percent

Would appreciate any recommendations or help. Thanks!

Well finally got this thing to work!

Here are the changes made to the Update code and also to the initial recordset in Advanced SQL mode.

For the Update, created a local variable which takes the value of the Session variable that contains the column name for a particular week.


' *** Update Record: set variables
Dim holdsvWN
holdsvWN=(Session("svWeekNumber")


Then changed the MM_columnsStr variable from the UD4 generated code to now concatenate the new local variable.


' MM_columnsStr = "Week17|',none,''|DateUpdated|',none,NULL"

The revised code follows.

MM_columnsStr = holdsvWN & "|',none,''|DateUpdated|',none,NULL"


Now for this to be complete dynamic the original recordset created via the Recordset server behavior had to be changed to read in the new local variable containing the weekly column name.

While in the Advanced SQL mode, change the previously hardcoded column name of Week17 to holdsvWN, the local variable.

Then, in the variables section, set holdsvWN with a default value of 99 and the run time value is Session("svWeekNumber" to pick up the value from the Session variable.

The new recordset code that UD4 generated follws with the Source statement the one containing the changes.

set rsUpdLose = Server.CreateObject("ADODB.Recordset"
rsUpdLose.ActiveConnection = MM_FPoolconn_STRING
rsUpdLose.Source = "SELECT UserName, LoserID, " + Replace(rsUpdLose__holdsvWN, "'", "''" + " FROM Losers_Table WHERE UserName = '" + Replace(rsUpdLose__MMColParam, "'", "''" + "'"
rsUpdLose.CursorType = 0
rsUpdLose.CursorLocation = 2
rsUpdLose.LockType = 3
rsUpdLose.Open()
rsUpdLose_numRows = 0


This works like a charm and now allows totally dynamic column names to be added to the code that UD4 produces.

No red exclamation point was displayed by the Recordset or Update behaviors dialog box as well.

The only drawback is that you cannot open the Recordset on the Databindings tab as an error with too few parameters is displayed probably because the variable column name is not on the table.

However, if you bind your recordset fields before changing the generated recordset code to include the variable column, then you can easily change any code to display any data binding variables on a page.

Not sure what other types of application could use dynamically built column names but a football pool where a different week number column must be updated each week works well.

This now allows for one update page instead of a separate update page for each week containing hardcoded week numbers.

Hope somebody finds this helpful. Thanks!