Select Statement not updating database correctly

@nbcrockettNov 13.2006

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!

[CODE] 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

rsCore.Update

rsCore.Close Set rsCore = Nothing [/CODE]

to post a comment

13 Comments(s) _↴

@russellNov 13.2006 — #use an update statement to do this.

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

connData.execute(sql)

@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

@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?

@nbcrockettauthorNov 14.2006 — #what db engine are u using?[/QUOTE]
Access

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.

@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>
 </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?

@nbcrockettauthorNov 14.2006 — #The statement shows up correctly when I use.

[CODE]
 Response.Write strSelect
 Response.End
 [/CODE]

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]

@russellNov 14.2006 — #is that the uid displayed on the page after your response.write : response.end ?

@nbcrockettauthorNov 14.2006 — #yes

@russellNov 14.2006 — #try this

<i>
 </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

@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!

@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.

@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.

@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.

Success!

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

Tweet This

Select Statement not updating database correctly

13 Comments(s) _↴

Success!

Social

Version

Select Statement not updating database correctly

13 Comments(s) ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

13 Comments(s) _↴