logo-dw

Go Back   Dreamweaver Club Forums > Hand Coders Forum > ASP
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 04-25-2008, 01:00 AM   #1
Whitster
 
Join Date: Apr 2008
Posts: 2
Default Cross Table Record Update

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?
Whitster is offline   Reply With Quote
Old 04-25-2008, 04:21 AM   #2
mangofreak
mangofreak's Avatar
 
Join Date: Jan 2006
Location: Toronto-Canada
Posts: 444
Default

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.
__________________
J.
DW | FW | HTML | CSS | ASP | some PHP | Windows | Ubuntu
mangofreak is offline   Reply With Quote
Old 04-27-2008, 01:34 PM   #3
Whitster
 
Join Date: Apr 2008
Posts: 2
Default

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.

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.
Code:
<<A href="mailto:%@LANGUAGE="VBSCRIPT">%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#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 = "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:

Code:
 <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>
          <%
  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">&nbsp;</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
%>
Whitster is offline   Reply With Quote
Old 05-03-2008, 02:50 AM   #4
mangofreak
mangofreak's Avatar
 
Join Date: Jan 2006
Location: Toronto-Canada
Posts: 444
Default

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:
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.

Cheers.
__________________
J.
DW | FW | HTML | CSS | ASP | some PHP | Windows | Ubuntu
mangofreak is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 10:36 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com