Re: call a function using ado

chieko (chieko@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
> Thanks for reading my question.
> the function converts integer utc time to eastern standard time.
> Function FtToUtc(FT_time)
>
> '(@FtValue bigint)
> 'returns DateTime
>
> Dim SecondsToAdd As Double
> Dim MilliSecToAdd As Double
> Dim GMT As Date
> Dim newDate As Date
> Dim num As Double
> GMT = #1/1/1970#
>
> 'declare @RetVal datetime
>
> 'set @RetVal = NULL
> If (FT_time <> 0) Then
> SecondsToAdd = (FT_time - 1.16444736E+17) / 10000000
> MilliSecToAdd = ((FT_time - 1.16444736E+17) / 10000 - (SecondsToAdd *
> 1000)) / 1000
>
> If (SecondsToAdd >= -2147483648#) And (SecondsToAdd <= 2147483647) And _
> (MilliSecToAdd >= -2147483648#) And (MilliSecToAdd <= 2147483647) Then
> newDate = DateAdd("s", SecondsToAdd, GMT)
> FtToUtc = DateAdd("s", MilliSecToAdd, DateAdd("s", SecondsToAdd, GMT))
> End If
> Else: FtToUtc = 0
> End If
> End Function
>
> and in visual basic i am using adodb to connect to the sql server then
> tried to issue the command:
> recordset_rst = fttoutc(cell number)
> I can provide the vb code when i have it available...later today maybe.

Judging from your previous post, this function of yours lives in SQL
Server, although the syntax indicates different.

But if the function indeed lives in SQL Server, and you issue

recordset_rst = fttoutc(cell number)

from Excel, then Excel is not going to look for the function in SQL
Server.

Rather you would need to do something like:

cmd.CommandType = adStoredProcedure
cmd.CommandText = "dbo.FtToUtc"
cmd.CreateParameter "@retval", adDBTimeStamp, adParamReturnValue
cmd.CreateParameter "@input", adDBTimeStamp, adParamInputValue, , _
cellvalue
cmd.Execute ,adReturnNoRecords
returnvalue = cmd.Parmeters("@retval")

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages
Loading