Hi,

This is in relation to one of the mods for snitz. Snitz Personalization. We moved to windows 2008 and are presented with a lot of problems. Most are taken care of fortunately or being taken care of. However, this one I can't figure it out.
Basically the database table is not being updated after the code is ran. The site is reading the tables just fine and is writing to all other tables just fine, just this one is giving me grief. Here is a text version of the asp page that is not updating the database:

http://www.coastercrazy.com/forum/my_edit_config.txt

If I manually add to the table, the site will read the table and display things correctly. However, if you log into the website and click any of the theme switching icons on the home page right below the menu on the right, it will tell you that it was updated successfully, however, it does not because you are still left with the default theme. Below is info to login and test it out for yourself:

Demo Account: SnitzTest
password: testtest

This code was working just fine on windows 2003 and sql server 2000. We moved to windows 2008 and sql server 2008 and this problem presented itself. I have deleted that specific table and recreated it manually and it did not fix the problem. I also deleted that table again and ran the dbs file for it and stil did not solve the problem. I am thinking maybe there is some code incompatability? Can someone please view the code linked above and see if there is anything that should be changed so it is windows 2008 and sql server 2008 compatible?
We thought maybe a trigger of the db was missing so I reexported the stored procs from the original db and imported them back into this db we have live now and still no fix. I appreciate any help you can provide on this situation.
Thank you,
Oscar

Off the bat, it sounds like there is a problem writing to the new table you created. Have you checked the permissions on the table to see if the account you using in config.asp has UPDATE permissions set for the table?

Yes it has UPDATE selected under permissions. There's 3 columns to select for UPDATE: Grant, With Grant and Deny

The Grant tick box is ticked, the remaining two are not. Should I tick box any of the other two?
Thank you,
Oscar

No the Grant should be enough. Does the SQL logs show any errors when you try to switch themes? If not, it may just be the code that is the problem. Would need to step through each UPDATE in the code and see which is failing.

There's no errors in the SQL logs when I try to switch themes. This you can help me out with the code perhaps? This is the file in charge of updating the database when switching themes: http://www.coastercrazy.com/forum/my_edit_config.txt

:)
thank you

it could be to do with the schema in which the table was created, does your new table have the same schema as all the other tables?
i.e
are they all called [dbo].TABLENAME, or is your new table something different like [username].TABLENAME

It is indeed different, it is username.TABLENAME with username being the username I use to connect to the db. However, I did create the table manually and that turned out to be dbo.TABLENAME and then it would not even read the table. It only reads username.TABLENAME

What I mean is after the table was created, I manually added a row to the table to see if it was even reading it right. In the dbo.TABLENAME, it was not reading the table after adding a row. In the username.TABLENAME, which was created by the dbs file, it read the table fine, but it would not write to it.

are you getting any errors returned ?
if you try to execute exactly the same sql in enterprise manager does it work (when logged in as the same user as the forum)?

When running the exact same sql in EM being logged in as the same user as the forum, no errors are being returned.

It does look like a schema / permissions issue The table should have the same schema as the existing forum tables and you should make sure the table is created with the same permissions as the other forum tables, in the exact same schema as the forum ones.

I recreated it using the same schema and permissions as the other tables and the code will not read from that table nor write to it as well

What error message are you getting?

I actually didn't get any error message doing the above. Even if I delete the table (FORUM_MY_CONFIG) it doesn't give an error. It just assumes a value of 0 and shows the default theme.

Well that simply can't be. If your code accesses the table, it would generate errors, unless your code is explicitly ignoring them. You are sure you don't have other versions of the table, under different schemas?

I am certain. You are welcomed to RDC into my server and check it out if you'd like?

Well if you want to email me the details I can try and have a look. Please provide me all the relevant data - remote desktop access and login data, SQL Server data, script name for the code that it is failing.

hi ruirib,

I have sent you a detailed email. I provided contact info as well and if there is any missing info, please reply to it.
Thank you,
Oscar

Got it. I'll see if I can check it later today. Will reply to you by email.

ruirib is awesome! He came in and fixed it all up. Thank you much Rui!

What was the problem Rui?

Well there were two coding errors. First, for some unknown reason, the Snitz connection object was not being used, a new connection was created. To open this connection, instead of passing the connection string, the code was passing the snitz connection object! For some reason, this worked on Windows 2003! The weird thing is that not only the method call to open the connection was not generating any errors, but any subsequent uses of the connection to execute SQL weren't generating errors either! So the code was executing SQL over even non existing database tables without so much as a "complaint"!
Once that was fixed, the desired SQL started to execute. There was another logical error (trying to update a record without being sure it existed, which didn't update at all when the record did not exist), but the major issue was the one with the connection opening method call.

well, that is a strange one for sure

Ahh yes. I see what you're saying now. Just went and look back at the file weeweeslap posted:

Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open my_Conn

It was using it's own connection instead. I see I see.

Yeppers, that was it. I guess this means that you can't even use the argument that a given set of code was working well before, when problems arise. You just need to question it all!! Not a reassuring thought!!

Happy Thanksgiving, thank you for resolving this error

You're welcome. We do not celebrate Thanksgiving here, but I hope you have a Happy one yourself.