Inserting more than one record from form

[apex82]

I have a form where I select from a dropdown then add a two digit code to a input box then press submit.

The submit then fires the form to a page which updates a SQL 2008 database table with the one record.

What I'd like to do is be able to add more than one "two digit code" to the input box which would in turn add more than one record. Perhaps separate the codes via a space?

E.G: If I typed in: AA BB CC. I would then get three records.

Is this possible?

Here is my working code for adding one value:

Form:

<form name="add" method="POST" action="add.asp">
  <table width="640"  border="0" cellspacing="10" cellpadding="0">
    <tr>
      <td width="200">Choose :</td>
      <td width="172">Enter Code(s):</td>
      <td width="228"> </td>
    </tr>
    <tr>
      <td width="200"><select name="Unit" id="Unit">
          <%
		SQL = "SELECT Unit.Unit_ID, Unit.[Unit code], Unit.[Unit description], Business.Business_ID, Business.[Business Code], Business.[Business description]  FROM Business " & _
       "LEFT JOIN Unit ON Business.UnitID = Unit.Unit_ID "& _
       "ORDER BY Unit.[Unit Code], Business.[Business code]"
	
		Set rs = obj_CN.Execute(SQL, adBoolean)

		nUnitID = 0

		do while not rs.EOF

        str_UnitID = rs("Unit_ID")
        str_Unit = rs("Unit code")
		str_UnitDescription = rs("Unit description")

		str_BusinessID = rs("Business_ID")
		str_BusinessCode = rs("Business code")
		str_BusinessDescription = rs("Business description")
        
    	if nUnitID <> str_UnitID then
        	if nUnitID <> -1 then
        	Response.Write "</optgroup>"
        	end if
		
        Response.Write "<optgroup label=""" & str_Unit & """>"
        nUnitID = str_UnitID
        end if
		
        Response.Write "<option value=""" & str_BusinessID & """#Selected#>" & str_BusinessCode & " - " & str_BusinessDescription &  "</option>"
				
        rs.MoveNext
		loop
%>
        </select>
      </td>
      <td width="172"><input type="text" name="SystemCode" id="SystemCode"></td>
      <td width="228"><input type="submit" name="Submit" value="Submit" ></td>
    </tr>
  </table>
</form>

Update/add record page:

<%

SQL = "INSERT INTO System ([BusinessID], [System Code], [System Description] )" 
SQL = SQL & " VALUES "
SQL =SQL & "('" & Request.Form("Unit") & "', '" & Request.Form("SystemCode") & "', '" & "System Code " & Request.Form("SystemCode") & "')"

Set rs = obj_CN.Execute(SQL, adBoolean)

%>

 

[foxbox]

TEST2.ASP:

<%
if request.ServerVariables("REQUEST_METHOD") = "POST" then

  For a = 1 to Request.Form("unit").Count
    cSQL = "INSERT INTO System ([BusinessID], [System Code], [System Description] ) VALUES(" & _ 
           "('" & Request.Form("Unit")(a) & "', '" & Request.Form("SystemCode") & "', '" & "System Code " & Request.Form("SystemCode") & "')"   
           response.write cSQL & "<br>"
   ' obj_CN.Execute(SQL, adBoolean)
  Next
  response.End
end if
%>

<form name="add" method="POST" action="test2.asp">
  <table width="640"  border="0" cellspacing="10" cellpadding="0">
    <tr>
      <td width="200">Choose :</td>
      <td width="172">Enter Code(s):</td>
      <td width="228"> </td>
    </tr>
    <tr>
      <td width="200">
      <select name="Unit" multiple>
      <option>AA</option>
      <option>BB</option>
      <option>CC</option>
      <option>DD</option>
      </select>
      </td>
      <td width="172"><input type="text" name="SystemCode" id="SystemCode"></td>
      <td width="228"><input type="submit" name="Submit" value="Submit" ></td>
    </tr>
  </table>
</form>

 

[apex82]

Thanks Foxbox.

I must be going wrong somewhere because I am getting the following error?
String or binary data would be truncated.

The form select="unit" will only have one value never multiple values, does that alter anything?

Here is my altered code:

<%
if request.ServerVariables("REQUEST_METHOD") = "POST" then

  For a = 1 to Request.Form("Unit").Count
    SQL = "INSERT INTO System ([BusinessID], [System Code], [System Description] ) VALUES " & _ 
           "('" & Request.Form("Unit")(a) & "', '" & Request.Form("SystemCode") & "', '" & "System Code " & Request.Form("SystemCode") & "')"   
	
	Set obj_RS1 = obj_CN.Execute(SQL, adBoolean)

  Next
  Response.End
  end if

%>

 

[apex82]

Having said the above, I am able to add one code i.e AA.

I only get the error when adding more than one code. i.e AA EE.

 

[foxbox]

String or binary data would be truncated." means you are trying to fill a database field with something that is too long. Most likely the "SystemCode" field, which has no "size=".


"Inserting more than one record from form" +
"E.G: If I typed in: AA BB CC. I would then get three records."

gave me the idea that your user must be able to multi-click codes AA, BB en CC in a dropdown list, which must result in 3 records.

remark the execute and put the response.write SQL
back. Then you see eacht SQL statement. Copy/pAste them into SQL and see what happens..

What do you mean with:
"The form select="unit" will only have one value never multiple values, does that alter anything?"

????!!!!
That alters everthing!
But where do the AA, BB, CC values come from?

 

[apex82]

But where do the AA, BB, CC values come from?

I would want to be able to add these values into the input box so I would be typing more than one "two digit code" to the input box. e.g. AA BB CC. Whilst only selecting one value from the dropdown.

 

[foxbox]

<html>
<head>
<script language="javascript">

    function FillText() {
        var selObj = document.getElementById('select1');
        var txtObj = document.getElementById('text1');

        var selIndex = selObj.selectedIndex;
        var txt = txtObj.value;
        txtObj.value = txt + selObj.options[selIndex].text;

        
        
     }
    
</script>

</head>
<body>

<form id=frm1 name=frm1>
<select name="select1" id="select1" onchange="FillText();">
<option value="AA">AA</option>
<option value="BB">BB</option>
<option value="CC">CC</option>
<option value="DD">DD</option>
</select>
<input type="text" id="text1" name="text1" />
</form>



</body>
</html>

 

[apex82]

Thanks foxbox.

This is it:

input = split(Request.Form("SystemCode")," ")

For i = 0 to Ubound(input)
	Response.Write "Inputs are -"& input(i)
Next

For i = 0 to Ubound(input)
	Response.Write "Inputs are -"& input(i)
	sSQL = "INSERT INTO System ([BusinessID], [System Code], [System Description] )" 
	sSQL = sSQL & " INPUTUES "
	sSQL = sSQL & "('" & Request.Form("Unit") & "', '" & input(i) & "', '"  & "System Code " & input(i) & "')"

	Set rs = obj_CN.Execute(sSQL, adBoolean)
Next