• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

AccessDataSource SQL With User Defined Function

I come from a MS Access background and am trying to develope a ASP.NET app using VB.  I have a mdb which I have connected to with the accessdatasource control.

How do I use my own function in an expression in the SQL select statement to add a calculated field to the query?

Basically I have a table that has latitudes and longitudes and a function which determines distance between two sets of coordinates.  I want to display all the records that are within x number of miles from a coordinate set but when I try to call my function from the accessdatasource it says its not defined.  What is the best way to do this?
0
jet46
Asked:
jet46
1 Solution
 
BirkyCommented:
Hi,

Is your function simple? If it is you could create a function in your SQL database then you could make your sql statement call it directly (See below).

Failing that Create 2 ADODB Recordsets. Load one with the conents from the Database. The second Recordset is created manually and will mimic it but with the addition of a field for the functions returned value. Sorry it wording might be a bit unclear but the example should work.

Might not be the best method but it is working for us.

Hope this helps
-- SQL

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
	-- Add the parameters for the function here
	<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
	-- Declare the return variable here
	DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

	-- Add the T-SQL statements to compute the return value here
	SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

	-- Return the result of the function
	RETURN <@ResultVar, sysname, @Result>

END
GO

-- To Call it would just be....

SELECT a , MYFUNCTION(b)
FROM ...


-- RecordSets Method
Dim RS1 As ADODB.Recordset
Dim RS2 As ADODB.Recordset

Set RS1 = New ADODB.Recordset
Set RS2 = New ADODB.Recordset

RS1.Open strSQL, Conx, adOpenKeyset, adLockOptimistic, adCmdText
RS1.MoveLast
RS1.MoveFirst

RS2.Fields.Append "Field1", adVarChar, 50
RS2.Fields.Append "Field2", adVarChar, 50
RS2.Fields.Append "NEWFIELD", adVarChar, 50
RS2.Open

Do While Not RS1.EOF
    RS2.AddNew
    RS2!Field1 = RS1!Field1
    RS2!Field2 = RS1!Field2
    RS2!NEWFIELD = MyFunction(RS1!Field2)
    RS2.Update
    RS2.MoveNext
Loop
RS2.MoveLast
RS2.MoveFirst

Open in new window

0
 
jet46Author Commented:
Thanks for your help.  I think I am going to give the ADO method a try.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now