Arthur Fuller
artful at rogers.com
Tue Mar 4 17:25:58 CST 2003
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
: : :
: : : It seems a long way about it but is there any simpler way?
: : :
: : :
: : : Regards
: : :
: : : David Emerson
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com