By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Updating recordsets in MS SQL 2005 not working

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. "UPDATE tblsomething SET column_name = 'something' "
But in all my pages I have been using:

Expand|Select|Wrap|Line Numbers
  1. rs.Open "SELECT * FROM tblSomething WHERE column_name=" & RequestSomething, connection, 1,3
and then I'll list the columns

Expand|Select|Wrap|Line Numbers
  1. rs.Fields("column_name") = Request.Form("form_name")
  2. -
  3. -
  4. 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_addlinkedserver to add servers into sysservers?

Does this make any sence to anyone?

Any help appreciated.

//Jesper
May 5 '06 #1
Share this Question
Share on Google+
5 Replies


P: 6
Need a little bit more to go on--such as your open statements for the database connection and the recordset.
May 7 '06 #2

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. Set DB = Server.CreateObject("ADODB.Connection") 
  2. SQL = "driver={SQL Server};SERVER=ip-number;UID=userid;PWD=password;database=databasename"
And the recordset:

Expand|Select|Wrap|Line Numbers
  1. Set rsUpdate = Server.CreateObject("ADODB.Recordset")
  2. strSQL = "SELECT * FROM tblName WHERE ID=" & Request("ID")
  3. rsUpdate.Open strSQL, DB,1,3
Then I list the field names I wan to update:

Expand|Select|Wrap|Line Numbers
  1. rsUpdate.Fields("column_name") = Request.Form("form_name")
  2. ... etcetera
And try to update:

Expand|Select|Wrap|Line Numbers
  1. 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
May 7 '06 #3

P: 6
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:

Expand|Select|Wrap|Line Numbers
  1. Set DB = Server.CreateObject("ADODB.Connection") 
  2. SQL = "driver={SQL Server};SERVER=ip-number;UID=userid;PWD=password;database=databasename"
And the recordset:

Expand|Select|Wrap|Line Numbers
  1. Set rsUpdate = Server.CreateObject("ADODB.Recordset")
  2. strSQL = "SELECT * FROM tblName WHERE ID=" & Request("ID")
  3. rsUpdate.Open strSQL, DB,1,3
Then I list the field names I wan to update:

Expand|Select|Wrap|Line Numbers
  1. rsUpdate.Fields("column_name") = Request.Form("form_name")
  2. ... etcetera
And try to update:

Expand|Select|Wrap|Line Numbers
  1. 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
May 16 '06 #4

P: 1
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.
Jun 7 '07 #5

debasisdas
Expert 5K+
P: 8,127
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.
Jun 7 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.