Updating recordsets in MS SQL 2005 not working

[julian77]

Hi,

I have recetly migrated from SQL 2000 to SQL 2005 and certain .asp-code that was working before, is no longer working.

Retrieving, inserting, deleting recordsets are no problem, but when I try to update I run into problems.

The following code works:

"UPDATE tblsomething SET column_name = 'something' "

But in all my pages I have been using:

rs.Open "SELECT * FROM tblSomething WHERE column_name=" & RequestSomething, connection, 1,3

and then I'll list the columns

rs.Fields("column_name") = Request.Form("form_name")
-
-
rs.Update

And this approach does not work. Since I am primarily a designer with programming knowledge on a need-to-know-basis, I don't know if something changed between the MS SQL versions? And I am not so keen on changing my code to the working solution in all those pages...

Since the error message is in Swedish I can't just post it but is says something about not finding the sql server in sysservers and tells me to run sp_addlinkedser ver to add servers into sysservers?

Does this make any sence to anyone?

Any help appreciated.

//Jesper

[arbert]

Need a little bit more to go on--such as your open statements for the database connection and the recordset.

[julian77]

Need a little bit more to go on--such as your open statements for the database connection and the recordset.

Arbert,

Thanks for your reply.

The connection is working, I can view, insert, etc recordsets from the database in SQL 2005. The problem arises when I try to update.

Connection looks generically like:

Set DB = Server.CreateObject("ADODB.Connection") 
SQL = "driver={SQL Server};SERVER=ip-number;UID=userid;PWD=password;database=databasename"

And the recordset:

Set rsUpdate = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tblName WHERE ID=" & Request("ID")
rsUpdate.Open strSQL, DB,1,3

Then I list the field names I wan to update:

rsUpdate.Fields("column_name") = Request.Form("form_name")
... etcetera

And try to update:

rsUpdate.Update

This exact code works towards an MS SQL 2000 database, but not MS SQL 2005 which I have with my new hosting company.

Any ideas why not? Thanks,

/Jesper

[arbert]

Arbert,

Thanks for your reply.

The connection is working, I can view, insert, etc recordsets from the database in SQL 2005. The problem arises when I try to update.

Connection looks generically like:

Set DB = Server.CreateObject("ADODB.Connection") 
SQL = "driver={SQL Server};SERVER=ip-number;UID=userid;PWD=password;database=databasename"

And the recordset:

Set rsUpdate = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tblName WHERE ID=" & Request("ID")
rsUpdate.Open strSQL, DB,1,3

Then I list the field names I wan to update:

rsUpdate.Fields("column_name") = Request.Form("form_name")
... etcetera

And try to update:

rsUpdate.Update

This exact code works towards an MS SQL 2000 database, but not MS SQL 2005 which I have with my new hosting company.

Any ideas why not? Thanks,

/Jesper

Ouch. I know it doesn't directly answer your question, but why are you performing an update like that??? It's slow and has a lot of overhead--you should think about passing a CONN.EXECUTE statement or best yet--use a stored proc for the update.

Brett
Perfect Computing, Inc

[gardnose]

Did you find a solution to this problem? I've just come up against the same thing. I'd prefer not to have to rewrite lots of old code just now.

[debasisdas]

Did you find a solution to this problem? I've just come up against the same thing. I'd prefer not to have to rewrite lots of old code just now.

Hi
gardnose
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow the posting guidelines in every new post/reply.