List Issues, Is there any other way doing this:

Report · Jul 04, 2008

I am using the lists, but due to some cause. the list functions are throwing an error:

I get an Error in this Scenarion:

In function ListGetAt(list, index [, delimiters]), the value of index, 11, is not a valid as the first argument (this list has 3 elements). Valid indexes are in the range 1 through the number of elements in the list.

In My database the values is just stored like this:

sometimes if 2 people are assigned as moderators they appear like this database column moderators of table forum.:

1,5

Then if one moderator is defined, it can appear as 5 or 6 as his userid is 5 or 6

When i try to delete any modetaror, it just looks for the element 1 in the list, which it is unable to find in certain cases, So it throws an error.

Here below is the code what am i trying.

HERE PROCESSING STARTS:

<cfif isdefined ("form.Remove")>
<cftry>
<Cfset pos = listcontainsnocase(FIELDNAMES, "Remove_")>
<Cfset workingpos = ReReplaceNoCase(ListGetAt(FIELDNAMES, pos), "Remove_", "")>
<Cfset forumid = ListGetAt(form.forumid, workingpos)>
<Cfset modid = ListGetAt(form.REMMODERATORS, workingpos)>

<CFQUERY NAME="GetModForum" datasource="#request.dsn#" username="#request.dsnuser#" password="#request.dsnpasswd#">
SELECT *
FROM Forums
where Forum_ID in (#forumid#)
</CFQUERY>

<Cfset moderatorlist = ValueList(GetModForum.Moderators)>
<cfset listfindpos = ListFindNoCase(moderatorlist, modid)>
<cfset moderatorlist = ListDeleteAt(moderatorlist, listfindpos)>

<CFQUERY NAME="UpdateCat" datasource="#request.dsn#" username="#request.dsnuser#" password="#request.dsnpasswd#">
UPDATE Forums
SET Moderators = '#moderatorlist#'
where Forum_ID in (#forumid#)
</CFQUERY>
<cfcatch type="any">
<cfoutput>#cfcatch.Detail#</cfoutput>
</cfcatch>
</cftry>

HERE FORm STARTS

<cfif GetTitles.recordcount gt 0>
<table width="100%" cellspacing="0" class="pClrBody" border="0">
<tr align="left" bgcolor="#<CFOUTPUT>#topcell#</CFOUTPUT>">
<td><strong>Forum</td>
<td><strong>Moderators</font></td>
<td width="10"></td>
</tr>
<cfoutput query="GetForums">
<tr>
<td align="left">#GetForums.Forum_Name#</td>

<Cfif GetForums.Moderators is "">
<td align="left">No Moderators Assigned to this Forum</td>
<Cfelse>
<td align="left">
<select name="RemModerators">
<cfloop from="1" to="#ListLen(GetForums.Moderators)#" index="M">

<cfif ListGetAt(GetForums.Moderators, M) neq "">
<CFQUERY NAME="GetMods" datasource="#request.dsn#" username="#request.dsnuser#" password="#request.dsnpasswd#">
SELECT *
FROM users
Where UserID in (#ListGetAt(Moderators, M)#)
</CFQUERY>

<option value="#GetMods.Userid#">#GetMods.UserLogin#
</cfif>
</cfloop>
</select>
</td>
<td align="right" width="10">
<input type="hidden" name="forumid" value="#forum_id#">
<input type="hidden" name="remove" value="yes">
<input type="submit" name="Remove_#currentrow#" value="Remove" onclick="javascript:return confirm('Are you sure you want to remove this Moderator?')">
</td>
</CFIF>
</tr>

Report · Jul 04, 2008

Normalize your database and you won't have to worry about stuff like this.

Report · Jul 04, 2008

Thanks Mate,

But now it os gone too far away and i cannot go to change the database structure..

I am looking into ways to do some stuff here, but till now have not recieved any success.

Report · Jul 04, 2008

As Dan says, you should normalize your database. It will save you problems in the long run.

In the meantime, remember that a list element must be at least one character. So ListLen(1,2,3,4,5) is 5,
ListLen(1,2,,,5) is 3 and ListLen(,,,,) is 0.