[dba-SQLServer]Replacement for DLookup with 2 parameters

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
>: : :




More information about the dba-SQLServer mailing list