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]
