Updating multiple records from form, and check all button

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kenji776 - 29 Sep 2005 22:22 GMT

Hey everyone. I have two problems in my page.
1) A button that is supposed to check all checkboxes does not work for reasons
unknown
2) Code to delete update multiple records doesn't seem to want to work.

Pertaining to problem one, there is no error message, just a not working
button.
For problem two I can provide the following error message

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error in UPDATE statement.

The error occurred in D:\FTP\Digital
Swordsmen\Prod\Members\U_add_Remove_friends.cfm: line 38

36 :                     UPDATE usersfriends
37 :                     SET security_level = #security_update123#
38 :                     WHERE record_id IN (#form.security_update123#)
39 :         </cfquery>
40 : </cfif>

SQL     UPDATE usersfriends SET security_level = 1,3,3,3,3,3 WHERE record_id
IN (1,3,3,3,3,3)
DATASOURCE     users
VENDORERRORCODE     -3503
SQLSTATE     42000

Basically the user is supposed to be able to put in a bunch of new values, and
have them all update with one click, but its having a problem. Ill attatch the
code, help with either problem or both is highly appreciated. Thanks in advnace.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<META NAME="Description" CONTENT="DigitalSwordsmen.com - Free Music, Games,
Videos, Programs, FTP Server, Mail Server and more. We have info on swords,
anime, and video games. Free forums, and personl profile. Web log comming
soon.">
<META NAME="Keywords" CONTENT="Swordsmanship, swords, swordfighting, anime,
manga, snes, nes, gba, sega 3x, roms, free, downloads, music, games, programs,
ftp server, mail server, pop3 server, web log, personal profiles, forums, chat,
cool stuff, pictures, downloads.">
<link href="../mainload.css" rel="stylesheet" type="text/css">
<title>Update friends list</title>

</head>

<body>


<cfif IsDefined("form.security_update123")>
        <cfquery     name="delete_sent_messages" datasource="users"
username="administrator" password="534Digital980" >
                    UPDATE usersfriends
                    SET security_level = #security_update123#
                    WHERE record_id IN (#form.security_update123#)
        </cfquery>
</cfif>




<cfif IsDefined("form.deletewho")>
    <cfquery name="delete_recieved_messages" datasource="users"
username="administrator" password="534Digital980">
            DELETE * FROM usersfriends WHERE record_ID IN (#form.deletewho#)
    </cfquery>
</cfif>




<cfif isdefined("form.add")>
    <cfquery name="getuserid" datasource="users" username="administrator"
password="534Digital980">
    Select userid
    from users
    where userlogin = '#form.friend#'
    </cfquery>
    <cfquery name="addfriends" datasource="users" username="administrator"
password="534Digital980">
        INSERT INTO UsersFriends (UserID, FriendID, security_level, friend_name,
username) VALUES (#session.userid#, #getuserid.userid#, #form.security#,
'#form.friend#', '#session.userlogin#');
    </cfquery>

</cfif>




<cfquery name="friendquery" datasource="users" username="administrator"
password="534Digital980">
    Select
    friends
    from users
    where userlogin = '#session.userlogin#'
</cfquery>


<span class="style1">Update Friends List</span>
<br>
<hr>
Here you can add friends to your buddy list. To add a user simply put in their
login name, and what
security level you want to clean them for and click add friend. The security
level feature is a feature
that allows you to share certain information with certain users. Such as
weblogs. A user with a privacy
level of 1 may see everything you make, unless you mark it as private. A user
with a security level of 5
may only see the more public details. The higher the user, the more they can
see, becasue they can see
whatever you say they can, plus all the levels below it. To edit a friends
security level, simply put their
name in the add friend box, and put in the new security level, their security
clearance will then be updated.


<cfif isdefined("form.add")>
    Friends List Updated!
</cfif>


<br>

<cfform name="addfriends" method="post">
<cfoutput>
<table width="200" border="1">
<tr>
<td>Friend Login </td>
<td>Security<br>
Level</td>
<td> </td>
</tr>
<tr>
<td><input name="friend" type="text" id="friend2"></td>
<td><div align="right">
<cfinput name="security" type="text" size="4" maxlength="1"
validate="integer" range="1,5">
</div></td>
<td><input type="submit" value="Add Friend" name="add"></td>
</tr>
</table>
<br>
</cfoutput>
</cfform>




<cfquery datasource="users" username="administrator" password="534Digital980"
name="get_friends_info">
    SELECT *
    from Usersfriends
    where userid = #session.userid#
</cfquery>

<hr>
<strong>Your Current Friends</strong><br>
<cfoutput>
    <cfform name="security_update" method="post">
    <table>
        <tr>
            <td width="55">Delete Friend </td>
        <td width="150">Friend Name</td>
                <td width="95">Security Level</td>
            </tr>
            
        <cfloop query="get_friends_info">
        <tr>
                    <td><input type="checkbox" name="Deletewho"
value="#get_friends_info.friendID#"></td>
            <td>#get_friends_info.friend_name#</td>
                    <td><cfinput name="security_update123" type="text"
value="#get_friends_info.Security_Level#" size="2" maxlength="1"
validate="integer" range="1,5"></td>
                </tr>
        </cfloop>

            <tr>
                <td colspan="3">
                    
                    <input type="button" value="Check All"
onClick="this.value=check(this.form.deletewho)">
                    <input type="submit" value="Delete and Update" name="update_sec">
                </td>
            </tr>
    </table>
    </cfform>
</cfoutput>
</body>
</html>

Reply to this Message

kenji776 - 29 Sep 2005 22:27 GMT

Well I got the check box running, I think it was a capitolization error. the other error persists however.

Reply to this Message

Dan Bracuk - 29 Sep 2005 22:48 GMT

Here is the sql your code generated.
SQL UPDATE usersfriends SET security_level = 1,3,3,3,3,3 WHERE record_id IN
(1,3,3,3,3,3)

since 1,3 etc is not a number, it must be text and there are no quotes around
it. That is a syntax problem for sure. Now, what is the data type for
security level? If it's numeric, that's a logic problem?

In plain english, what are you trying to do?

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / September 2005

Updating multiple records from form, and check all button