I havent found any solutions to this problem yet, so I figured I'd post it up and see if anyone has had a similar problem. I'm basically trying to add multiple new items to the same table in our Access SQL Database based on checkbox form data. The user selects a number of categories under which to classify an item (which is also being added in another table). Here's the code I have to retrieve the data and store it in a static size array that is equal to the maximum possible choices (I had errors without putting it into a known-length array):
Dim VidName, VidUrl, VidDescription, VidCategory, VidKeywords, VidSRC, VidType, Hidden, Approve, RunTime, mode_a, i, VidNail, myArray(5)
VidCategory=Request("Category")
mode_a=split(VidCategory,",")
For i=LBound(mode_a) to UBound(mode_a)
myArray(i) = mode_a(i)
Next
Then later in the page, after the video data is inserted into the video table, I need to call the video category table and insert the video id with each corresponding category id(s):
SQL = "SELECT tbl_categories.category_name, tbl_categories.category_id"
SQL = SQL & " FROM (tbl_video_categories INNER JOIN tbl_video ON tbl_video_categories.video_id = tbl_video.video_id) INNER JOIN tbl_categories ON tbl_video_categories.category_id = tbl_categories.category_id"
SQL = SQL & " GROUP BY tbl_categories.category_name, tbl_categories.category_active, tbl_categories.category_id"
SQL = SQL & " HAVING (((tbl_categories.category_active)=1))"
SQL = SQL & " ORDER BY tbl_categories.category_id "
Set rs2 = LoadData(SQL)
If rs2.RecordCount > 0 Then
rs2.MoveFirst
Dim j
j = 0
*' Send is a shorthand function defined elsewhere, basically just Response.Write These sends are for testing purposes only. *
Do While Not rs2.EOF
Send myArray(j)
Send "<br>"
Send rs2("category_id")
Send "<br>"
Send rs2("category_name")
Send "<br>"
Send "<br>"
If rs2("category_name") = myArray(0) Then
Send "ZERO"
Send " <br/>"
SQL = "INSERT INTO tbl_video_categories (video_id, category_id)"
SQL = SQL & " VALUES (" & rs("video_id") & "," & rs2("category_id") & ")"
PerformSQL(SQL)
End If
If rs2("category_name") = myArray(1) Then
Send "ONE"
Send " <br/>"
SQL = "INSERT INTO tbl_video_categories (video_id, category_id)"
SQL = SQL & " VALUES (" & rs("video_id") & "," & rs2("category_id") & ")"
PerformSQL(SQL)
End If
If rs2("category_name") = myArray(2) Then
Send "TWO"
Send " <br/>"
SQL = "INSERT INTO tbl_video_categories (video_id, category_id)"
SQL = SQL & " VALUES (" & rs("video_id") & "," & rs2("category_id") & ")"
PerformSQL(SQL)
End If
If rs2("category_name") = myArray(3) Then
Send "THREE"
Send " <br/>"
SQL = "INSERT INTO tbl_video_categories (video_id, category_id)"
SQL = SQL & " VALUES (" & rs("video_id") & "," & rs2("category_id") & ")"
PerformSQL(SQL)
End If
If rs2("category_name") = myArray(4) Then
Send "FOUR"
Send " <br/>"
SQL = "INSERT INTO tbl_video_categories (video_id, category_id)"
SQL = SQL & " VALUES (" & rs("video_id") & "," & rs2("category_id") & ")"
PerformSQL(SQL)
End If
rs2.MoveNext
j = j + 1
Loop
End If
Set rs2 = Nothing
There are no errors, but No matter how I have tried to change it (using Elseifs, changing the order, stepping through myArray with a counter, etc) It only ever adds the first category in the array to the table, even if there are more than one selected. If you step through the myArray(j) and rs2.categories, they both have data (assuming some checkboxes were clicked), and if all categories are selected and in myArray, they even print out on the same iterations. So the data is there, but the "Send Zero" If statement is the only one that is ever true, and it only executes once for some reason, even though Do While not rs2.EOF executes 5 times (there are currently 5 categories).
Any Help on this would be greatly appreciated!
Edit: rs is another record set defined on the page, and you can assume it has the correct data for the sql insertion (aka the sql code all works)
*Edit 2: I tried ipr101's suggestion. with the first 4/5 checkboxes selected, sending myArray(n) and rs2("category_name"), this was the output:*
Iteration Number: 0
Array: Risk Management
RecordSet: Risk Management
ZERO
Iteration Number: 1
Array: EVMS
RecordSet: EVMS
Iteration Number: 2
Array: PMP Exam Prep
RecordSet: Program Planning
Iteration Number: 3
Array: Lectures
RecordSet: PMP Exam Prep
Iteration Number: 4
Array:
RecordSet: Lectures
As you can see, it only executed the first if, but the recordset and array still had values in future iterations, but did not execute the other if statements
Answer: Ok I figured out how to make this work. Basically I was trying to overcomplicate things. Instead of sending the category name from the form and trying to compare it to the DB values and then trying to add the video_id and category id, I just send the category id (which is unique) as the checkbox value(s), and then execute the sql in the first for loop I had:
For i=LBound(mode_a) to UBound(mode_a)
SQL = "INSERT INTO tbl_video_categories (video_id, category_id)"
SQL = SQL & "VALUES (" & rs("video_id") & "," & mode_a(i) & ")"
PerformSQL(SQL)
Next
Thanks all for your input and suggestions, they were very helpful!
