MYSQL UPDATE Not WOrking

All Forums >> [Web Development] >> ASP and Database

Message

sentinel -> MYSQL UPDATE Not WOrking (10/6/2005 11:25:32)
Hey all... I have a table in MYSQL that I am trying to update. I cant figure out what the correct SQL is to do this. Here is my SQL code. Update Events SET approved="Yes" WHERE eventid = 'eventid' When I run it nothing happens. I have also tried eventid = ::eventid:: `eventid` = '$eventid' eventid = eventid Any Ideas???

rdouglass -> RE: MYSQL UPDATE Not WOrking (10/6/2005 13:05:51)
quote: Update Events SET approved="Yes" WHERE eventid = 'eventid' I think it should be this: "Update Events SET approved='Yes' WHERE eventid = " & eventid That any better?

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 13:11:10)
Hey there... Just returns an error with that

rdouglass -> RE: MYSQL UPDATE Not WOrking (10/6/2005 13:16:14)
quote: Just returns an error with that What error?

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 13:20:06)
It comes back with this generic message: Database Results Error The operation failed. If this continues, please contact your server administrator. Thats all i get

rdouglass -> RE: MYSQL UPDATE Not WOrking (10/6/2005 13:26:57)
What field types are eventid and approved?

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 13:30:29)
EventID is an Integer field Approved is a text field If i leave the sql like Update Events SET approved='Yes' WHERE EventID = EventID it will update everything. So i do know that it can update the database.

rdouglass -> RE: MYSQL UPDATE Not WOrking (10/6/2005 14:46:12)
Where does eventid come from? a form maybe? "Update Events SET approved='Yes' WHERE EventID = " Request("EventID") That would be my next try.

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 14:52:17)

EventID is the column in the database that is autonumbered by mysql..

Here is the Table definition

--
-- Table structure for table `hcstaff`.`events`
--

DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
  `EventID` int(10) unsigned NOT NULL auto_increment,
  `UserName` text NOT NULL,
  `Status` text NOT NULL,
  `System` text NOT NULL,
  `Description` text NOT NULL,
  `ApprovedBy` text,
  `DateEntered` date default '0000-00-00',
  `Expiration` datetime default '0000-00-00 00:00:00',
  `Approved` text NOT NULL,
  PRIMARY KEY  (`EventID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

rdouglass -> RE: MYSQL UPDATE Not WOrking (10/6/2005 14:57:18)
quote: EventID is the column in the database that is autonumbered by mysql Sure, I understand. But where does the criteria come in? IOW, how do you determine which records to update? You obviously don't want to update them all. Is it a list of ID's or an individual one? Does it come from a form or querystring? Etc.

Spooky -> RE: MYSQL UPDATE Not WOrking (10/6/2005 14:58:14)
Have you changed debug to true? http://www.frontpagewebmaster.com/m-175524/tm.htm

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:00:27)

my code:

The link page to the update script

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM events WHERE approved = "No""
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=9 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&EventID=19&UserName=201&Status=201&System=201&Description=201&ApprovedBy=201&DateEntered=133&Expiration=135&Approved=201&"
fp_iDisplayCols=9
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="44727" --
<tr>
        <td height="13" align="left" valign="top" width="880" colspan="4">
        <p align="right"><font face="Verdana" size="1">Delete Event |
        <a target="_blank" href="Event_Update_Approve.asp?EventID=<%=FP_FieldURL(fp_rs,"EventID")%>">Approve Event</a></font></td>
      </tr>

My Update Page:

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="Update Events SET approved='Yes' WHERE EventID = " request("EventID")"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=16 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="50161" --><!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr>" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
</table>
<p align="center">�</p>

<p align="center">�<p align="center">�<input type="submit" value="Approved! Click Me" onClick="ClsWindow()" name="B1" tabindex="1" size="20" >
</p>
</p>

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:05:25)
Rdouglas: I have a DRW that displays a hyperlink which send the EventID to teh update page. Spooky: i turned on DEBUG.. I forgot all about that.. sorry here is the error quote: Database Results Error Description: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EventID")' at line 1 Number: -2147217900 (0x80040E14) Source: Microsoft OLE DB Provider for ODBC Drivers

rdouglass -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:08:04)
quote: Event_Update_Approve.asp?EventID=<%=FP_FieldURL(fp_rs,"EventID")%> AH. That's what I was looking for. Have you tried this one? "Update Events SET approved='Yes' WHERE EventID = " Request("EventID") If that doesn't work, try putting this before the DRW: Response.write(Request("EventID")) Response.end and see if the EventID is actually making it to the page. EDIT: Try making this line: Event_Update_Approve.asp?EventID=<%=FP_FieldURL(fp_rs,"EventID")%> into this: Event_Update_Approve.asp?EventID=<%=trim(FP_Field(fp_rs,"EventID")&"")%>

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:11:21)
Rdouglas... That did not work. I put that code before the DRW and it is telling me FALSE

Spooky -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:12:40)
"Update Events SET approved='Yes' WHERE EventID = "& Request("EventID")

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:12:58)
Rdouglas.... Getting this Error Type: Microsoft VBScript runtime (0x800A01CA) Variable uses an Automation type not supported in VBScript /approvals.asp, line 67

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:15:20)
Spooky... Got this error Database Results Error Description: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EventID")' at line 1 Number: -2147217900 (0x80040E14)

rdouglass -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:16:05)
quote: & Request("EventID") [&o] [&o] [&o] I don't have a copy of mySQL running here at work to look at but if you don't have this resolved by this evening, I have a copy on my home network I can look at. But hopefully someone will figure it out by then.

Spooky -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:19:05)
What is the URL showing in the browser address?

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:22:00)
Spooky... When I put cursor over a hyperlink I get this http://hc-wxmp-rrwagn/Event_Update_Approve.asp?EventID=49 (its an internal network)

Spooky -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:26:53)
If you update the database directly, via phpmyadmin using this SQL, does it error? Update Events SET approved='Yes' WHERE EventID=49

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:30:38)
when i use the mysql browser it updates just fine. [8\|][8\|]

Spooky -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:37:07)
Does the line in your code now look like this? fp_sQry="Update Events SET approved='Yes' WHERE EventID = " & request("EventID") Note - your code previously had an extra quote at the end

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:39:41)

Yes... And I get this message

quote:

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/Event_Update_Approve.asp, line 12, column 81
fp_sQry="Update Events SET approved='Yes' WHERE EventID = " & request("EventID")"
--------------------------------------------------------------------------------^

This is the update page code with all recent changes:

<table width="100%" border="1">
  <thead>
  </thead>
  <tbody><%Response.write(Request("EventID")) <BR>Response.end %>
    <!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
    </SCRIPT>
<% end if %>
<%
fp_sQry="Update Events SET approved='Yes' WHERE EventID = " & request("EventID")"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=16 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>

Spooky -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:42:50)
The code you are showing me has the extra quote after request("EventID") It should not be there

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:45:16)
The line now shows: fp_sQry="Update Events SET approved='Yes' WHERE EventID = " & request("EventID") It is still not updating the table.... Gremlins???

Spooky -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:50:37)
but no errors this time?

sentinel -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:54:34)
no errors.... Just the part that says FALSE from the code rdouglas had me put in. <%Response.write(Request("EventID")) Response.end %>

Spooky -> RE: MYSQL UPDATE Not WOrking (10/6/2005 15:57:40)
You can remove that code, but change your SQL string to : fp_sQry="Update Events SET approved='Yes' WHERE EventID = " & clng(request.querystring("EventID")) How does that work?

Page: [1] 2 next > >>

0.078125