Column names as Variable SQL problem...

[pgferro]

I have a table where I have to refer to the column names through some varibales. Everything works fine for almost everything (yeah, almost is alway the problem...) but when i try to use the variable name to perform a calculation, i get a type mismatch error, meaning that the statement is considered litteraly and not by the specific column value.
Example :

Sql="UPDATE MyTable SET "& ColumnVariable & "=" & ColumnVariable+1 & " WHERE SomeField=SomeValue"

By the way, I get the error exactly at the sql line position, so that I cannot response.write the statement..

Any way to get around this ??

THANKS !!

 

[NoCoolHandle]

Sql="UPDATE MyTable SET "& [RED]ColumnVariable[/RED] & "=" & [RED]ColumnVariable[/RED]+1 & " WHERE SomeField=SomeValue"

My best guess is the variable ColumnVariable (in red) is Null or an Empty string...
Therefore it can't be added to 1 and or won't let you concatinate the string... and or it is trying to add a string to an number and you should do an explicit cast..

However I would put money on it being empty.. and not adding with 1

Rob

 

[pgferro]

No, all values are initialized as 0 (zero), and running the same query in access or query analizer, works like a charm...

Problem is that in the statement the calculation is evaluated as a string and not as the actual value of the column..

 

[ChrisHirst]

just because it works in access doesn't mean it will work in vbScript. Access seems to accept just about anything you throw at it MS covering for the expected knowledge level of the average user maybe? or maybe they would put it more like, Assisting the users to make a more robust interface.

You can do calcs with strings and it seems to do an implicit typecast first.

assuming your column names are numbers and the field type is integer casting the 2nd instance of ColumnVariable to an integer first should work ok.

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[pgferro]

Thanks, I solved the problem :

Sql="UPDATE MyTable SET "& ColumnVariable & "=" & [COLOR=red]Eval(ColumnVariable)[/color red]+1 & " WHERE SomeField=SomeValue"