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.
smime.p7s
Description: S/MIME cryptographic signature
