Insert Loop

[schase]

Hi all

I'm making a intranet - send message type of system.

3 list box's, one with individuals, one with departments and one with Access Levels.

I want a user to be able to select any combination from the three box's, so the 4th and 9th may be selected from users and two departments selected at the same time.

They then put in a subject, and their message and it goes to the next page.

The inserting to database - tblMessages, will later be pulled by the individual the message is intended for by their userid.

So I need to insert individual records for anyone belonging in whatever group was selected.

I'm guessing an array to remove comma deliminated?

Can someone point me into a direction?

Thank you

"I like a man who grins when he fights."

-- Prime Minister Winston Churchill

Stuart

 

[Tarwn]

yep, split to array and loop, you pretty much have the idea already. Although in th case of loops I find it handier to use the Recordset's AddNew method, but thats just personal preference.
You may need to do a sleect from the database before you start inserting, though, to get a list of people to submit to. My suggestion would be instead of saving a copy of the mesage for each and every person, save the message in a single table with an id. Then create another table with fields of message id, person id, read flag. That way you cut down on storage and can even provide an extra bit of functionality with that read flag (marking as new or read already). When someone goes to check messages you simply pull out a recordset with the two tables crossed where they are the person id. If you want to get real fancy you could create a person_message table like above and a group_message table. this way if there are 50 people in a group than you just check for messages in person_message group for that persons id and then check group_message table for groups that person belongs to. You could even split them up in the display giving someone a list of personal messages and group messages.
-Tarwn

 

[schase]

Hi Tarwn,

Thank you for your response - I think I am getting close, but it's only inserting the first record pulled from the tblUsers where their Department matches the forms department.

heres the code. Right now I have 2 records for department number 4 - so the recordset should loop to insert the second as well - but it only returns the first.

<%
set rs2 = Server.CreateObject(&quot;ADODB.Recordset&quot
rs2.ActiveConnection = MM_EPHintra_STRING
rs2.Source = &quot;SELECT * FROM tblMessages&quot;
rs2.CursorType = 1
rs2.CursorLocation = 2
rs2.LockType = 3
rs2.Open()
rs2_numRows = 0
%>

<%
fldUserarray=split(rsUsers(&quot;UserID&quot,&quot;,&quot
for i=0 to ubound(fldUserarray)
if len(trim(fldUserarray(i))) > 0 then
rs2.addnew
rs2(&quot;fldUser&quot=trim(fldUserarray(i))
rs2(&quot;fldSubject&quot= txtSubject
rs2(&quot;fldFrom&quot= Request.Cookies(&quot;EPHUserName&quot
rs2(&quot;fldMessage&quot= txtMessage
rs2.update
end if
next
%> &quot;I like a man who grins when he fights.&quot;

-- Prime Minister Winston Churchill

Stuart

 

[Tarwn]

Could you put in your sql for rsUsers as well? I would guess that it is the culprit, but don't know what it is pulling out
-Tarwn

 

[schase]

You bet - i've also tryed setting cursor to dynamic - to no avail.

Heres the rsUsers.

<%
Dim rsUsers__MMColParam
rsUsers__MMColParam = &quot;1&quot;
If (Request.Form(&quot;txtDepartment&quot <> &quot;&quot Then
rsUsers__MMColParam = Request.Form(&quot;txtDepartment&quot
End If
%>
<%
Dim rsUsers
Dim rsUsers_numRows

Set rsUsers = Server.CreateObject(&quot;ADODB.Recordset&quot
rsUsers.ActiveConnection = MM_EPHIntra_STRING
rsUsers.Source = &quot;SELECT userID FROM qryUsers WHERE tblUsers_fldDepartment = &quot; + Replace(rsUsers__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;&quot;
rsUsers.CursorType = 0
rsUsers.CursorLocation = 2
rsUsers.LockType = 1
rsUsers.Open()

rsUsers_numRows = 0
%> &quot;I like a man who grins when he fights.&quot;

-- Prime Minister Winston Churchill

Stuart

 

[Tarwn]

Ummm...If your not using javascript, string concatenations are &'s not +'s
And if the fldDepartment is a string then you will need to write it as (assuming asp in vbscript):

"SELECT userID FROM qryUsers WHERE tblUsers_fldDepartment = '" & Replace(rsUsers__MMColParam, "'", "''") & "'"

Plus you have userID capitalized two differant ways, one in each piece of code.

-tarwn

 

[schase]

Not sure on the & versus + I'm using UltraDev, it pretty much created it that way - I replaced and got a type mismatch error.

&quot;I like a man who grins when he fights.&quot;

-- Prime Minister Winston Churchill

Stuart

 

[schase]

I tried it on a different table and it still only inserted the first record returned in the recordset.

Somehow it must not be returning up to pick up the other records? &quot;I like a man who grins when he fights.&quot;

-- Prime Minister Winston Churchill

Stuart