/    Sign up×
Community /Pin to ProfileBookmark

Database Update

I’ve got a webpage that is supposed to update a database, but it’s not working. I’ve used this exact same code on another page and it works fine. I’ve narrowed the problem down to the point that when you tell the recordset to update (recordset.Update) it just wont. I need to have this fixed ASAP so any help would be appreciated. Thanks!

to post a comment

6 Comments(s)

Copy linkTweet thisAlerts:
@chrismartzMar 21.2006 — Please post some code so we can see the exact line.
Copy linkTweet thisAlerts:
@nbcrockettauthorMar 22.2006 — Set rsCore = Server.CreateObject("ADODB.Recordset")

strSelect = "SELECT * FROM tblCore WHERE UID LIKE '" & Session("strUID") & "'"

rsCore.Open strSelect, connEdenCrest, , adLockPessimistic

rsCore("Unsubscribed") = True

rsCore("UnsubDate") = Date

rsCore("UnsubTime") = Time

rsCore.Update

rsCore.Close

Set rsCore = Nothing
Copy linkTweet thisAlerts:
@nbcrockettauthorMar 23.2006 — Ok, new turn of events. I know the code I posted earlier works. I've used it in 100 plus other places. Why it's not working now is a mystery to me and if anyone can tell me why it's not working that would be great. My new question is about what happened when I was randomly changing code trying to get it to work. The first thing I tried was changing the Select statement. Instead of using a wildcard to pull all the fields from the table I typed out the fields I needed.

[INDENT]Old Code:[/INDENT]

strSelect = "SELECT * FROM tblCore WHERE UID LIKE '" & Session("strUID") & "'"

[INDENT]New Code:[/INDENT]

strSelect = "SELECT UID, Unsubscribed, UnsubDate, UnsubTime FROM tblCore WHERE UID LIKE '" & Session("strUID") & "'"

When I made this change the information started updating to the database, but not porperly. It updated the three fields I wanted it to, but it did it to all the records in the database even though the Where part of the Select statement was telling it to only pull one record. When this happened I thought maybe there's something wrong with the Where part of the statement, so I wrote a little loop into my code. The loop wrote to the screen all of the records that were in the recordset that I was creating.

[INDENT]Loop Code:[/INDENT]

Do While Not rsCore.EOF

rsCore("UID")

rsCore.MoveNext

Loop

When I did this it only showed the one record that I wanted to update on the screen, but was still updating every record in the database.

Ok, lets make this more interesting. This page I'm working on is to unsubscribe from something. I have an almost identical page that's used for resubscribing and it was giving me the same problem of not updating the database. The only difference on the two pages is that the unsubscribe page fills in values for the three fields while the resubscribe page removes them. I tried the same thing on the resubscribe page and got a different result. This time it removed the values like it was supposed to, but it removed it in only three different records. Not one like it was supposed to, not all like it was doing on the unsubscribe page, but just three. The three it changed were identical records except the UID field. The UID field had a single digit that was different in each one (ex. 10254721, 20254721, 30254721). I wrote the loop code into this page just to make sure it was pulling only the one record I wanted it to and it was. At this point I just about lost it and trashed my computer. Instead I decided to try something random again. This time I added another field to the Select statement. This one I wasn't using on this page, but at this point I was ready to kill someone so it didn't matter.

[INDENT]New Code:[/INDENT]

strSelect = "SELECT UID, BusinessType, Unsubscribed, UnsubDate, UnsubTime FROM tblCore WHERE UID LIKE '" & Session("strUID") & "'"

I really didn't think it could get any stranger, but I was wrong. This new field changed what records were being updated. It was no longer updating all records, but instead was updating only the records that had the same business type as the UID in the Where part of the statement. At this point it appeared to be using the fields choosen in the Select statement as a filter, so I just added more of the fields into the list. This next piece of code finally made it only update the one record that I wanted it to.

[INDENT]New Code:[/INDENT]

strSelect = "SELECT UID, FirstName, MiddleName, LastName, Address1, Address2, City, State, Zip, Unsubscribed, UnsubDate, UnsubTime FROM tblCore WHERE UID LIKE '" & Session("strUID") & "'"

I would really like to understand why all of this happened, so if anyone can shed some light I would be extremely grateful!!! Thanks to anyone who's brave enough to take a stab at it and anyone who actually read the whole thing. Let me know if you have any questions.

Thanks,

Nick Crockett
Copy linkTweet thisAlerts:
@RibeyedMar 23.2006 — Hi Nick,

i think all the results you are getting are correct because you are using LIKE in your statement. I would hazard a guess that its the way the database determines how alike the data in one field is like the data in another.

If you are using the session ID which is unique to each session can't you not just use equal to (=) instead of LIKE?
Copy linkTweet thisAlerts:
@nbcrockettauthorMar 23.2006 — You're correct about using = instead of LIKE, this is a unique number tied to a single individual. If I change LIKE to = then it works the same. If I then take out the list of fields and replace it with the * wildcard it doesn't update anymore like it was doing before. If I put the field list back in, but take out the ones I don't need then it updates all records like it was doing before. In all of these cases the recordset is only pulling one record, which happens to be the one that I need it to update. I have always been under the impression that it would only update the records that were part of the recordset pulled. If that's the case why is it updating records that aren't in the recordset. Thanks for your help!
Copy linkTweet thisAlerts:
@RibeyedMar 23.2006 — Hi,

ok i think it maybe that you are telling the code to update fields that you don't want to. I do agree that the behaviour you are experience is not normal so can i sujects a code change?

<i>
</i> DBConn.Execute ("UPDATE tblCore (Unsubscribed, UnsubDate, UnsubTime) VALUES ('True', '"&amp;Date()&amp;"', '"&amp;Time()&amp;"')")

×

Success!

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 3.29,
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: @darkwebsites540,
tipped: article
amount: 10 SATS,

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

tipper: Anonymous,
tipped: article
amount: 10 SATS,
)...