Anyone use ODBC Scalar Functions??

bigfella · Nov 6, 2000

I'm having trouble understanding scalar functions. Can anyone tell me why this doesn't work....

SELECT * FROM LiveClient
WHERE {fn UCASE(customer) } LIKE '%#UCase(form.clientname)#%'

It produces a 'Data type mismatch in criteria expression' error.

The field (customer) and form var (clientname) are both strings!!??

bankholdup · Nov 6, 2000

What happens if you try it without the '' around the variable?

bankholdup

bigfella · Nov 6, 2000

I've tried a few variations, but it's the scalar function it doesn't like.

If i do away with the scalar and the CF UCase() function and enter the form.clientname in the correct case, then it works!!

bjkersey · Nov 7, 2000

The following worked for me:
<HTML>
<HEAD>
<TITLE>Retrieving Employee Data Based on Criteia from Form</TITLE>
</HEAD>
<cfset username = 'B'>
<BODY>
<CFQUERY NAME="GetEmployees" DATASOURCE="ActionItems">
SELECT userID, userName, fullName, email, password
FROM users
WHERE ucase(fullName) like '%#ucase(username)#%'
</CFQUERY>
<H4>Employee Data Based on Criteria from Form</H4>
Last Name: <INPUT TYPE="Text" NAME="fullName" SIZE="20"
MAXLENGTH="35"><br>

<CFOUTPUT QUERY="GetEmployees">
The query returned #GetEmployees.RecordCount# records.
</CFOUTPUT>

<TABLE border=1>
<TR>
<TH>User ID</TH>
<TH>User Name</TH>
<TH>Full Name</TH>
<TH>Email</TH>
<TH>Password</TH>
</TR>
<CFOUTPUT QUERY="GetEmployees">
<TR>
<TD>#userID#</TD>
<TD>#userName#</TD>
<TD>#fullName#</TD>
<TD>#email#</TD>
<TD>#password#</TD>
</TR>

</CFOUTPUT>
</TABLE>

</BODY>
</HTML>

Bill Kersey

bigfella · Nov 8, 2000

Thanks Bill, that works.

Who needs scalar functions, eh!!!!??

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Anyone use ODBC Scalar Functions??

bigfella

IS-IT--Management

bankholdup

Programmer

bigfella

IS-IT--Management

bjkersey

Programmer

bigfella

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor