/    Sign up×
Community /Pin to ProfileBookmark

Select Statement not updating database correctly

I have a page that updates a database when an individual wants to unsubscribe from our mailings. Each individual is assigned a Unique ID (UID) which I use to find them when I want to update their records. The UID is an eight digit number. I use similar code on dozens of pages without a problem, but for some reason this page doesn’t want to work. The code below should update the database for just the one individual whose UID = Session(“strUID”), but instead it unsubscribes everyone in the database. Does anyone have any idea why this page would be acting this way. Thanks!

Set rsCore = Server.CreateObject(“ADODB.Recordset”)
strSelect = “SELECT Unsubscribed, UnsubscribedDate, UnsubscribedTime FROM tblCore WHERE UID = ” & Session(“strUID”)
rsCore.Open strSelect, connData, , adLockPessimistic

rsCore(“Unsubscribed”) = True
rsCore(“UnsubscribedDate”) = Date
rsCore(“UnsubscribedTime”) = Time


Set rsCore = Nothing

to post a comment

13 Comments(s)

Copy linkTweet thisAlerts:
@russellNov 13.2006 — use an update statement to do this.

sql = "UPDATE tblCore SET Unsubscribed = 1, ...."

Copy linkTweet thisAlerts:
@nbcrockettauthorNov 13.2006 — I'd really like to know why the code I posted isn't working since it works on other pages. However, I am interested in your method. Are there any advantages to using it instead of my code? What's the exact translation from mine to yours? Thanks
Copy linkTweet thisAlerts:
@russellNov 13.2006 — what db engine are u using? make sure u response.write strSelect and execute it against db to be sure only one record in rs.

do u explicitly declare cursor location in the ones that work right?
Copy linkTweet thisAlerts:
@nbcrockettauthorNov 14.2006 — what db engine are u using?[/QUOTE]

make sure u response.write strSelect and execute it against db to be sure only one record in rs.[/QUOTE]
Not exactly sure what you're asking

do u explicitly declare cursor location in the ones that work right?[/QUOTE]
Yes and No, depends on the page and what I'm doing.
Copy linkTweet thisAlerts:
@russellNov 14.2006 — well, we would EXPECT thatstrSelect = "SELECT Unsubscribed, UnsubscribedDate, UnsubscribedTime FROM tblCore WHERE UID = " & Session("strUID") would return only one record, so your rs.update should affect only one record. since that is not the case, we need to inspect the recordset for ourselves...

so right after that line, add this:
</i>Response.Write strSelect
then run the page.

Now, open MSACCESS and create a new query (in sql view), paste in the query as displayed on the page and execute it directly.

how many records?
Copy linkTweet thisAlerts:
@nbcrockettauthorNov 14.2006 — The statement shows up correctly when I use.
Response.Write strSelect

I had to use end otherwise I wouldn't have seen the code because the page automatically redirects to another page and I didn't want it updating the whole database.

When I copy the code into access it shows only one record. The only thing I changed was I inserted 10254721 where the Session variable was. That is an actually UID in the database.

ASP Code
[CODE]strSelect = "SELECT Unsubscribed, UnsubscribedDate, UnsubscribedTime FROM tblCore WHERE UID = " & Session("strUID")[/CODE]
Access Code
[CODE]SELECT Unsubscribed, UnsubscribedDate, UnsubscribedTime FROM tblCore WHERE UID = 10254721[/CODE]
Copy linkTweet thisAlerts:
@russellNov 14.2006 — is that the uid displayed on the page after your response.write : response.end ?
Copy linkTweet thisAlerts:
@russellNov 14.2006 — try this
</i>Sub unsubscribe(uid)
Dim cmd
Dim sql

<i> </i>If len(uid) &lt; 1 Then Exit Sub

<i> </i>Set cmd = Server.CreateObject("ADODB.Command")

<i> </i>sql = "UPDATE tblCore " &amp;_
<i> </i> "SET Unsubscribed = true, " &amp;_
<i> </i> "UnsubscribedDate = #" &amp; date() &amp; "#, " &amp;_
<i> </i> "UnsubscribedTime = #" &amp; time() &amp; "# " &amp;_
<i> </i> "WHERE uid = " &amp; uid

<i> </i>With cmd
<i> </i> .ActiveConnection = YOUR_CONN_STRING_HERE
<i> </i> .CommandType = 1 '' adCmdText
<i> </i> .CommandText = sql

<i> </i> .Execute 128 '' adExecuteNoRecords
<i> </i>End With

<i> </i>Set cmd = Nothing
End Sub
Copy linkTweet thisAlerts:
@nbcrockettauthorNov 14.2006 — Russell, thanks for all of your help. Your last response worked and the database is updating properly on that page now. However, if you or anyone else can help me figure out why my original code wasn't working that would be great. Normally I would just be happy that it worked and go about life, but I use this same type of code in several hundred other places. This makes me worried that they may not always be working properly. As you can imagine my clients might not be happy about that. Any ideas and thanks again!
Copy linkTweet thisAlerts:
@russellNov 15.2006 — i'd have to have access to your actual db and code to figure out exactly what's going on. recordset updating is not supported by all db providers, and is dependant on cursor type and location -- might try specifying server-side cursor and see if that fixes original code -- but i can definitely say that a T-SQL Update statement and using the command object is almost always the appropriate way to update a database via ASP.
Copy linkTweet thisAlerts:
@nbcrockettauthorNov 16.2006 — I was able to get it to work by adding the adOpenDynamic cursor. It still bothers me that in other places I haven't had to add a cursor though. Anyway, everywhere I've read suggests that using the method I'm using is better than using the T-SQL Update Statement. Why do you suggest it over the others? Thanks again for your help.
Copy linkTweet thisAlerts:
@russellNov 17.2006 — glad u got it working. generally best to use DML to modify database. not sure who is saying that using recordset updating is best. definitely a valid way to update db, just not the usual way. doesnt work with all providers. some recordsets cant be updated, depending on lock and cursor type. nothing wrong with doing it though.


Help @nbcrockett spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 6.4,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ

legal: ({
terms: of use,
privacy: policy
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
recent_tips: (
tipper: @junehugh,
tipped: article
amount: 500 SATS,

tipper: @buildinteractive,
tipped: article
amount: 1000 SATS,

tipper: @farhadmalegam,
tipped: article
amount: 1000 SATS,