J.R.,
The difference in the way the two statements function is in your code. In the first
example, you set the cursor type (rs.Open sql, conn,3,3) as updatable. In the second
example, you set the cursor to the default type (non-updatable: rs.Open sql, conn).
I haven't used ASP extensively recently, but it seems likely that if you add the
",3,3" to the second rs.Open statement your app will behave properly.
Randy Clamons
Systems Programming
Astro-auction.com
> ------------Original Message------------
> From: "J.R. Bullington" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED], [EMAIL PROTECTED]
> Date: Thu, Oct-7-2004 1:45 PM
> Subject: MySQL v ASP problem
>
> I have searched high and low and I know that you guys can help out ( as
> you
> have helped me before ).
>
> First, I will list table descriptions, then the problem, then I will
> list
> the code, and finally the permissions.
>
> The Descriptions:
> 2 tables - both MyISAM.
> "Table 1" has 9 fields, 1 index PID (PK, Index);
> "Table 2" has 36 fields, 1 index EcnID (PK, index). PID is a FK in
> "Table 2";
> ASP and IIS 6 on a Win2k3 server
> MySQL 4.0.20 on a Linux RHEL AS
>
> The Problem:
> I am trying to do an rs.update using ASP. In "Table 1", code works
> perfectly, retrieves and updates without issue. In "Table 2", same
> code,
> doesn't work. NOTE: I have to use rs.update and not "UPDATE <tablename>
> SET
> ..." due to the large amount of data that needs to be pushed. I get the
> old
> "Query-based update failed because the row to update could not be
> found."
>
> So here we go with the code:
>
> **************************** BEGIN NECESSARY CODE
> *************************************************
> <%
>
> Dim Conn
> Conn = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=<<IPADDRESS>>;"_
> 'MyODBC driver is 3.51.9
> & "DATABASE=<<DBNAME>>; UID=<<UID>>;PWD=<<PWD>>; OPTION=3"
> '
> dim resdata(36), resflds(36), reschks(36), resnote(36), resfrmt(36),
> errtext
> Set rs = Server.CreateObject("ADODB.Recordset")
> '
> if request.querystring("EcnID")<>"" then
> session("EcnID")=request.querystring("EcnID")
>
> ****** CODE JUMP ******
> '
> if request.form("B1")="Exit Without Changes" then
> response.redirect("<<SOMEOTHERPAGE.ASP>>") 'Handle bail-out
> '
> if request.form("B1")="COMPLETE REVISION" then
> '**** START HERE TO STORE UPDATES ****
> '
> vararray=session("resdata")
> 'retrieve the session data provided by
> vararra1=session("resflds")
> 'database query
> vararra2=session("reschks")
> vararra3=session("resnote")
> '
> for x=0 to ubound(vararray)
> 'Parse the session data into usable arrays
> resdata(x)=vararray(x)
> resflds(x)=vararra1(x)
> reschks(x)=vararra2(x)
> resnote(x)=vararra3(x)
> next 'x
> '
> sql = "Select * from tblEncounter where EcnID= " & session("EcnID")
> response.write sql
> response.flush
> rs.Open sql, conn,3,3
> '
> For Each objItem in request.form
> 'look at form field
> for x=0 to ubound(resflds)
> 'search all the field name array
> if ucase(objitem)= ucase(resflds(x)) then
> 'update the data
> resdata(x)=request.form(objitem)
> 'resdata array now contains newest data
> end if
> next 'x
> next 'objitem
>
> rs("Compdate")=now()
> '
> rs.update
> response.redirect("<<SOMEOTHERPAGE.ASP>>")
> end if
> '
> if request.form("B1")="Update Information" then
> '**** START HERE TO DO UPDATE / ERROR CHECK ****
> '
> vararray=session("resdata")
> 'retrieve the session data provided by the
> vararra1=session("resflds")
> 'database query
> vararra2=session("reschks")
> vararra3=session("resnote")
> '
> for x=0 to ubound(vararray)
> 'Parse the session data into usable arrays
> resdata(x)=vararray(x)
> resflds(x)=vararra1(x)
> reschks(x)=vararra2(x)
> resnote(x)=vararra3(x)
> next 'x
> '
> **** CODE JUMP *****
>
> Else
> '**** START HERE FOR NEW DATA PULL-UP ****
> '
> dim resname
> dim rs
> dim sql
> sql = "Select * from tblEncounter where EcnID="&session("EcnID")
> rs.Open sql, conn
> rs.MoveFirst
> '
> x=0
> for each fld in rs.Fields
> 'Load RS into an session array
> resflds(x) = fld.name
> 'Load field names from database
> resdata(x) = fld.value
> 'Initialize the fields to null
> reschks(x) = 1
> 'set field status to "good"
> resnote(x) = ""
> 'set field comment to "null"
> x=x+1
> next
> '
> rs.close
> '
> session("resdata")=resdata
> session("resflds")=resflds
> session("reschks")=reschks
> session("resnote")=resnote
>
> end if
> '
> %>
> *********************** END NECESSARY CODE **************************
>
> Now, permissions:
>
> MySQL - FULL CONTROL FOR THIS DATABASE ( ALL PRIVLIGES WITH GRANT
> OPTION )
> IUSR - Read, Read & Execute, Write, Modify, List Folder Entries
>
> Please, if any help can be offered I would greatly appreciate it. If
> you
> need anything else from me, please don't hesitate to ask!
>
> TIA
> J.R.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
