user defined function in sql from vb.net

[mmy]

I have an access database (mco.mdb) with a table tblA. Using a windows form (from visual studio 2005) and a datagridview I want to display the contents of tblA.

I use the following code in the load event of the form as test.

Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mco.mdb"
Dim objConnection As OleDbConnection = New OleDbConnection(strConnection)
Dim objDataAdapter As OleDbDataAdapter
Dim objDataset As DataSet
dim sql as String = "select ID, description from tblA"
objConnection.Open()
objDataadapter = New OleDbDataAdapter(sql, objConnection)
objDataset = New DataSet("MCO")
objDataAdapter.Fill(objDataset, "A")
objConnection.Close()
DataGridView1.DataSource = objDataset.Tables("A")

This works without a problem. Now I want to write a user defined function in the sql statement. For example:
sql = "select ID, description, fTest() as new_column_name from tblA"

When I run the form I receive the error "Undefined function fTest in expression". This is normal because I haven't written this function yet.

But how do I have to write this function? If I use following code (like a normal function in vb.net) I still receive the error. If I use the debugger, I see this function never get accessed.

Public Function fTest() as string
   return "abc"
end function

So, next to the columns ID and Description I want to see the column "new_column_name" with "abc" in it for every value. This is just for testing purpose. Eventually this column has to receive a calculation depending from some other criteria...

Does anybody know how to solve this problem? I'm already stuck on it for several days... : (

 

[jmcilhinney]

Your SQL code is executed on the database. The database doesn't know anything about your VB code. You cannot access anything in your VB application from the SQL code. If you want the SQL code to execute a custom function then you have to define that function in the database itself. I'm not sure whether that's possible in Access or not as I've never tried. I know it's possible in SQL Server and other grown-up databases.

 

[mmy]

In Access I could write a module containing the the function and vba-code.

but I understand the sql line is executed on the database, and this one should know how to execute the function.

I needed this to fill 2 columns containing extra information. But I bypassed this by making a formula in crystal report making the calculation.

Thanks anyway for the reply...

 

[cjard]

I'm not 100% sure that a Function ina Module in an access database, can be run by your statement.

I.e. you write the function MyFunc

You then attempt:

SELECT MyFunc(column1) FROM table



I have a feeling that this wont work, because you cannot use uder defined functions in an access SQL statement


Use a better database, like SQLServer

 

[mmy]

cjard,

in Access I got it working. It's quite simple. You can try it using when designing a query.

make one field as: ColumnName: FuctionName([access field x])

in a module create this function:

function FunctionName(variable x) as string (or the datatype you want to return)
...
...
return "abc"
end function

if you execute the query, in ColumnName "abc" will be showed for every field.

I was just wondering if it was possible with VB.NET in combination with an Access table

 

[cjard]

As noted I think actually that though you can use a UDF from access, you cannot use a UDF from VB.N.. i.e. that SQL:

SELECT MyFunc(col) FROM table

will work fine in access, but as soon as you put it in an oledbcommand:

New OleDbCommand("SELECT MyFunc(col) FROM table")

It will say "function not recognised" or soemthing.. Its the way it is.. THe function is built into the Access front end, not the Jet back end.. WHen you use an Access db from .NET youre not actually using Access, youre using jet

Upgrade to a better db like SQLSX or ORACLE10gExpress