Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Web Programming > Adobe Web Programming > Dreamweaver (all versions)
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Dreamweaver (all versions) section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 23rd, 2006, 06:11 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update form and Repeat Region

HI, I have created an update form which has a repeat region.

When I try to update, the form tries to put all the data required into one line. It gives me the following error.

Microsoft JET Database Engine error '80040e57'
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
/html/advertiser-basic-keyword.asp, line 154

The database consists of one table (tblResults) which has four fields.
1) idResults (AutoNumber),
2) idCategory (Text),
3) idSupplier (Text),
4) idSpecies (Text).

The three (Text) fields all have relationships with the following tables.
1) tblCategory
2) tblSupplier
3) tblSpecies

The (results) table pulls all the information together which lists 10 different species and categories for the one supplier.

Example
Speices ---------- Category ------- Supplier
1) Oak ----------- Hardwood ------- Bobs Milk Bar
2) Pine ---------- Softwood ------- Bobs Milk Bar
3) Oregon -------- Softwood ------- Bobs Milk Bar
4) etc
to
10).

I have a repeat region which shows all this information in the one screen for the one supplier. When i try to submit the form, it tries to insert all the 10 Species into the one line, and the same with the category.

Any suggestions?

I have included the result from the response.write.

update tblResults set idSpecies = 'Australian Oak, Balau, Balau, Tasmanian Oak, Jarrah, Brush Box, Meranti, Blue Gum Southern, Brush Box, Jarrah',idCategory = 'Balustrading & Corner Brackets, Cabinet Timbers, Balustrading & Corner Brackets, Decking, Cabinet Timbers, Cabinet Timbers, Cabinet Timbers, Decking, Cabinet Timbers, Balustrading & Corner Brackets' where idResults = 5

Please ask for any further info that may be required.

Mally


Reply With Quote
  #2 (permalink)  
Old May 23rd, 2006, 11:04 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Does the repeat region contain text boxes for all the items?

In that case, this is by design. If you have multiple textboxes with the same name, they be submitted as a comma separated value. So this:

<input type="text" name="txtFirstName" />
<input type="text" name="txtFirstName" />

can end up like

Imar,Mally

provided you'd type those names in the text boxes.

So, you should probably create a separate section (possibly with its own form, or even a separate page) to do the insert.

Alternatively, you could give each text box a unique name (using the recordset's primary key column for example).

Then, at the server you need to find out what row was updated, and then manually construct a proper SQL statement.

Does this help?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Reply With Quote
  #3 (permalink)  
Old May 24th, 2006, 03:35 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply Imar,. I am not understanding completly. I have included the most important code below to give you an idea on what I have done so far. Sorry re the lengthy code that DWMX creates.



<%
' *** 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_connSeek_STRING
  MM_editTable = "tblResults"
  MM_editColumn = "idResults"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "advertiser-basic-main.asp"
  MM_fieldsStr = "f1|value|select|value"
  MM_columnsStr = "idSpecies|',none,''|idCategory|',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
    'Response.write MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If End If End If %>
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Session("MM_UserName") <> "") Then
  Recordset1__MMColParam = Session("MM_UserName")
End If %>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_connSeek_STRING
Recordset1.Source = "SELECT * FROM Query1 WHERE UserName = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0 %>
<%
Dim rsCategory
Dim rsCategory_numRows
Set rsCategory = Server.CreateObject("ADODB.Recordset")
rsCategory.ActiveConnection = MM_connSeek_STRING
rsCategory.Source = "SELECT * FROM tblCategories ORDER BY CategoryTitle ASC"
rsCategory.CursorType = 0
rsCategory.CursorLocation = 2
rsCategory.LockType = 1
rsCategory.Open()
rsCategory_numRows = 0 %>
<%
Dim rsResults
Dim rsResults_numRows
Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_connSeek_STRING
rsResults.Source = "SELECT * FROM tblResults ORDER BY idSpecies ASC"
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()
rsResults_numRows = 0 %>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows %>
<form ACTION="<%=MM_editAction%>" METHOD="POST" name="form1">
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<input name="hiddenField" type="hidden">
<input name="f1" type="text" id="f13" value="<%=((Recordset1.Fields.Item("TimberSpecies" ).Value))%>" size="33">
<select name="select">
<option value="%" <%If (Not isNull((Recordset1.Fields.Item("CategoryTitle").Va lue))) Then If ("%" = CStr((Recordset1.Fields.Item("CategoryTitle").Valu e))) Then Response.Write("SELECTED") : Response.Write("")%>>Select Category</option>
<% While (NOT rsCategory.EOF) %>
<option value="<%=(rsCategory.Fields.Item("CategoryTitle") .Value)%>" <%If (Not isNull((Recordset1.Fields.Item("CategoryTitle").Va lue))) Then If (CStr(rsCategory.Fields.Item("CategoryTitle").Valu e) = CStr((Recordset1.Fields.Item("CategoryTitle").Valu e))) Then Response.Write("SELECTED") : Response.Write("")%> ><%=(rsCategory.Fields.Item("CategoryTitle").Value )%></option>
<%
rsCategory.MoveNext()
Wend
If (rsCategory.CursorType > 0) Then
rsCategory.MoveFirst
Else
rsCategory.Requery
End If %>
</select>
<% Repeat1__index=Repeat1__index+1
   Repeat1__numRows=Repeat1__numRows-1
   Recordset1.MoveNext()
Wend %>
<p><input type="submit" name="Submit" value="Submit">
<input type="hidden" name="MM_update" value="form1">
<input type="hidden" name="MM_recordId" value="<%= rsResults.Fields.Item("idResults").Value %>"></form>
Reply With Quote
  #4 (permalink)  
Old May 24th, 2006, 12:38 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Yeah, Dreamweaver is pretty good at creating loads of code.... ;)

Anyway, it seems that each row / record in the repeating region is editable, right? I see stuff like <input type="text" /> in the region which means that each item is editable.

I don't think that's a good idea. You'll need to write a lot of code yourself to make that work, trying to figure out what text box belongs to what primary ID and then construct the SQL statement manually.

Is it an option, instead of editing all items at the same time, to just limit it to one? For example, each item could have an Edit link that sends you to an Add/Update page that allows you to change a single item at a time. Much easier to implement, and it'll result in cleaner and easier to understand code....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Reply With Quote
  #5 (permalink)  
Old May 25th, 2006, 02:32 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, just as it was in one of the books. That might be easier. I will try. One of my friends sent this as a solution.

"You could simply add that to the end of the field name:
<input name="myfield<%=Repeat1__index%>" type="text">"

Do you think that it would be a method to try or would be too difficult?

Thanks

Mally
Reply With Quote
  #6 (permalink)  
Old May 25th, 2006, 03:39 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Yeah, that could work, and is exactly what I meant with this:
Quote:
quote:Alternatively, you could give each text box a unique name (using the recordset's primary key column for example).
However, using this method, you give each row a unique ID but that's not enough.

When you click the submit button, you'll need to loop through all your items, find the appropriate controls and reconstruct the SQL statement.

E.g.

For i = 0 To 5
  UserName = Request.Form("txtUserName" & i)
Next

This code gets the user name from a field called txtUserName with has the record ID or index appended to it.

It's a lot more work this way (compared to a master / detail page that allows you to edit a single record) but when your requirements say you need to be able to edit multiple rows at the same time, this would be the way to do it.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Reply With Quote
  #7 (permalink)  
Old May 26th, 2006, 07:33 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey there, so close yet so far to getting this working. :(....

I have created the main page which shows all the results in the database. The results are pulled together using a repeat region. Next to each result I have an edit button which I used the go to detail page function. When you click on to edit, it takes you to another page which shows the one result, with a text field for editing. When you click onto the submit button to make your changes, it deletes the entry. So instead of showing all 10 results, it shows 9. If you continue to edit, it just deletes all entries, one by one.

Is there anything that you can suggest?

If required, what code would you require to help.

Thanks.

Sorry, Mally.
Reply With Quote
  #8 (permalink)  
Old May 26th, 2006, 07:38 AM
Authorized User
 
Join Date: Aug 2005
Location: , , .
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried to do a response.write on the page that edits, but it will not work...

  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
Response.write MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If
End If
Reply With Quote
  #9 (permalink)  
Old May 26th, 2006, 03:18 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

If you want to display the query, you need to add an additional Response.End statement, after Response.Write.

Otherwise, the query will still be executed, and you're still redirected away:

Response.write MM_editQuery
Response.End()

I am not sure I understood the message before your last one, so if you're still having problems, can you describe them in some more detail?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort a repeat region hendricksonet VBScript 0 August 11th, 2005 01:55 PM
hyperlink in repeat region recordset stephanvh Dreamweaver (all versions) 3 June 16th, 2005 01:05 PM
Repeat Region with a difference. fatmcgav Dreamweaver (all versions) 2 January 17th, 2005 05:00 PM
Region? JAtkinson Visual C++ 0 May 17th, 2004 07:02 AM
Repeat region, I cannot change the 'show records' karib Dreamweaver (all versions) 3 May 6th, 2004 03:27 PM



All times are GMT -4. The time now is 06:41 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.