Solved

Asp.Net: private function in SQL SELECT statement

Posted on 2004-09-23
20
1,176 Views
Last Modified: 2010-05-18
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
 
0
Comment
Question by:HStrix
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +3
20 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 12134664
Hi,

use this
strSQL =  "select * from table  where (" & MyFunction(field) & " = '" & strCompare & "'")


Regards,
B..M
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12134665
I don't see where your returning a value from your function?
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12134674
also just run the function directly before the SQL statement and then assign an intermediary variable the value the function returns.

Regards,

Aeros
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 3

Expert Comment

by:mAjKoL
ID: 12134678
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 & "'")
0
 
LVL 33

Expert Comment

by:raterus
ID: 12134832
Could you use a UDF in sql server?  (User Defined Function), or do you have to have this function in VB.
0
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12134918
in SQL Server yes - but this is MS Access
0
 

Author Comment

by:HStrix
ID: 12134919
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.
0
 
LVL 33

Expert Comment

by:raterus
ID: 12134954
You can do that too in Access, you just have to write the function in an Access Module.
0
 

Author Comment

by:HStrix
ID: 12134991
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.
0
 

Author Comment

by:HStrix
ID: 12135000
The function "MyFunction" also is part of the Asp.Net (VB.Net) application.
0
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12135015
So you have to write it in an Access Module as raterus says. Than you can use it in SELECT statement
0
 
LVL 33

Expert Comment

by:raterus
ID: 12135055
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.
0
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12135062
0
 
LVL 33

Accepted Solution

by:
raterus earned 500 total points
ID: 12135076
Are you sure you have to do it like THIS though? in the many many years I've been writing DB queries, I've never had to run a function against a returned field like that, I would imagine there are much easier ways of doing this.  If anything make a new field in your table that contains the results of that function.
0
 

Author Comment

by:HStrix
ID: 12135140
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.
0
 
LVL 33

Expert Comment

by:raterus
ID: 12135233
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!
0
 
LVL 1

Expert Comment

by:blueforce
ID: 12135261
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.
0
 

Author Comment

by:HStrix
ID: 12135339
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.

0
 
LVL 33

Expert Comment

by:raterus
ID: 12136042
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.
0
 

Author Comment

by:HStrix
ID: 12140213
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question