Re: MySQL v ASP problem
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
> 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=<>;"_
> 'MyODBC driver is 3.51.9
>& "DATABASE=<>; UID=<>;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("<>")'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("<>")
> 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
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 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=<>;"_
'MyODBC driver is 3.51.9
& "DATABASE=<>; UID=<>;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("<>") '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("<>")
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
