update a table column from Request.Form using TSQL 1

[cr84net2]

This TSQL is killing me... I was able to update one table using the same Request.form fields. Now the next update is giving me an error. This is the update statement:

ltUpdateSql = "UPDATE lteam SET ltID= sID WHERE user=" & spID

conn.Execute(ltUpdateSql)

DB connection is opened above
and sID and spID are from:

sID=Request.Form("ID")
spID=Request.Form("playerID")

I am receiving the error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Invalid column name 'sID'.

Both of the fields from Request.Form are numbers although as I have recently learned they may not be perceived as such.

I have tried:

ltUpdateSql = "UPDATE lteam SET ltID=" & sID & "WHERE user=" & spID

This does nothing obviously.

How do I let the statement know this is not a column name.. Any help with this would be greatly appreciated.

 

[gmmastros]

do you have access to Query Analyzer? I hope you do. Can you run this query and post the results back here.

Select Column_Name, Data_Type
From   Information_Schema.Columns
Where  Table_Name = 'lteam'

This will let us know what the data types for the columns are. With this information, we should be able to help you resolve this problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cr84net2]

George, you need a white horse and a little mask, so people can say,"Who was that masked man!"

Here are the lteam data types:

Column_Name Data_Type
ID int
weightclass int
wrestlername nvarchar
wrestlerID int
userlevel int
user int
points int
ltID int

Thanks for all of your help. I am reading like crazy and trying to learn this stuff..I didn't know it was so much different than working with access!!

 

[gmmastros]

I have tried:

ltUpdateSql = "UPDATE lteam SET ltID=" & sID & "WHERE user=" & spID

Unfortunately, in this case, I think something as simple as a missing space is causing your problem. You need to put a space between the Quote and the WHERE. Like this...

ltUpdateSql = "UPDATE lteam SET ltID=" & sID & "[!](Put a space here)[/!]WHERE user=" & spID

Without the space, you would end up with....

[tt][blue]UPDATE lteam SET ltID=3WHERE user=2[/blue][/tt]

With the space...

[tt][blue]UPDATE lteam SET ltID=3 WHERE user=2[/blue][/tt]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cr84net2]

Thanks George...

When I input the space i get this error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Conversion failed when converting the nvarchar value 'dbo' to data type int.

Huh?

I have seen this error before haven't I. Is it as simple as:

ltUpdateSql = "UPDATE lteam SET ltID=" & CAST(sID AS int) & " WHERE user=" & spID

No...I tried that and got the same error..

Any suggestions?

Thanks again.

 

[gmmastros]

There is something weird going on. Do you have any triggers on this table?

Run this to find out.

[COLOR=blue]Select[/color]  [COLOR=blue]Name[/color] [COLOR=blue]As[/color] TriggerName, 
        [COLOR=#FF00FF]Object_Name[/color](parent_obj) [COLOR=blue]As[/color] Table_Name
[COLOR=blue]From[/color]    SysObjects 
[COLOR=blue]Where[/color]   xtype = [COLOR=red]'TR'[/color]
        And [COLOR=#FF00FF]Object_Name[/color](parent_obj) = [COLOR=red]'lteam'[/color]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh crap. Nevermind about that trigger business. I thought that you may have a mis-behaving trigger that is causing your problem.

I finally figured this out. user is a reserved work in T-SQL. As such, the only way to get this to work is if you use square brackets around the column name. Unfortunately, you will need to do this EVERYWHERE you use this column.

ltUpdateSql = "UPDATE lteam SET ltID=" & sID & " WHERE [!][[/!]user[!]][/!]=" & spID

If this doesn't solve your problem, I'm gonna eat my shoe!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cr84net2]

Who was that Masked Man!"

Thanks George,,I'm glad you didn't get to taste shoe leather..that did the trick!

I really appreciate all of your help!!