Re: call a function using ado
- From: chieko <chieko@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 30 Jan 2006 05:27:05 -0800
Yes, you're correct. i was in a hurry so i copied the function from the excel
vba.
I'll try your suggestion.
Thank you,
Chieko
the excel adodb connection:
Function NewFtToUtc(FT_time)
' Make a connection to the sttr sql server
' open the connection to the portal database.
' Run the stored procedure using the cell number as input.
'***************8
'Go to error line if error occurs
On Error GoTo cmdExecuteLinkedErr
' declare a few variables
Dim oConn As ADODB.Connection
Dim strConn As String
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim cmdCommand As ADODB.Command
' set the query string
strSQL = "select [dbo].[FtToUtc](FT_time)"
Set oConn = New ADODB.Connection
strConn = "provider = sqloledb; Server = kuroda-port; database = pubs;
integrated security = sspi"
oConn.Open strConn
Debug.Print "The connection string is: " & oConn.ConnectionString
'Build the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = oConn
With cmdCommand
..CommandText = "sp_ftToUtc"
..CommandType = adCmdStoredProc 'adCmdText execute stored procedure.
rs = oConn.Execute("sp_ftToUtc", , 4)
End With
'Open the recordset.
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = oConn
' execute the query
rs.Open cmdCommand
'print something out.
Debug.Print rs.RecordCount
Debug.Print rs.Fields!cur_date
'set the function to equal the first record.
Set NewFtToUtc = rs.Fields!cur_date
Debug.Print NewFtToUtc & " is the new date."
rs.Close
oConn.Close
' ************
' CHECK for native jet query errors.
cmdExecuteLinkedExit:
DoCmd.Hourglass False
Exit Function
cmdExecuteLinkedErr:
'If Err.Number = dbengine.Errors(0).Number And _
'dbengine.Errors.Count > 1 Then
'For Each errAny In dbengine.Errors
' MsgBox "Error " & errAny.Number & " raised by " _
' & anyErr.Source & ": " & errAny.Description, _
' vbCritical, "cmdExecuteAttached()"
'Next errAny
'Else
MsgBox "Error " & Err.Number & " raised by " _
& Err.Source & ": " & Err.Description, _
vbCritical, "cmdExecuterAttached()"
'End If
Resume cmdExecuteLinkedExit
End Function
the fttoutc code.
returns datetime
as
begin
declare @SecondsToAdd bigint, @MilliSecToAdd bigint
declare @RetVal datetime
set @RetVal = NULL
set @SecondsToAdd = (@FtValue - 116444736000000000)/10000000
set @MilliSecToAdd = (@FtValue - 116444736000000000)/10000 -
(@SecondsToAdd * 1000)
if (@SecondsToAdd >= -2147483648) and (@SecondsToAdd <= 2147483647) and
(@MilliSecToAdd >= -2147483648) and (@MilliSecToAdd <= 2147483647)
set @RetVal = dateadd(ms, @MilliSecToAdd, dateadd(ss, @SecondsToAdd,
cast('1970-01-01 00:00:00.000' as datetime)))
return @RetVal
end
"Erland Sommarskog" wrote:
> 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
>
.
- Follow-Ups:
- Re: call a function using ado
- From: Erland Sommarskog
- Re: call a function using ado
- References:
- Re: call a function using ado
- From: Mary Chipman [MSFT]
- Re: call a function using ado
- From: Erland Sommarskog
- Re: call a function using ado
- Prev by Date: Re: foxpro view
- Next by Date: Re: call a function using ado
- Previous by thread: Re: call a function using ado
- Next by thread: Re: call a function using ado
- Index(es):
Relevant Pages
|
Loading
