Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhudson
Starting Member
4 Posts |
 Posted - 2011-07-27 : 09:19:19
|
| Hello:I am having trouble with a newly setup server on which I am trying to run my classic ASP/SQL Server 2005 application. The page I am having trouble with has the following code. qry="SELECT * FROM Users WHERE UserNumber = 1 or TempUserNumber = 1 AND SiteName IN('plexcor', 'common')"rs.Open qry, Connect, adOpenStatic, adLockOptimistic, adCmdTextrs("TempUserNumber")=0rs.Updaters.CloseIf the line rs("TempUserNumber")=0 is commented out the update works fine. If it is not commented out it generates a runtime error 3219. (Operation is not allowed in this context.) Updating or inserting records via a SQL Query statement works fine (ex INSERT or UPDATE) - only using the Update method of the recordset object seems to fail. Is there some type of permissions I need to change to make this work?Regards,David |
|
|
dhudson
Starting Member
4 Posts |
Posted - 2011-07-28 : 09:01:48
|
| Does anyone have an idea what is going on with this problem? I really need to get it resolved and Lunarpages support has not been real helpful. Any and all replies welcome.Regards,David |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2011-07-28 : 09:26:48
|
| It's been a while since I have seen classic ASP code, so I can't tell you why you are seeing this error...I can guess, however, that your recordset may be empty. Perhaps you should check if there are no records returned before trying to update the field?There's also an alternative. Why don't you try executing an UPDATE statement directly, instead of loading up a recordset simply to update one field. Something like this would do the same thing:qry="UPDATE Users SET tempUserNumber = 0 WHERE UserNumber = 1 or TempUserNumber = 1 AND SiteName IN('plexcor', 'common')"Connect.Execute qryA couple of other things. Your code seems to assume that your query will only return one record. What if there are multiple (or no records)? Also, make sure you have brackets around the distinct parts of your WHERE clause. You can imagine that WHERE (UserNumber = 1 or TempUserNumber = 1) AND SiteName IN('plexcor', 'common') is quite different from WHERE (UserNumber = 1) or TempUserNumber = 1 AND SiteName IN('plexcor', 'common')OS |
|
|
|
dhudson
Starting Member
4 Posts |
Posted - 2011-07-28 : 11:10:54
|
| mohdowais:Thanks for your input. I agree some of it is not written as well as it could be - and there are uses of the direct UPDATE clause being used in the code in conjunction with the connect.execute (which does work by the way.) A little background. This is part of a custom CMS system (Classic ASP front end/SQL Server 2005 backend) hosted on a dedicated server at Lunarpages. The company I work for is going out of the hosting business but we have one customer who wants to keep using the custom CMS until they can get a new website. They leased a dedicated server also at Lunarpages - a little less powerful one but setup the same way or so I thought. (Server 2003 O/S - SQL Server 2005 Workgroup). I moved our software over to the new server and put up a small test site to verify operation. This is when all this started. I am sure it is some type of permission issue when doing updates via recordsets. (maybe something that was on by default before and is now off). So the main point is this is an application that is working on a different server at the same provider but not working on the new server. Sorry for the long winded reply.Regards,David |
|
|
|
dhudson
Starting Member
4 Posts |
Posted - 2011-08-01 : 13:23:14
|
| Hi Everyone:Wanted to post the solution to this problem. The connection string had to be changed. Switching from Driver={SQL Server} to Driver={SQL Native Client} did the trick. Turns out the working database had a Service pack one level down from the latest server. Regards,David |
|
|
|
|
|
|
|
|
Errors on ASP page with ADO update