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. Can anyone suggest where I have gone wrong?
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) Coke Cola --- Can ------------ Bobs Milk Bar
2) Pepsi ---------- Can ----------- Bobs Milk Bar
3) Fanta ---------- Bottle --------- Bobs Milk Bar
I have a repeat region which shows all this information in the one screen for the one supplier. When i try tu submit the form, it tries to insert all the 3 Species into the one line, and the same with the category.
Any suggestions?

the form tries to put all the data required into one line. Can anyone suggest where I have gone wrong

probably by doing this

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

but apart from that no idea;

1/ can't see how your query is concatenated.
2/ No idea what database you are using.
3/ No idea what language you are using
4/ No idea if you have it coded to use CROSS JOINs

etc etc

Any suggestions?

Yep, give us some information to work with

That would help.

The information comes in from a Query in an Access database. The code used to that that is....

<%
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
%>

The Update table and repeat region uses this....

<form ACTION="<%=MM_editAction%>" METHOD="POST" name="form1">
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<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
%>
<input name="update" type="submit" id="update" value="Update">
<input type="hidden" name="MM_update" value="form1">
<input type="hidden" name="MM_recordId" value="<%= rsResults.Fields.Item("idResults").Value %>">
</form>

This is more of the update command.

<%
' *** 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
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>

You missed out the most important bit;

what does the query look like after it has been concatenated

hint:
response.write out MM_editQuery before the call to execute.

However I would suggest that your approach is incorrect for several reasons;

Using the one form to display, edit and update several record is woefully innefficient unless you are proficient enough in coding to be able to construct a loop that will determine which records have been changed and only update those.
This of course can also be inefficient as the code has to loop through all the records where only one may be required, fine for a few dozens records but very time and resource heavy for a few hundred or more.

By all means have a single page to do the work but what you need to do is select only one record to update, then display this. When changed and the submit button is pressed (which now says "Update") detect this and update that one record "WHERE table.id = " etc.
This method probably does preclude the use of DW to write your code so you will have to get your hands "dirty".

Your table structure looks inneficient as well;
I would guess that you are using the text in your example table data as the PK - FK relationship between the linked table. This is not the way to have a normalised structure. Your tblResults should only contain the IDs of the records in the other tables. This reduces data redundancy and avoids the major problem with spelling errors when editing data (change one and they all change)

[off topic]
it's Coca Cola BTW. Coke is a different thing entirely (used to fuel furnaces in the steel industry )
[/off topic]

Thanks for the reply. 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

Microsoft JET Database Engineerror '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

It seems that it is trying to insert it all into one field.

Mally

Well as you can tell from the query output, the form is working exactly as it should and returning all the <input> elements with the same name as a comma seperated string.

You need to make it so that only 1 record (the one being edited) is returned.

Originally Posted by chrishirst

You need to make it so that only 1 record (the one being edited) is returned.

I think I am getting you here, can you go into a little more detail, please.

Mally

simplest way is to use your existing form for the display and use a radio button to select the record to edit, then when the form is submitted pick up the value of the submit button and the record ID from the radio button and just show that one record for editing.

I have a "playing" site for the catalogue/cart application I am still developing if you want to see how the principle works

http://testing.candsdesign.co.uk/

logon as
user: temp

pass: <removed >

It's not a live database and I'll drop the password in a few days

not the latest version of code so there may be the odd bug in it