HStrix
asked on
Asp.Net: private function in SQL SELECT statement
Hello experts,
in my Asp.Net (VB.Net) application
I'm using an SQL select statement like
strSQL = "select * from table where (field = '" & strCompare & "'")
but I need an SQL statement like
strSQL = "select * from table where (MyFunction(field) = '" & strCompare & "'")
Private Function MyFunction(strField as String) as String
...
myfunction = "xxx"
End Function
I cannot use a function for strCompare.
I couldn't find a way to have for this a proper syntax.
If anyone knows a solution please supply appropriate [snippet] information.
The database behind is MS Access.
Thank you very much!
HStrix
in my Asp.Net (VB.Net) application
I'm using an SQL select statement like
strSQL = "select * from table where (field = '" & strCompare & "'")
but I need an SQL statement like
strSQL = "select * from table where (MyFunction(field) = '" & strCompare & "'")
Private Function MyFunction(strField as String) as String
...
myfunction = "xxx"
End Function
I cannot use a function for strCompare.
I couldn't find a way to have for this a proper syntax.
If anyone knows a solution please supply appropriate [snippet] information.
The database behind is MS Access.
Thank you very much!
HStrix
I don't see where your returning a value from your function?
also just run the function directly before the SQL statement and then assign an intermediary variable the value the function returns.
Regards,
Aeros
Regards,
Aeros
I am not quite sure what do you need this function there. Looks like it should return a column name. Am I right?
If so - you could do:
strSQL = "select * from table where (" & MyFunction(field) & "= '" & strCompare & "'")
If so - you could do:
strSQL = "select * from table where (" & MyFunction(field) & "= '" & strCompare & "'")
Could you use a UDF in sql server? (User Defined Function), or do you have to have this function in VB.
in SQL Server yes - but this is MS Access
ASKER
Perhaps I gave to little information;
"field" represent a database field in the table used in the FROM clause.
For
strSQL = "select * from table where (" & MyFunction(field) & "= '" & strCompare & "'")
I'm getting the error that "field" is not declared.
"field" represent a database field in the table used in the FROM clause.
For
strSQL = "select * from table where (" & MyFunction(field) & "= '" & strCompare & "'")
I'm getting the error that "field" is not declared.
You can do that too in Access, you just have to write the function in an Access Module.
ASKER
As I mentioned in my question,
the statement is in an Asp.Net (VB.Net) application.
I don't understand what can do in Access.
the statement is in an Asp.Net (VB.Net) application.
I don't understand what can do in Access.
ASKER
The function "MyFunction" also is part of the Asp.Net (VB.Net) application.
So you have to write it in an Access Module as raterus says. Than you can use it in SELECT statement
I understand that, but you can't (when you define the query), pull out a returned field from the query. This is not functional syntax.
strSQL = "select * from table where (" & MyFunction(field) & "= '" & strCompare & "'")
What you have to do is first get the results using plain old field, then make a temporary table using your function, then perform the original match.
strSQL = "select * from table where (" & MyFunction(field) & "= '" & strCompare & "'")
What you have to do is first get the results using plain old field, then make a temporary table using your function, then perform the original match.
This shoul help you:
http://www.di-mgt.com.au/classes.html
http://www.di-mgt.com.au/classes.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I understand your comments as follows:
In Access I write the function MyFunction in a module MyModule.
But this means, the SQL statement needs to be modified to ???
strSQL = "select * from table where (MyFunction(field) = '" & strCompare & "'")
This looks like a Stored Procedure .. ??
But it cannot be
strSQL = "select * from table where (" & MyFunction(field) & "= '" & strCompare & "'")
Please supply a little bit more information.
In Access I write the function MyFunction in a module MyModule.
But this means, the SQL statement needs to be modified to ???
strSQL = "select * from table where (MyFunction(field) = '" & strCompare & "'")
This looks like a Stored Procedure .. ??
But it cannot be
strSQL = "select * from table where (" & MyFunction(field) & "= '" & strCompare & "'")
Please supply a little bit more information.
I don't understand what you mean, nothing in that looks like a stored procedure. Oh yeah though, make your function "Public" declared in the module though!
What are you trying to accomplish with MyFunction(field) ?
I'm trying to think of an example of why you would do that.
Just an example:
I could assume maybe you have a SocialSecurity field that stores the data as
xxx-xxx-xxxx
And the string your app wants to compare gets passed in as xxxxxxxxxx so you want to format them identically?
Why not format the variable instead of the field?
I just can't figure out why you would want to run a function on a database field then compare a string value to it.
Need more input.
I'm trying to think of an example of why you would do that.
Just an example:
I could assume maybe you have a SocialSecurity field that stores the data as
xxx-xxx-xxxx
And the string your app wants to compare gets passed in as xxxxxxxxxx so you want to format them identically?
Why not format the variable instead of the field?
I just can't figure out why you would want to run a function on a database field then compare a string value to it.
Need more input.
ASKER
The problem is related to a date field with different format based on the culture.
So I cannot format the variable, what I made on other places in my application.
So I cannot format the variable, what I made on other places in my application.
You should be storing dates in a "date" format in the server, also it should NEVER be the job of the database to format the dates in a format the end user will be viewing. If you don't follow these rules, well you get the problems you are running into.
ASKER
Thanks to everyone,
I came to the conclusion solving my problem I need to create a new field.
In addition, I agree with your comment raterus concerning the usage of date fields.
I'm also using MySQL and there the date format is independent from the culture and easy to deal with.
HStrix
I came to the conclusion solving my problem I need to create a new field.
In addition, I agree with your comment raterus concerning the usage of date fields.
I'm also using MySQL and there the date format is independent from the culture and easy to deal with.
HStrix
use this
strSQL = "select * from table where (" & MyFunction(field) & " = '" & strCompare & "'")
Regards,
B..M