HOW TO INSERT MULTIPLE RECORDS IN ONE TABLE

[Chrisma]

hi guys,

I need some one to help me on how to insert multiple records in a table. I used multiple selct list box where in the user are allowed to choices as many as he/she can.
my problem is if it is multiple records it did not add to my table but is doesn't appear any error message.

here is my code


sql = "SELECT * FROM TRAINING WHERE STAFF_SK = "&session("staffsk")&" AND COURSE_NAME = '"&sqlsafe(course)&"' AND BRANCH = '"&sqlsafe(school)&"' AND START_DATE = #"&startdate&"#"
rs.open sql, cn
rs.close


For i = 0 To cInt(Request.Form("choices")) - 1


sql = "Insert into TRAINING (STAFF_SK, COURSE_NAME, BRANCH, START_DATE, PROVIDER_NAME, FUND_SOURCE, HOURS, DIRECT_COSTS, ERT_COSTS, SCHOOL_COSTS, BRANCH_COSTS, DEPARTMENT_SK, PROGRAM_TYPE) " & _
"VALUES ("&session("staffsk")&","&course&","&school&",#"&startdate&"#,"&provider&","&funding&","&hours&","&direct&","&ert&", "&schcst&","&brcst&" , "&department_sk&","&programtype&")"
next
'response.write sql
rs.open sql, cn

 

[QatQat]

How are you manimg your select boxes?

a good way of doing this is to name your select boxes with a name and a sequential number.

i.e.

mybox1, mybox2, mybox3, etc..

then for i = 1 to n  '(where n is the number of select controls)


insert into mytable (my fiels) values ('" & request("mybox" & i) & "')

next

Cheers

QatQat



Life is what happens when you are making other plans.

 

[Chrisma]

thanks QatQat

here is my code for select box
<form name="addtraining" action="grouptraining.asp" method="POST">
<table border="0" cellspacing="0" cellpadding="0" align="center" width="100%">

<tr>
<td valign="top" width="175" bgcolor="#000099">

<select name="staff" size =10 onChange="moveOver(this.value);">

<%
sql = "SELECT SCHOOL, DEPARTMENT_SK FROM LOCAL_STAFF WHERE AGS_NUM = " & session("authenticated_ags")
rs.open sql, cn
authenticatedschool = rs("SCHOOL")
authenticateddepartmentsk = rs("DEPARTMENT_SK")
rs.close
sql = "SELECT * FROM LOCAL_STAFF WHERE DEPARTMENT_SK = "&authenticateddepartmentsk&" ORDER BY SURNAME, GIVEN_NAME"
rs.open sql, cn
if not rs.eof then
Do Until rs.eof
response.write "<option value=""" & rs("STAFF_SK") & "::" & rs("AGS_NUM") & "::" & rs("SURNAME") & "::" & rs("GIVEN_NAME") & "::" & rs("CLASS_LEVEL") & "::" & rs("GENDER") & "::" & rs("ATSI") & "::" & rs("SCHOOL") & "::" & rs("NESB") & "::" & rs("DISABLED") & "::" & rs("DEPARTMENT_SK") & """>" & rs("SURNAME") & ", " & rs("GIVEN_NAME")&"</option>" &VbCRLF
rs.movenext
Loop
end if
rs.close
%>

</select></td>

<td valign="top" width="175">
List of Name:
<br>
<select multiple name="choiceBox" style="width:150;" size="10">
</select>
</td>
</tr>

<tr>
<td colspan=2 height=10>
<input type="button" value="Remove" onclick="removeMe();">

<input type="button" value="Get Selected Values" onclick="saveMe();">
</td>
</table>
</form>
</center>

 

[Gorkem]

Hi Chrisma,

When you have a multi-select select box, the result comes back in a comma delimited format for that field... the easiest way to do it would be to split the field into an array and then process that.. Also, in your code, I am unsure where in your FOR/NEXT statment that you are actually using the request.form value...

tmpStr = split(request.form("choices"))
For i = 0 To ubound(tmpStr) - 1
     value = tmpStr(i)
     ' write SQL statement to store the value...
     ...
     ...
     ...
next

Cheers,

G.

Oxigen - Next generation business solutions

http://www.oxigen.ca

-----------------------------
Components/Tools/Forums/Software/Web Services