datasource question

[news.microsoft.com]

Hi
I would preciate any help on calling for a fuction inside a datasource
string using visual basic.

for example:

I have one function that evaluates some condition in each recordset and
would like to create something like this:
mydatacontrol.recordsource = "select * from mytable where
myfunction(variable)='valor'"

the main idea is to replace all the 'Á' by an 'A', the 'é' by an 'e' and so
on calling the function

if i use the expression above calling myfunction i get a error
here is my code for myfunction

Public Function myfunction(cadeia As String)
Dim cacentos
Dim sacentos
Dim nciclo As Integer
Dim acento
cacentos = "áéíóúÁÉÍÓÚãõÃÕâêîôûÂÊÎÔÛçÇàèìòùÀÈÌÒÙ"
sacentos = "aeiouAEIOUaoAOaeiouAEIOUcCaeiouAEIOU"
For nciclo = 1 To Len(cadeia)
acento = InStr(cacentos, Mid(cadeia, nciclo, 1))
If acento <> 0 Then
cadeia = Left(cadeia, nciclo - 1) & Mid(sacentos, acento, 1) &
Mid(cadeia, nciclo + 1)
End If
Next
myfunction= cadeia
End Function

 

[Ray at]

Wow, how many languages do you speak? Portuguese? German? Spanish?
Italian?

This isn't a classic ASP issue, as indicated by your code. I almost follow
what you're saying, and if I do:

mydatacontrol.recordsource = "select * from mytable where " &
myfunction(variable) & "='valor'"

Ray at work

 

[news.microsoft.com]

That will not evaluate each record.
I should write record instead of value
Thanks anyway

 

[news.microsoft.com]

reformulating example :

mydatacontrol.recordsource = "select * from mytable where
myfunction(recordset.field)='valor'"

 

[MSFT]

Hello,

We can have the usedr defined function "myfunction" in the query string
directly. The query string will be send to database and database can't
recognize it. If your database is SQL server 2000, you may define a UDF in
the database to take place the function. If not, you may change your query
to:

mydatacontrol.recordsource = "select * from mytable where fieldname='válór'"

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Chris Hohmann]

reformulating example :

mydatacontrol.recordsource = "select * from mytable where
myfunction(recordset.field)='valor'"

[SQL Server 2000]
SELECT * FROM mytable WHERE myfield = 'valor'
COLLATE SQL_Latin1_General_Cp850_CI_AI

[Access/SQL Server 7]
SELECT * FROM mytable WHERE myfield LIKE 'v[aáãâà]l[oóõôò]r'

In the future please indicate which database and version you are using.
If you are using SQL Server 2000, there is a topic on the COLLATE clause
in Books Online(BOL).

HTH
-Chris Hohmann

 

[MSFT]

My previous post may be too brief on SQL server UDF and I should to address
more on it:

SQL User-Defined Functions allow you to define your own Transact-SQL
functions using the CREATE FUNCTION statement. It take zero or more input
parameters, and return a single value. Some user-defined functions return a
single, scalar data value, such as an int, char, or decimal value. For more
information on Sql server UDF, you may refer to:

Microsoft SQL Server 2000 User Defined Functions Whitepaper
http://support.microsoft.com/default.aspx?scid=/support/sql/content/
2000papers%2fUser_defined_functions.asp

If you find that UDF's are not a solution for you, , you may refer to
Chris's suggestion. It is also a wonderful solution for this issue. Thank
for Chirs!

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[khudyaev]

Hollo!