[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