Select + update not working together

Question

nekesa 0 Newbie Poster

17 Years Ago

Have gotten the select statement and Update statement to work separatly, but need them to work on the same page and cannot seem to make it happen. Here is the current state of things. Any help would be appreciated.

<%
Dim conn,rs,strsql,sql_update,ssn,cps
strsql = "Select SSN From CWCT07 where SSN = '" & _ 
Request.Form("SSN") & "'"

sql_update = "Update CWCT07 Set CPS = 'Yes' where ssn = '93737'"

'DSN less connection
set conn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\shared\Casework.mdb"
rs.Open strsql 
conn.Execute sql_update
    
    If (not rs.BOF) and (not rs.EOF) then
   Response.Redirect "http://cwpsalem.pdx.edu/staff/Index.html" 
else
   Response.Redirect "http://cwpsalem.pdx.edu"
end if

'close the connection
rs.close
set rs = nothing
conn.close
set conn = nothing
%>
</script>

asp

2 Contributors
3 Replies
93 Views
1 Day Discussion Span
Latest Post 17 Years Ago Latest Post by noman78

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

noman78 0 Light Poster · Answer 1 · 2007-01-25T21:09:52+00:00

try to update the record fter making rs=nothing
as may be when u open the recordset it is making it read only
for further assistance u can get back to me

nekesa 0 Newbie Poster · Answer 2 · 2007-01-26T00:59:05+00:00

try to update the record fter making rs=nothing
as may be when u open the recordset it is making it read only
for further assistance u can get back to me

RS was the problem. I was trying to include a redirect if no records were found and seemed to be doing it too late. I pulled out the If/Then and any references to RS here:

<%@ Language=VBScript %>
<%Response.Buffer=true%>
<HTML><HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
Dim conn,strsql,sql_update,ssn,cps
ssn = "Select SSN From CWCT07 where SSN = '" & Request.Form("SSN") & "'"
sql_update = "Update CWCT07 Set Jan25AM = 'Now()' where ssn = '" & Request.form("ssn") & "'"

'DSN less connection
set conn = server.CreateObject("ADODB.Connection")
'set rs = server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\shared\Casework.mdb"
'rs.Open strsql 
conn.Execute sql_update
'close the connection
conn.close
set conn = nothing
%>
</script>
Congratulations
</BODY></HTML>

This works, except for the Time/date stamp, which I can address in another thread.
I would still like to include the If/then statement and am playing with different ways to make it happen. Mostly I get an "Object is invalid or closed" message when I try and include it then close the RS connection.
In response to Noman78's response to the previous post: It is not so much that I am new to ASP, but that I rarely (like once every two years or so) get a chance to use it in my job...then suddenly "can you do this?".:eek:
So I am trying to muddle through. Thanks for your help.

noman78 0 Light Poster · Answer 3 · 2007-01-26T01:10:35+00:00

you can try this piece of code

set conn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\shared\Casework.mdb"
rs.Open strsql
conn.Execute sql_update
dim eof_var ' a varriable

If (not rs.BOF) and (not rs.EOF) then
eof_var=1
else
eof_var=0
end if

'close the connection
rs.close
set rs = nothing
conn.close
set conn = nothing

If eof_var=1 then
Response.Redirect "http://cwpsalem.pdx.edu/staff/Index.html"
else
Response.Redirect "http://cwpsalem.pdx.edu"
end if

i hope it will work