David Emerson
davide at dalyn.co.nz
Tue Mar 4 18:17:28 CST 2003
Thanks all for the feedback. I am still digesting it. One point of
clarification - My solution using ADO: I presume that this needs to pull
all the records from the server to the local machine before filtering it as
opposed to the sproc/UDF method which does all the filtering on the
server. Is this correct?
David
At 4/03/2003, you wrote:
>Since we're into variety (i.e. the plural of spouse is... spice), you could
>also create a scalar UDF that returns said value.
>
>-----Original Message-----
>From: dba-sqlserver-admin at databaseadvisors.com
>[mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Francisco H
>Tapia
>Sent: March 4, 2003 2:47 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer]Replacement for DLookup with 2 parameters
>
>This is true, though I've never time-ed it to find out my performance
>gains... The great thing is that there is more than one way to skin a
>'sproc' ;o)
>
>-Francisco
>http://rcm.netfirms.com
>----- Original Message -----
>From: "Mike and Doris Manning" <mikedorism at ntelos.net>
>To: <dba-sqlserver at databaseadvisors.com>
>Sent: Tuesday, March 04, 2003 11:30 AM
>Subject: RE: [dba-SQLServer]Replacement for DLookup with 2 parameters
>
>
>: Right. I've found it faster to use Output Parameters with a sproc instead
>: of having to open a recordset.
>:
>: Doris Manning
>: Database Administrator
>: Hargrove Inc.
>: www.hargroveinc.com
>:
>:
>: -----Original Message-----
>: From: dba-sqlserver-admin at databaseadvisors.com
>: [mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Francisco H
>: Tapia
>: Sent: Tuesday, March 04, 2003 12:52 PM
>: To: dba-sqlserver at databaseadvisors.com
>: Subject: Re: [dba-SQLServer]Replacement for DLookup with 2 parameters
>:
>:
>: So maybe something like this?
>:
>: Dim oCmd As ADODB.Command
>: Dim lRecordsAffected As Long
>:
>: ' Create and Open a new Connection
>: Set oConn = New ADODB.Connection
>: Set oConn = CurrentProject.Connection
>: ' Create a new Command
>: Set oCmd = New ADODB.Command
>: With oCmd
>: .CommandType = adCmdStoredProc
>: .CommandText = "stp_AmountDue "
>: Set .ActiveConnection = oConn
>:
>: ' Create the Command's Parameters and set their values
>: .Parameters.Append .CreateParameter("RETURN_VALUE", _
>: adInteger, adParamReturnValue, 0)
>: .Parameters.Append .CreateParameter("AmountDue", _
>: adCurrency, adParameterOutput, , NULL)
>: .Parameters.Append .CreateParameter("CustIdNo", _
>: adInteger, adParamInput, , CustIdNo)
>: .Parameters.Append .CreateParameter("InvNumber", _
>: adInteger, adParamInput, , InvNumber -1)
>: ' Run the command
>: .Execute lRecordsAffected, , adExecuteNoRecords
>: ' Do not return recordset
>:
>: ' Print new identity value
>: Debug.Print .Parameters("AmountDue").Value ' New Identity End With
>:
>: CREATE Procedure stp_AmountDue (@AmountDue as Currency, @CustIDno as int,
>: @InvNumber as Int) AS
>: SELECT @AmountDue = CurrentMth FROM tblCustStatement WHERE CustNoId =
>: @CustNoId AND
>: InvNumber = @InvNumber
>: ---------END
>:
>:
>: -Francisco
>: http://rcm.netfirms.com
>: ----- Original Message -----
>: From: "Mike and Doris Manning" <mikedorism at ntelos.net>
>: To: <dba-sqlserver at databaseadvisors.com>
>: Sent: Tuesday, March 04, 2003 5:16 AM
>: Subject: RE: [dba-SQLServer]Replacement for DLookup with 2 parameters
>:
>:
>: : Actually he doesn't need to use a recordset at all. He could create a
>: sproc
>: : that returns "CurrentMth" as an Output Parameter. If there is no
>matching
>: : record, then the return value is NULL and he can use NZ function to
>: convert
>: : it to 0.
>: :
>: : Doris Manning
>: : Database Administrator
>: : Hargrove Inc.
>: : www.hargroveinc.com
>: :
>: :
>: : -----Original Message-----
>: : From: dba-sqlserver-admin at databaseadvisors.com
>: : [mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Francisco
>: : Tapia
>: : Sent: Tuesday, March 04, 2003 02:41 AM
>: : To: dba-sqlserver at databaseadvisors.com
>: : Subject: Re: [dba-SQLServer]Replacement for DLookup with 2 parameters
>: :
>: :
>: : Think sprocs, David....
>: : So your Function would be more along the lines of..
>: :
>: : Public Function cbfAmtDue() as Currency
>: : Dim rst ADODB.Recordset
>: : Set rst = New ADODB.Recordset
>: :
>: : Currentproject.Connection.stp_AmountDue CustIDNo, InvNumber - 1, rst
>: :
>: : if rst.eof = true then
>: : cbfAmtDue = Null 'Or 0
>: : else
>: : cbfAmtDue = rst!CurrentMth
>: : end if
>: : '--------END
>: : Now in a sproc on your SQL Server you'd add the following sproc...
>: :
>: : ----------
>: : Create Procedure stp_AmountDue (@CustIDno as int, @InvNumber as Int) AS
>: : SELECT CurrentMth From tblCustStatement Where CustNoId = @CustNoId AND
>: : InvNumber = @InvNumber ---------END
>: :
>: : this is all OTOMH, so please check your syntax...
>: : -Francisco
>: : http://rcm.netfirms.com/
>: : ----- Original Message -----
>: : From: "David Emerson" <davide at dalyn.co.nz>
>: : To: <dba-SQLServer at databaseadvisors.com>
>: : Sent: Monday, March 03, 2003 8:14 PM
>: : Subject: [dba-SQLServer]Replacement for DLookup with 2 parameters
>: :
>: :
>: : : What I am looking for here is some confirmation I am doing the right
>: : thing.
>: : :
>: : : In A97 I had a simple statement to return a single value if the
>: : : corresponding record existed (this was in the Control Source of a
>field
>: -
>: : :
>: : : =IIf(Not
>: : : IsNull([CustIDNo]),nz(DLookUp("Current","tblCustStatement","CustIDNo =
>"
>: &
>: : : [CustIDNo] & " and StatementNumber = " & [InvNumber]-1),0))
>: : :
>: : : Now in my ADP I am using the following (and setting the field control
>: : : source to '=cbfAmtDue()') -
>: : :
>: : : Public Function cbfAmtDue() As Currency
>: : :
>: : : Dim cnn As ADODB.Connection, rst As ADODB.Recordset
>: : :
>: : : Set cnn = CurrentProject.Connection
>: : : Set rst = New ADODB.Recordset
>: : : rst.Open "tblCustStatement", cnn, adOpenDynamic, adLockOptimistic
>: : :
>: : : cbfAmtDue = 0
>: : : If Not IsNull([CustIDNo]) Then
>: : : rst.Filter = "CustIDNo = " & Me!CustIDNo & " and
>: StatementNumber
>: : =
>: : : " & DLookup("InvNumber", "tblCustomers", "CustomerID = " &
>: Me!txtCustID) -
>: : 1
>: : : If rst.EOF <> True Then 'Statement record exists
>: : : cbfAmtDue = rst!CurrentMth
>: : : End If
>: : : End If
>: : :
>: : : rst.Close
>: : : Set rst = Nothing
>: : : cnn.Close
>: : : Set cnn = Nothing
>: : :