ADD MULTIPLE RECORDS !!!!!

[jedel]

OK, I am at the end my rope here. I have read everything I can find on this and I cannot get anything to work. Please don't send me to Dr Dev's page, its the last thing I tried and the code that I'm presenting with the problem.

Firstly, here is the page

http://www.calvarycc.org.au/admin/knect/mbr/MbrAttendance.asp

It seems to process the code, but nothing is entered into the second table.

Here is the INSERT code:

<%
If request("button")<>"" Then
    	
   intRecIDs = Replace(Request("hidRecIDs"), "*", "")
   arrRecIDs = Split(intRecIDs, ", ")
   For i = 0 to Ubound(arrRecIDs)
      sSurname = Replace(Request("Surname" & arrRecIDs(i)), "'", "''")
      sFirstname = Replace(Request("FirstName" & arrRecIDs(i)), "'", "''")
	  sEmail = Replace(Request("Email" & arrRecIDs(i)), "'", "''")
	  sPh = Replace(Request("Ph" & arrRecIDs(i)), "'", "''")
	  sMob = Replace(Request("Mob" & arrRecIDs(i)), "'", "''")
	  sMDate = Replace(Request("Meet_Date" & arrRecIDs(i)), "'", "''")
	  sGp = Replace(Request("GroupID" & arrRecIDs(i)), "'", "''")
	  sAttend = Replace(Request("Attended" & arrRecIDs(i)), "'", "''")
      Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
        MM_editCmd.ActiveConnection = MM_connect_STRING
      MM_editCmd.CommandText = "INSERT INTO attendanceTBL (Surname, Firstname, Email, Ph, Mob, Attended, Meet_Date, GroupID) VALUES"_
          & sSurname & "', '" & sFirstname & "', '" & sEmail & "', '" & sPh & "', '" & sMob & "', '"& sAttend & "', '" & sMDate & "', " & sGp & " WHERE RecID = " & arrRecIDs(i)
      commUpdate.CommandType = 1
      commUpdate.CommandTimeout = 0
      commUpdate.Prepared = true
      commUpdate.Execute()
   Next
   strMessage = i & " Records Updated"
   Response.Redirect("MbrAttendanceSubmit.asp?Message=" & strMessage)
End If

    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "MbrAttendanceSubmit.asp"
    If (Request.QueryString <> "") Then
      If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
        MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
      Else
        MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
      End If
     Response.Redirect(MM_editRedirectUrl)
  End If
%>

HERE IS THE FORM:

 <form id="AddMbrFRM" name="AddMbrFRM" method="POST" action="MbrAttendance.asp">
      <span class="normalTxtnobox">      </span>
      <table width="642" border="0" cellspacing="2" cellpadding="0">
        <tr>
          <td width="21" class="heading">&nbsp;</td>
          <td width="91" class="heading">Surname</td>
          <td width="90" class="heading">First Name</td>
          <td width="180" class="heading">email</td>
          <td width="72" class="heading">Phone</td>
          <td width="72" class="heading">Mobile</td>
          <td width="100" class="heading">Attended</td>
        </tr>
        <% 
While ((Repeat1__numRows <> 0) AND (NOT knectMbr.EOF)) 
%>
<% if Request("button")<>"" Then %>

<% End If%>
          <tr>
          <% RecID =(knectMbr.Fields.Item("mbr_ID").Value) ' Store the current RecordID in a variable %>
	  <%=RecID%>
      
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="HidRecID<%=RecID%>" type="hidden" value="<%=RecID%>" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><label class="normalTxtnobox">
              <input name="Surname<%=RecID%>" type="text" id="Surname" value="<%=(knectMbr.Fields.Item("mbr_Surname").Value)%>" size="15" />
            </label></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Firstname<%=RecID%>" type="text" id="Firstname" value="<%=(knectMbr.Fields.Item("mbr_Firstname").Value)%>" size="15" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Email<%=RecID%>" type="text" id="Email" value="<%=(knectMbr.Fields.Item("mbr_email").Value)%>" size="30" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Ph<%=RecID%>" type="text" id="Ph" value="<%=(knectMbr.Fields.Item("mbr_Ph").Value)%>" size="12" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Mob<%=RecID%>" type="text" id="Mob" value="<%=(knectMbr.Fields.Item("mbr_Mob").Value)%>" size="12" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><label>
              <select name="Attended<%=RecID%>"  class="normalTxtnobox" id="Attended">
                <option value="Yes">Yes</option>
                <option value="No">No</option>
              </select>
              <input name="Meet_Date<%=RecID%>" type="hidden" id="Meet_Date" value="<%=Date() %>" size="12" />
              <input name="GroupID<%=RecID%>" type="hidden" id="GroupID" value="11" />
            </label></td>
          </tr>
          
          <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  knectMbr.MoveNext()
Wend
%><tr>
            <td colspan="7" align="right" bgcolor="#E2E2E2" class="normalTxtnobox"><label>
              <input type="submit" name="button" id="button" value="Submit" />
            </label></td>
          </tr>
            </table>
    
      
      </form>

Like I said. I have look and looked and looked and while I can edit multiple records and add multiple records to the same table, I cannot add mulitple records to the second table.

THE TABLE STRUCTURE
is the same in both tables. the dates are dates the numbers are numbers and the text is text across both tables.

I want to add the following fields to their corresponding fields in the second table:

Surname -- Surname - (text)
FirstNAme -- Firstname - (text)
Email - Email (text)
Ph -- Ph (text)
Mob -- Mob (text)
GroupID--GroupID (number)
Meet_date -- Meet_Date (Date)
Attended -- Attended (Text)

I have had errors where the code say the variables are of the wrong type, Buffer length too short, expected parameters....I could go on, and on. Frankly, I'm begining to think it cannot be done.

If anybody here can help it. please take me through this STEP - BY - STEP so I can learn and understand why this code is not working.

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[lebisol]

certianly frustrating when something is not working...dr dev site is great resource so I am not sure why reject it.
Being how there is no PM on these forums here is a TEMP link that will be removed.
hope it helps.

:-------------------------------------:
Do the DW »|MostarNetworks|

 

[jedel]

lebisol,

Great work mate! There are some things on this SWF file that Dr Dev did not detail or I must have skipped over and that was to type in the Value names from the variables.

I'll go and have yet another play and see if I can get it to work. Mind you, I will want to do this with multiple records.

We'll see how we go.

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jedel]

OK,

I used your method, which is fine for one record at a time, however it does not work for multiple records.

Went back to DR Dev and tried to manipulate the code a little. I have managed to insert multiple records, but there is not data in the fields, its just 9 records of nothing but the primary key.

So, I went back to Dr Dev...again and I think I may have. Problem now is i have the SQL string but the page keeps saying I'm missing a semicolon. When I try and place at the end of the SQL Statement, it says it expected the end of the statement or a ")". SO, where and how do I place the semicolon in the code below?

<%
If Request("button")<>"" Then
intRecID = Replace(Request("hidRecID"), "*", "")
   arrRecIDs = Split(intRecID, ", ")
   For i = 0 to Ubound(arrRecIDs)
   	SN = Replace(Request("Surname" & arrRecIDs(i)), "'", "''")
    FN = Replace(Request("Firstname" & arrRecIDs(i)), "'", "''")
	EM = Replace(Request("Email" & arrRecIDs(i)), "'", "''")
    PH = Replace(Request("Ph" & arrRecIDs(i)), "'", "''")
	MOB = Replace(Request("Mob" & arrRecIDs(i)), "'", "''")
    GP = Replace(Request("GroupID" & arrRecIDs(i)), "'", "''")
	DTE = Replace(Request("Meet_Date" & arrRecIDs(i)), "'", "''")
    ATT = Replace(Request("Attended" & arrRecIDs(i)), "'", "''")
	
Set MbrIns = Server.CreateObject ("ADODB.Command")
MbrIns.ActiveConnection = MM_connect_STRING
MbrIns.CommandText = "INSERT INTO attendanceTBL (Surname, Firstname, Email, Ph, Mob, GroupID, Meet_Date, Attended)  VALUES ('" & SN & "','" & FN & "','" & EM & "','" & PH & "','" & MOB & "','" & GP & "','" & DTE & "','" & ATT & "') WHERE intRecID = " & arrRecIDs(i)

MbrIns.CommandType = 1
MbrIns.CommandTimeout = 0
MbrIns.Prepared = true
MbrIns.Execute()
Next
End IF
%>

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[lebisol]

Oh man it has been so long since I did any ASP and tut. above was more of a joke for a friend of mine years ago...hence the speed of tutorial and the voice .

The best I can see of hand would be:
WHERE intRecID = " & arrRecIDs(i) &"

But not sure....as long as you only dealing with missing statements you are almost there. Sorry man, really long time since I hand coded any asp.
Good luck!


:-------------------------------------:
Do the DW »|MostarNetworks|

 

[johnwm]

lebisol - That would leave an unpaired quote.

jedel - An Insert statement doesn't take a Where clause in any version of SQL that I'm aware of.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[jedel]

Hey Guys, Thanks for your input.

johnwm,

I did remove the WHERE clause from the SQL and it all started working. Actually, I found that that a number of methods worked to a point, but they did not add any data into the fields.

lebisol,

Excusing the voise and the language, you tutorial shed a lot of light on using Dreamweaver.

Guys, I'm going to add an FAQ on this detailing the steps and the links that got me through. There is nothing anywhere that details this particular form of multiple records. I'll plonk it into the DW Ultra Dev area I think and place a link to it at the end of this post so people will know where to go.

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[lebisol]

Cool, perhaps it would be worth re-doing the tutorial on my end as well...I could certianly use the refresh on asp. Hard to keep up when ur not doing this day after day.
I should have cought the WERE as John pointed out.
In any case, if do write it up jedel consider linking back from this thread.
All the best!

:-------------------------------------:
Do the DW »|MostarNetworks|

 

[jedel]

Happy to,

Anything to get some progress and helpful code on this. I've just finished the FAQ. I'll dress it up a little. but the content is here;

faq770-6952

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."