Select + update not working together

•

What is DaniWeb IT Discussion Community?

You're currently browsing the ASP section within the Web Development category of DaniWeb, a massive community of 456,552 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,426 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our ASP advertiser: Lunarpages ASP Web Hosting

Thread Tools Display Modes

Views: 1728 | Replies: 3

•

Join Date: Jan 2007

Posts: 10

Reputation: nekesa is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

nekesa

Offline

Newbie Poster

Select + update not working together

Jan 24th, 2007

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.

 Help with Code Tags 
 asp Syntax (Toggle Plain Text) 
<%
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>
<%
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>

•

Join Date: Nov 2005

Location: Dubai,UAE

Posts: 36

Reputation: noman78 is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

noman78

Offline

Light Poster

Re: Select + update not working together

Jan 25th, 2007

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

•

Join Date: Jan 2007

Posts: 10

Reputation: nekesa is an unknown quantity at this point

Rep Power: 2

Solved Threads: 0

nekesa

Offline

Newbie Poster

Re: Select + update not working together

Jan 25th, 2007

•

Originally Posted by noman78

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:
[code:ASP]
<%@ 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>
[/code]
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.

•

Join Date: Nov 2005

Location: Dubai,UAE

Posts: 36

Reputation: noman78 is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

noman78

Offline

Light Poster

Re: Select + update not working together

Jan 25th, 2007

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