Cross Table Record Update

Question

I'll start by saying i'm inexpereinced when it comes to using
Dreamweaver and asp so any help will be appreciated.

I am trying to create an update form that updates one table
in an Access database but the variables are taken from another
table.

The table being updated is an order table with all the basic
information. The piece of information to be updated is Order
Status. In the order table this value is gotten from an Order
Status table and in this table there is only each type of status so
In Progress, Delivered etc.

I can create the update form so that when I click on an order
it redirects to an update page which has only a drop down box with
all the types of order status in. When I select the order status I
want and click update all I get is an error message saying either
the Website is under maintanence or there is a programming error. I
use the Record Update Form Wizard for this.

So i'm saying update Order Status in the Order table taking
the options of various Order Status' from the Order Status table.
Hope that makes sense.

Is there a way of doing this correctly?

WhitsterUK · Answer

I have tried to try a simpler way but I can still not get it to work. Now instead of taking the Order Status values from another table I have simply populated the OrderStatus field in the Order table with the Order Status'.

However the table still refuses to update. I have tried following tutorials such as this one: http://kb.adobe.com/selfservice/view...nalId=tn_15867 but this does not work for me. I can update simple items like the house number of the address of an order but not the order status.

Here is the code for the update page:

I'm not sure which bit of the code you need so i've c+p everything bar the basic html stuff.

<<A href="mailto:%@LANGUAGE="VBSCRIPT">%@LANGUAGE="VBS CRIPT" CODEPAGE="1252"%>

<%
' *** 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 & "?" & 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")) = "form1" And CStr(Request("MM_recordId")) <> "") Then
MM_editConnection = MM_OpsMan_STRING
MM_editTable = "[Order]"
MM_editColumn = "OrderID"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = " http://138.253.192.10/ebus211g6/oper...ptedorders.asp"
MM_fieldsStr = "OrderStatus|value"
MM_columnsStr = "OrderStatus|',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
Else
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 ordersupdate
Dim ordersupdate_numRows
Set ordersupdate = Server.CreateObject("ADODB.Recordset")
ordersupdate.ActiveConnection = MM_OpsMan_STRING
ordersupdate.Source = "SELECT * FROM [Order]"
ordersupdate.CursorType = 0
ordersupdate.CursorLocation = 2
ordersupdate.LockType = 1
ordersupdate.Open()
ordersupdate_numRows = 0
%>
<%
Dim orderstatus
Dim orderstatus_numRows
Set orderstatus = Server.CreateObject("ADODB.Recordset")
orderstatus.ActiveConnection = MM_OpsMan_STRING
orderstatus.Source = "SELECT DISTINCT OrderStatus FROM [Order]"
orderstatus.CursorType = 0
orderstatus.CursorLocation = 2
orderstatus.LockType = 1
orderstatus.Open()
orderstatus_numRows = 0
%>

This bit comes at the end:

<form method="POST" action="<%=MM_editAction%>" name="form1">
<table align="center">
<tr valign="baseline">
<td nowrap align="right">OrderStatus:</td>
<td> <select name="OrderStatus">
<%
While (NOT orderstatus.EOF)
%>
<option value="<%=(orderstatus.Fields.Item("OrderStatus"). Value)%>" <%If (Not isNull(ordersupdate.Fields.Item("OrderStatus").Val ue)) Then If (CStr(orderstatus.Fields.Item("OrderStatus").Value ) = CStr(ordersupdate.Fields.Item("OrderStatus").Value )) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(orderstatus.Fields.Item("OrderStatus").Value) %></option>
<%
orderstatus.MoveNext()
Wend
If (orderstatus.CursorType > 0) Then
orderstatus.MoveFirst
Else
orderstatus.Requery
End If
%>
</select> </td>
</tr>
<tr valign="baseline">
<td nowrap align="right"> </td>
<td> <input type="submit" value="Update Record"> </td>
</tr>
</table>
<input type="hidden" name="MM_update" value="form1">
<input type="hidden" name="MM_recordId" value="<%= ordersupdate.Fields.Item("OrderID").Value %>">
</form>
</body>
</html>
<%
ordersupdate.Close()
Set ordersupdate = Nothing
%>
<%
orderstatus.Close()
Set orderstatus = Nothing
%>

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded