Update Issue

Question

Update Issue

bobbabuoy

Old Hand

Points: 378
More actions
February 8, 2005 at 3:47 pm

#92231
I am having trouble updating a sql server 2000 recordset from an asp page. I know the sql is good because I use the same one to retrieve the data that I want to update (using an execute method) but I get "Invalid Column Name" for a clearly valid column name when I try to update it using rs.open. Could it be my locks or cursor type?
Here is my code in case there is a brain cramp in there that I am missing:
```
code:
Set rs=Server.CreateObject("ADODB.Recordset")sql = "SELECT rr.RaceLogRsltsID, rr.FinalTime, rg.RaceDist, rg.RaceType, rg.EventName, rg.EventDate, rg.RaceLogID "sql = sql & "FROM RaceLog rg INNER JOIN RaceLogRslts rr ON rr.RaceLogID = rg.RaceLogID "sql = sql & sWHERE & " ORDER BY " & sFinalSortrs.Open sql, conn, adOpenKeyset, adLockOptimistic
```
It tells me that the rg.RaceDist is an invalid column name.
Thanks in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

An update statement may only target one table.

I don't know what the update statement looks like, but it sounds as if you're trying to update RaceLog and RaceLogRslts in one statement.

If that is the case, then you will need to split the update statement into two parts, one updating each table.

Could you post the update statement here please.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bobbabuoy Old Hand Points: 378 More actions · Answer 2

Yes that is what I am doing. I did not know that was not allowed. Thanks for your help!

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 3

Set rs=Server.CreateObject("ADODB.Recordset")

sql = "SELECT rr.RaceLogRsltsID, rr.FinalTime, rg.RaceDist, rg.RaceType, rg.EventName, rg.EventDate, rg.RaceLogID "

sql = sql & "FROM RaceLog rg INNER JOIN RaceLogRslts rr ON rr.RaceLogID = rg.RaceLogID "

sql = sql & sWHERE & " ORDER BY " &

sFinalSortrs.Open sql, conn, adOpenKeyset, adLockOptimistic

Can u check whether this sql is correct !!!. I think there is wrong in SQL Statment

My Blog: http://dineshasanka.spaces.live.com/

bobbabuoy Old Hand Points: 378 More actions · Answer 4

The sql statement is correct-at least it returns the correct statement in response.write (although you are missing a line break after sFinalSort)

Deborah Van Wright Newbie Points: 3 More actions · Answer 5

When I had this error (and could plainly see that the column existed), I was advised to rename the column. The reason was that there could have been some hidden control characters accidentally included in the name. I found that hard to believe but once I renamed it (and then gave it the real name again), it worked. Maybe it will work for you too...?

bobbabuoy Old Hand Points: 378 More actions · Answer 6

I haven't had a chance to check the solution that involved splitting the updates by table. Once I try that, if that doesn't work, I will try to rename the table.

Thanks for the advice!