View Full Version : Cross Table Record Update

04-25-2008, 12:00 AM
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.

Is there a way of doing this correctly?

04-25-2008, 03:21 AM
Can you show your code?

1. you have 2 tables, right?

tblOne = here you have the status values

tblTwo = in here you have the other info.

if you want to update tblOne, you don't need to make an Insert or update on tblTwo. All you need is to create a recordset calling tblOne: i.e.

Select *
From TblOne
Order by Name ASC

then use your wizard and create your update form.

If you want to update tblTwo, where I imagine you have a foreign key from tblOne then it would be something like:

Select item1, item2, item3, statusID1, statusID, statusname
From tblOne, tblTwo
Where statusID1 = statusID

then use the wizard and create your update from.

hope it helps. However, you should always provide more details and your code for us to be able to help better.

04-27-2008, 12:34 PM
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/viewContent.do?externalId=tn_15867 but this does not work for me.

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.

<!--#include file="../Connections/OpsMan.asp" -->
' *** 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 = ""
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)))
' 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
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
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
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
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
If (MM_editRedirectUrl <> "") Then
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_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_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").Value)) 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>
If (orderstatus.CursorType > 0) Then
End If
</select> </td>
<tr valign="baseline">
<td nowrap align="right">&nbsp;</td>
<td> <input type="submit" value="Update Record"> </td>
<input type="hidden" name="MM_update" value="form1">
<input type="hidden" name="MM_recordId" value="<%= ordersupdate.Fields.Item("OrderID").Value %>">
Set ordersupdate = Nothing
Set orderstatus = Nothing

05-03-2008, 01:50 AM
I don't know if you have solved your problem. I have been too busy therefore my absence. I was looking at your code and I'm intrigued by the brackets you have in you code:

MM_editTable = "[Order]"...

ordersupdate.Source = "SELECT * FROM [Order]"

I have tried recreating the bracket using DM8 and any recorset I create doesn't have any square bracket. How did you get this? If you don't know, why don't you try removing those brackets and see wehat happens.

I try being around more often.