Reply to topic
Database Update Function
jamie
HostMySite Sales Rep
HostMySite Sales Rep

Joined: 19 Mar 2004
Posts: 770
Location: Newark, De
Post Posted: Wed Nov 10, 2004 4:19 am Reply with quote
I'm linking a database update script to the status of a checkbox. Sofar I've got the 'add' function working, however the 'remove a record' is giving me an internal server error for some reason. The checkbox form looks like this:

Code:
<td width="5" height="30" align="left" valign="middle">
          <span class="main"><input type="checkbox" name="#CardID#own" value="yes"
         <cfif qCheckCardId.recordCount GTE 1> CHECKED</cfif> onclick="kada_AutoResWindow('#HOMEPAGE#/users/addCards/editOwn.cfm?CardID=#qAllCards.CardID#','window','400','150','')"></span></td>


and here's the Javascript function that calls to:

Code:
<script language="JavaScript" type="text/JavaScript">
<!--
function kada_AutoResWindow(theURL,winName,width,height,features) {
//1.1 kadazuro kadazuro@kardouz.com
   if(window.screen){
      per_ancho=(width/800)*100;//porcentaje screen-ancho
      per_alto=(height/width)*100;//porcentaje de ancho-alto
      win_ancho=(screen.width*per_ancho)/100;//ancho de la ventana
      win_alto=(win_ancho*per_alto)/100;//alto de la ventana
      x=(screen.width-win_ancho)/2;//centra x
      y=(screen.height-win_alto)/2;//centra y
   }else{// si el buscador es 4-- queda todo igual :(
      x=0;
      y=0;
      win_ancho=w;
      win_alto=y;
   }
   winfeatures=("top="+y+",left="+x+",width="+win_ancho+",height="+win_alto+","+features);
   window.open(theURL,winName,winfeatures);
}

and here is the actual page in the call that does the processing:

Code:
<!--- make sure form was submitted --->
<cfif  NOT isdefined("URL.CardID")>
<h2>Sorry there has been an error, please try again</h2>
   <p><a href="javascript:history.go(-1);">Go back</a></p>
    <div align="center">
      <cfabort>
    </div>
</cfif>



<div align="center">
  <!--- get company info --->
  <cfinvoke
   component="#getSelectedCard#"
   method="getSelectedCard"
   returnvariable="qSelectedCard">
  </cfinvoke>
 
<!--- <cfoutput query="qSelectedCard">
#qSelectedCard.CardID#
</cfoutput> --->

<!--- Get Card Name instead of ID --->
 <cfquery name="qCardName" datasource="#dsn#" username="#un#" password="#pw#">
 SELECT CardTitle
 FROM tblcards
 WHERE cardID = <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
 </cfquery>
 
<!--- Check users db too make sure they dont alreay have this card entered --->
  <cfquery name="qCheckUN" datasource="#dsn#" username="#un#" password="#pw#">
  SELECT userID
  FROM users
  WHERE username =
  <cfqueryparam value="#getAuthUser()#" cfsqltype="cf_sql_varchar">
  </cfquery>
 
<cfquery name="qCheckCardId" datasource="#dsn#" username="#un#" password="#pw#">
  SELECT *
  FROM mycards
  WHERE cardID =
  <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
  AND userID =
  <cfqueryparam value="#qCheckUN.userID#" cfsqltype="cf_sql_varchar">
  AND own >= 1
  </cfquery>
 
<cfquery name="qCheckZero" datasource="#dsn#" username="#un#" password="#pw#">
  SELECT *
  FROM mycards
  WHERE cardID =
  <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
  AND userID =
  <cfqueryparam value="#qCheckUN.userID#" cfsqltype="cf_sql_varchar">
  AND own = 0
</cfquery> 
 
</div>
<cfif qCheckCardId.recordCount GTE 1>
  <div align="center">
<cfquery name="removeCardOwn" datasource="#dsn#" username="#un#" password="#pw#">
  UPDATE mycards set own = 0
  WHERE cardID = <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
  AND userID = <cfqueryparam value="#qCheckUN.userID#" cfsqltype="cf_sql_varchar">
</cfquery> 

    <h1><cfoutput>#URL.cardID#</cfoutput>has been removed!</h1>
    <a href="javascript:;" onclick="window.close()">Close Window</a><br />
  or will self close
  <script language="javascript">
<!--
setTimeout("self.close();",3000)
//-->
</script>
</cfif>
<cfif qCheckZero.recordCount GTE 1>
  <div align="center">
<cfquery name="removeCardOwn" datasource="#dsn#" username="#un#" password="#pw#">
  UPDATE mycards set own = 1
  WHERE cardID = <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
  AND userID = <cfqueryparam value="#qCheckUN.userID#" cfsqltype="cf_sql_varchar">
</cfquery> 

    <h1><cfoutput query="qCardName">#CardTitle#</cfoutput> has been added!</h1>
    <a href="javascript:;" onclick="window.close()">Close Window</a><br />
  or will self close
  <script language="javascript">
<!--
setTimeout("self.close();",3000)
//-->
</script>

  <cfabort>
  <cfelse>
  <cfquery name="addCardOwn" datasource="#dsn#" username="#un#" password="#pw#">
      INSERT INTO mycards (cardID, userID, own, trade)
               VALUES ('#cardID#', '#qCheckUN.userID#', 1, 0)
  </cfquery>
  </div>
</cfif>

<h1><cfoutput query="qCardName">#CardTitle#</cfoutput> has been added!</h1>
    <a href="javascript:;" onclick="window.close()">Close Window</a><br />
  or will self close
  <script language="javascript">
<!--
setTimeout("self.close();",3000)
//-->
</script>



You can see this in action by going to http://mtgotradingpost.com and logging in as 'test' with password 'test'. When no check is present, you can add a card by clicking on the box. However, when there is a check clicking on the box does NOT call the 'remove' function (which really just updates the value to zero). I know this is pretty confusing but if anyone can muddle through it with some insight I'd appreciate it.
loftboy
Forum Regular

Joined: 24 Jun 2004
Posts: 1129
Location: Colorado
Post Posted: Wed Nov 10, 2004 4:39 am Reply with quote
nice code Wink haha
bobclingan
Forum Regular

Joined: 16 Sep 2004
Posts: 271
Location: Abingdon, MD
Post Posted: Wed Nov 10, 2004 12:56 pm Reply with quote
Wow, not only can you write javascript, but spanish javascript Smile
Josh
Forum Regular

Joined: 01 Apr 2004
Posts: 1031
Location: Felton, Delaware
Post Posted: Wed Nov 10, 2004 1:53 pm Reply with quote
roflmao!!!

TACO MI AMBRE PONCHO BURRITO!!! Wink
jamie
HostMySite Sales Rep
HostMySite Sales Rep

Joined: 19 Mar 2004
Posts: 770
Location: Newark, De
Post Posted: Wed Nov 10, 2004 2:24 pm Reply with quote
You guys are a riot. You should really consider quitting your jobs and going on the road as comedians. It would be like the 3 stooges - Josh would say something about how awesome Frontpage is, then Dave hits him with a 2x4, and Bob pokes them both in the eyes...

Laughing
jamie
HostMySite Sales Rep
HostMySite Sales Rep

Joined: 19 Mar 2004
Posts: 770
Location: Newark, De
Post Posted: Wed Nov 10, 2004 3:24 pm Reply with quote
Ok I got part of it - I had two cfquerys with the same name. Now I get the following error for this:

Invalid argument value: Duplicate entry &apos;2-31003001&apos; for key 1
Josh
Forum Regular

Joined: 01 Apr 2004
Posts: 1031
Location: Felton, Delaware
Post Posted: Wed Nov 10, 2004 3:54 pm Reply with quote
Hey I never said FP was awesome... nor did I recommend it... just that I was going to help somebody without totally avoiding their question and just answering with "Switch to {what I use} kuz it's the best"...

*ducks under 2x4*
jamie
HostMySite Sales Rep
HostMySite Sales Rep

Joined: 19 Mar 2004
Posts: 770
Location: Newark, De
Post Posted: Sun Nov 14, 2004 2:15 am Reply with quote
I know you were all dying to find out the stunning conclusion...I rewrote the code and found a way to make it work. Here's the action page:

Code:
<!--- make sure form was submitted --->
<cfif  NOT isdefined("URL.CardID")>
<h2>Sorry there has been an error, please try again</h2>
   <p><a href="javascript:history.go(-1);">Go back</a></p>
    <div align="center">
      <cfabort>
    </div>
</cfif>

<cfif  NOT isdefined("URL.UserID")>
<h2>Sorry there has been an error, please try again</h2>
   <p><a href="javascript:history.go(-1);">Go back</a></p>
    <div align="center">
      <cfabort>
    </div>
</cfif>

<div align="center">
<cfquery name="qCheckCardId" datasource="#dsn#" username="#un#" password="#pw#">
  SELECT own
  FROM mycards
  WHERE cardID = <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
  AND userID = <cfqueryparam value="#URL.userID#" cfsqltype="cf_sql_varchar">
</cfquery>

<!--- Get Card Name instead of ID --->
<cfquery name="qCardName" datasource="#dsn#" username="#un#" password="#pw#">
 SELECT CardTitle
 FROM tblcards
 WHERE cardID = <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
 </cfquery>

</div>
<cfif qCheckCardId.recordCount GTE 1>
<cfoutput query = "qCheckCardId">
<cfswitch expression = #qCheckCardId.own#>
<cfcase value = "1">
<div align="center">
<cfquery name="removeCardOwn" datasource="#dsn#" username="#un#" password="#pw#">
  UPDATE mycards set own = 0
  WHERE cardID = <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
  AND userID = <cfqueryparam value="#URL.userID#" cfsqltype="cf_sql_varchar">
</cfquery> 

    <h1><cfoutput>#qCardName.cardTitle#</cfoutput> has been removed!</h1>
   <a href="javascript:;" onclick="window.close()">Close Window</a><br />
  or it will close itself in 30 seconds
  <script language="javascript">
<!--
setTimeout("self.close();",30000)
//-->
</script>
</cfcase>
<cfcase value = "0">
<div align="center">
<cfquery name="removeCardOwn2" datasource="#dsn#" username="#un#" password="#pw#">
  UPDATE mycards set own = 1
  WHERE cardID = <cfqueryparam value="#URL.cardID#" cfsqltype="cf_sql_varchar">
  AND userID = <cfqueryparam value="#URL.userID#" cfsqltype="cf_sql_varchar">
</cfquery> 

    <h1><cfoutput>#qCardName.cardTitle#</cfoutput> has been added!</h1>
   <a href="javascript:;" onclick="window.close()">Close Window</a><br />
  or it will close itself in 30 seconds
  <script language="javascript">
<!--
setTimeout("self.close();",30000)
//-->
</script>
</cfcase>
</cfswitch>
</cfoutput>
<cfelse>
<cfoutput>
<div align="center">
<cfquery name="addCardOwn" datasource="#dsn#" username="#un#" password="#pw#">
 INSERT INTO mycards (cardID, userID, own, trade)
 VALUES ('#URL.CardID#', '#URL.UserID#', 1, 0)
</cfquery>
<h1><cfoutput>#qCardName.cardTitle#</cfoutput> has been added!</h1>
   <a href="javascript:;" onclick="window.close()">Close Window</a><br />
  or it will close itself in 30 seconds
  <script language="javascript">
<!--
setTimeout("self.close();",30000)
//-->
</script>
</div>
</cfoutput>
</cfif>


If anyone sees a way this can be done better or has any questions, let me know!
Database Update Function
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
 		All times are GMT  
Page 1 of 1  
  
  
 Reply to topic