How to call UDF from VB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to call UDF from VB

Author

Topic

LePrezAtWork
Starting Member

16 Posts

Posted - 2004-04-22 : 15:55:09

Hi!

I need help! I need to call a UDF that return TABLE value from VB6. How can I do that? There is the UDF


CREATE FUNCTION dbo.TestFunc(@CustNo varchar(15))
RETURNS TABLE
AS
RETURN(SELECT * FROM Customers WHERE CustNo = @CustNo)

Thank's

LePrezAtWork
Starting Member

16 Posts

Posted - 2004-04-22 : 17:10:29

Ok... no one look to have any answer and my search do nothing but I have discovered that I can receive TABLE value using sproc. Since I do not want to allow direct acces to the tables in the database I think that what I have found is a better way. There is my sample :


CREATE PROCEDURE dbo.TestProc
AS
SELECT * FROM Customers WHERE CustNo = '12345678'

and the VB code :


  myDBCnn = GetDBConnection("ID1")
  myDBCnn.Open
  If myDBCnn.State = adStateOpen Then
    cmdGetLang.ActiveConnection = myDBCnn
    cmdGetLang.CommandText = "dbo.TestProc"
    cmdGetLang.CommandType = adCmdStoredProc
    cmdGetLang.CommandTimeout = 0
    Set rstGetLang = cmdGetLang.Execute
    MsgBox rstGetLang!CustNo
    Set rstGetLang = Nothing
    myDBCnn.Close
  End If

So I suppose that if my sproc uses parameters it will also work.
Do you think that it's the better way to do that??

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources