error converting the varchar value 'True' to a column of d..

In a vb6 app using ado connecting to a sql server 2000 stored procedure,
am seeing this error:
"Syntax error converting the varchar value 'True' to a column of data
smallint"

The error only seems to happen when updating one record. Other
records work fine. The stored proc takes 22 varchar parameters, 1 smallint
parameter,
and one int parameter

Is this indicating a problem with the varchar parameters or the smallint
parameter?

any ideas?

Thanks,
dk

"dk" <dk.TakeThisOut@discussions.microsoft.com> wrote in message
news:3F3A0B54-B4DA-4806-9E6C-6535BAB4D78D@microsoft.com...
 > In a vb6 app using ado connecting to a sql server 2000 stored procedure,
 > am seeing this error:
 > "Syntax error converting the varchar value 'True' to a column of data
 > smallint"
 >
 > The error only seems to happen when updating one record. Other
 > records work fine. The stored proc takes 22 varchar parameters, 1 smallint
 > parameter,
 > and one int parameter
 >
 > Is this indicating a problem with the varchar parameters or the smallint
 > parameter?
 >
 > any ideas?

The value True (in VB) will implicitly convert to any integer type, because
it's literally represented as -1. The string "True" (in VB) can be coersed
to the value True (in VB) using
CBool(variable_that_resolves_to_string_true).

Afaik, there is no symbolic value TRUE in SQL, the Boolean concept exists,
expressions evaluate as true or false, but there are no symbolic constants
that represents boolean values. Try this in Query Analyzer:

if TRUE
print 'in truth there is meaning' --(Returns a syntax error.) Otoh,
this:

if (1 = 1)
print 'in truth there may be meaning' --(Prints text as expected, 1 = 1
is a true expression.)

As such, the non-numeric string 'True' cannot be converted to an integer in
T-SQL, except perhaps with a case statement in the expression, e.g., CASE
WHEN @param = 'True' THEN 1 ELSE 0 END (but that doesn't really count.)

So, to answer your question directly, the problem is that non-numeric string
values are being passed in varchar parameters that are intended for
conversion to interger field values. What is likely happening is that a
Boolean variable (or variant/vt_boolean) is being implicitly or explicitly
converted to a string (the result of which is the string 'True'.) You must
make sure that any VB boolean values are explicitly coersed to their numeric
equivilents before passing to SQL, e.g.,

Dim b as Boolean
Dim p as ADODB.Parameter ' Value property is of type variant
[skipping init]
p.Value = CStr(CInt(b))

If you merely coerse to an integer type, ADO will whine about type mismatch.
If you merely assign p.Value = b, or do something less sophisticated like:

Dim s as String
s = b
p.Value = s ' this, btw, would be a kludge

you'll end-up with the string value 'True' on the SQL side, which as you've
observed, is problematic.


Hope this helps,
Mark


 > Thanks,
 > dk
 ><!-- ~MESSAGE_AFTER~ -->