ColdFusion Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsProgramming Languages - MoreColdFusion Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old June 12th, 2005, 06:12 PM
Panther893's Avatar
Panther893 Panther893 is offline
MostarDesigns.com
Dev Shed Novice (500 - 999 posts)
 
Join Date: Mar 2004
Posts: 821 Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 5 Days 21 h 15 m 58 sec
Reputation Power: 56
Send a message via MSN to Panther893
Exclamation Update query not working for some reason

Code:
<cfparam name="FORM.submitvalue" default="0">
<cfif "#FORM.submitvalue#" is "1">
  <cfloop index="loopcount" from="1" to="#FORM.numberofforms#">
	  <cfparam name="FORM.delete#Evaluate(LoopCount)#" default="0">
	  <cfset variables.ID="form.ID#Evaluate(LoopCount)#">
	  <cfset variables.ID="#Evaluate(variables.ID)#">
	  <cfset variables.name="form.name#Evaluate(LoopCount)#">
	  <cfset variables.name="#Evaluate(variables.name)#">
	  <cfset variables.gorder="form.gorder#Evaluate(LoopCount)#">
	  <cfset variables.gorder="#Evaluate(variables.gorder)#">
	  <cfset variables.delete="form.delete#Evaluate(LoopCount)#">
	  <cfset variables.delete="#Evaluate(variables.delete)#">
  
	  <cfquery datasource="#datasourcename2#" name="squad_update" blockfactor="100" username="#dbusername#" password="#dbpassword#">
	  UPDATE unit_galaxy
	  SET subname = '#variables.name#' , gorder = #variables.gorder#
	  WHERE ID = #variables.ID# and unitid = #url.ID#
	  </cfquery>
	  
	  <cfif "#variables.delete#" is not "0">
		<cfquery datasource="#datasourcename2#" name="squad_delete" blockfactor="100" username="#dbusername#" password="#dbpassword#">
		DELETE FROM unit_galaxy
		WHERE ID = #variables.delete# and unitid = #url.ID#
		</cfquery>
	  </cfif>
  
  </cfloop>
  <cflocation url="index.cfm?pageid=#pageid#&leaderpage=#leaderpage#&ID=#url.ID#&LID=#LID#">
</cfif>

<cfquery datasource="#datasourcename2#" name="galaxy_count" blockfactor="100" username="#dbusername#" password="#dbpassword#">
SELECT *
FROM unit_galaxy
WHERE unitid = #ID#
</cfquery>
<cfquery datasource="#datasourcename2#" name="galaxy" blockfactor="100" username="#dbusername#" password="#dbpassword#">
SELECT *
FROM unit_galaxy
WHERE unitid = #ID#
ORDER BY gorder ASC
</cfquery>

<cfoutput>
<table width="#setwidth#" border="0" cellspacing="1" cellpadding="2" class="style">
  <tr>
    <td colspan="5" bgcolor="#forum_categorycolor#" align="center"><strong>Unit Squads</strong></td>
  </tr>
</cfoutput>
  <tr>
    <td width="50" align="center"><em>ID</em></td>
    <td width="145" align="center"><em>Name</em></td>
    <td width="50" align="center"><em>Order</em></td>
    <td width="50" align="center"><em>Delete</em></td>
    <td width="300" align="center"></td>
  </tr>
  <cfoutput>
  <form action="index.cfm?pageid=#pageid#&leaderpage=#leaderpage#&ID=#url.ID#&LID=#LID#" method="post">
  <input name="submitvalue" value="1" type="hidden">
  <input size="1" type="hidden" name="numberofforms" value="#galaxy_count.recordcount#">
  </cfoutput>
  <cfset formnumber = 1>
  <cfoutput query="galaxy">
  <input size="1" type="hidden" name="ID#formnumber#" value="#ID#">
  <tr>
    <td width="50" align="center">#ID#</td>
    <td width="145" align="center"><input name="name#formnumber#" type="text" value="#subname#"></td>
    <td width="50" align="center">
	<select name="gorder#formnumber#">
	<cfloop index="loopcount" from="1" to="#galaxy_count.recordcount#">
	<option value="#Evaluate(LoopCount)#" <cfif "#Evaluate(LoopCount)#" is "#gorder#">selected</cfif>>#Evaluate(LoopCount)#</option>
	</cfloop>
	</select>
	</td>
    <td width="50" align="center"><input type="checkbox" name="delete#formnumber#" value="#ID#"></td>
    <td width="300" align="center"></td>
  </tr>
  </cfoutput>
  <tr>
    <td colspan="4" align="right"><input type="submit" value="Update"></td>
    <td width="300"></td>
  </tr>
  </form>
  <tr>
    <td colspan="5" height="20"></td>
  </tr>
  <tr>
    <td colspan="2" width="195" bgcolor="<cfoutput>#forum_categorycolor#</cfoutput>" align="center"><strong>Create a Squad</strong></td>
    <td colspan="3" width="400"></td>
  </tr>
  <cfoutput>
  <form action="index.cfm?pageid=#pageid#&leaderpage=squad_new&ID=#url.ID#&LID=#LID#" method="post">
  <tr>
    <td colspan="2" width="195">Squad Name:<div class="spacer">&nbsp;</div><input name="name" type="text"></td>
    <td colspan="3" width="400"></td>
  </tr>
  <tr>
    <td colspan="5" height="2"></td>
  </tr>
  <cfset "neworder" = #galaxy_count.recordcount# + 1>
  <tr>
    <td colspan="2" width="195">
	Order:<div class="spacer">&nbsp;</div>
	<select name="gorder">
	<cfloop index="loopcount" from="1" to="#neworder#">
	<option value="#Evaluate(LoopCount)#" <cfif "#Evaluate(LoopCount)#" is "#neworder#">selected</cfif>>#Evaluate(LoopCount)#</option>
	</cfloop>
	</select>
	</td>
    <td colspan="3" width="400"></td>
  </tr>
  <tr>
    <td colspan="5" height="2"></td>
  </tr>
  <tr>
    <td colspan="2" width="195"><input type="submit" value="Create Squad"></td>
    <td colspan="3" width="400"></td>
  </tr>
  </form>
  </cfoutput>
</table>



That code doesnt work for some reason, code similar to it works on another page.

Here is the error:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.


SQL = "UPDATE unit_galaxy SET subname = 'Keshik,The Frost Falcons,Turkina Sibko' , gorder = 1,2,3 WHERE ID = 16,18,17 and unitid = 18"

Data Source = "A9523_MC2"


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (14:4) to (14:129) in the template file O:\HOSTED WEB SITES\ZLATKO.LAKISIC\DEATHFX_NET\WWW\ADMIN\UNITS\SQUAD.CFM.


Date/Time: 06/12/05 19:10:21
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)
Remote Address: 85.94.128.43
HTTP Referrer: http://www.deathfx.net/admin/index.cfm?pageid=leadermenu&leaderpage=squad&ID=18&LID=2
Query String: pageid=leadermenu&leaderpage=squad&ID=18&LID=2
__________________
MostarDesigns.com

Reply With Quote
  #2  
Old June 12th, 2005, 06:55 PM
kiteless kiteless is offline
Moderator
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Jun 2002
Location: Raleigh, NC
Posts: 3,655 kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Week 4 Days 13 h 29 m 8 sec
Reputation Power: 53
If these columns are strings...

gorder = 1,2,3 WHERE ID = 16,18,17 and unitid = 18

they need single quotes around them.

As an aside, in the future if you have a question please try to omit all the unnecessary code and supply a simple example of the code causing the error and nothing else. All the extra code (such as the table generation or the unrelated queries) just makes things less clear. Thanks.
__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian.
How to Post a Question in the Forums

Reply With Quote
  #3  
Old June 12th, 2005, 07:03 PM
Panther893's Avatar
Panther893 Panther893 is offline
MostarDesigns.com
Dev Shed Novice (500 - 999 posts)
 
Join Date: Mar 2004
Posts: 821 Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 5 Days 21 h 15 m 58 sec
Reputation Power: 56
Send a message via MSN to Panther893
i thought having the form that submits it would be helpful, the ID and gorder values are listed as number fields in the database wouldnt it be wrong to put single quotes around them?

Reply With Quote
  #4  
Old June 12th, 2005, 07:34 PM
kiteless kiteless is offline
Moderator
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Jun 2002
Location: Raleigh, NC
Posts: 3,655 kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Week 4 Days 13 h 29 m 8 sec
Reputation Power: 53
If those columns are numeric, then you can only insert one number into them. You can't insert a list of numbers, for that the column would have to be a string field (varchar most likely).

Including the form might be helpful depending on the problem. But if you decide to do that, stripping it down to just the form and the query causing the problem will make it easier for others to help you. The more code there is, the longer it takes to figure it out, resulting in fewer people willing to take the time to reply. Optimizing your post isn't just good netiquite, it's in your own best interest as well.

Reply With Quote
  #5  
Old June 13th, 2005, 03:38 PM
Panther893's Avatar
Panther893 Panther893 is offline
MostarDesigns.com
Dev Shed Novice (500 - 999 posts)
 
Join Date: Mar 2004
Posts: 821 Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level)Panther893 User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 5 Days 21 h 15 m 58 sec
Reputation Power: 56
Send a message via MSN to Panther893
so the only i guess "easy way" to do this is to change the field to varchar?

Reply With Quote
  #6  
Old June 13th, 2005, 05:23 PM
kiteless kiteless is offline
Moderator
Dev Shed Expert (3500 - 3999 posts)
 
Join Date: Jun 2002
Location: Raleigh, NC
Posts: 3,655 kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level)kiteless User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 1 Week 4 Days 13 h 29 m 8 sec
Reputation Power: 53
If you want to insert a list of multiple numbers into one column, then yes, you must change the column type. You could also create another table that links an id in the base table to numbers in the second table, and just insert each number and id in its own row in the second table. This is probably the best solution but it is additional work.

Reply With Quote
Reply

Viewing: Dev Shed ForumsProgramming Languages - MoreColdFusion Development > Update query not working for some reason


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway