passing a parameter to SQL MIN() function

Discussion in 'ASP General' started by Guest, Jan 4, 2004.

  1. Guest

    Guest Guest

    From ASP I run a query using MIN(some_field/parameter)

    Now I need to pass this parameter to the query from ASP code

    How do I do that?
    Syntax like
    MIN([some_field] / [])
    does not work
     
    Guest, Jan 4, 2004
    #1
    1. Advertising

  2. Did you mean

    MIN(some_column) / parameter

    ?

    Since the parameter becomes constant for all rows in the query, this should
    yield the same answer.

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    <aa> wrote in message news:...
    > From ASP I run a query using MIN(some_field/parameter)
    >
    > Now I need to pass this parameter to the query from ASP code
    >
    > How do I do that?
    > Syntax like
    > MIN([some_field] / [])
    > does not work
    >
    >
     
    Aaron Bertrand [MVP], Jan 4, 2004
    #2
    1. Advertising

  3. Guest

    Bob Barrows Guest

    aa wrote:
    > From ASP I run a query using MIN(some_field/parameter)
    >
    > Now I need to pass this parameter to the query from ASP code
    >
    > How do I do that?
    > Syntax like
    > MIN([some_field] / [])
    > does not work


    What database and version?

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jan 4, 2004
    #3
  4. Guest

    Bob Barrows Guest

    Headdead03 wrote:
    > Dim DS As DataSet
    > Dim MyConnection As SqlConnection


    He asked for ASP code, not ASP.Net.

    >> Now I need to pass this parameter to the query from ASP code


    This IS a classic ASP group after all. :)

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jan 4, 2004
    #4
  5. Guest

    Guest Guest

    That might be a solution, Aaron.

    Building a SELECT SQL statement and inserting there the parameter like
    SQLstring = SQLstring & "MIN(some_column) / " & parameter
    or
    SQLstring = SQLstring & "MIN(some_column / " & parameter & ")"
    should not present problems.

    Yet if I wanted to have this parameterized query stored in Access2000 -
    what would be the syntax?
    MIN(some_column) /[] failed when atemting to run it in Access






    "Aaron Bertrand [MVP]" <> wrote in message
    news:...
    > Did you mean
    >
    > MIN(some_column) / parameter
    >
    > ?
    >
    > Since the parameter becomes constant for all rows in the query, this

    should
    > yield the same answer.
    >
    > --
    > Aaron Bertrand
    > SQL Server MVP
    > http://www.aspfaq.com/
    >
    >
    >
    >
    > <aa> wrote in message news:...
    > > From ASP I run a query using MIN(some_field/parameter)
    > >
    > > Now I need to pass this parameter to the query from ASP code
    > >
    > > How do I do that?
    > > Syntax like
    > > MIN([some_field] / [])
    > > does not work
    > >
    > >

    >
    >
     
    Guest, Jan 4, 2004
    #5
  6. Guest

    Guest Guest

    "What database and version?"
    Access2000



    "Bob Barrows" <> wrote in message
    news:...
    > aa wrote:
    > > From ASP I run a query using MIN(some_field/parameter)
    > >
    > > Now I need to pass this parameter to the query from ASP code
    > >
    > > How do I do that?
    > > Syntax like
    > > MIN([some_field] / [])
    > > does not work

    >
    > What database and version?
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
     
    Guest, Jan 4, 2004
    #6
  7. Guest

    Guest Guest

    Thanks, but Dim DS As DataSet faild on VBScript/ASP and the rest of the
    syntax look an bit infamiliar to me.
    I am on ASP3


    "Headdead03" <> wrote in message
    news:...
    > Dim DS As DataSet
    > Dim MyConnection As SqlConnection
    > Dim MyCommand As SqlDataAdapter
    > MyConnection = New SqlConnection(
    > "server=localhost;uid=webuser;pwd=secret;database=ShipLog" )
    > 'MyConnection = New
    >

    SqlConnection("server=(local)\NetSDK;database=shiplog;Trusted_Connection=yes
    ")
    > MyCommand = New SqlDataAdapter("GetCustName", MyConnection)
    >
    > MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
    >
    > 'MyCommand.SelectCommand.Parameters.Add(New
    > SqlParameter("@CategoryName", SqlDbType.NVarChar, 15))
    > 'MyCommand.SelectCommand.Parameters("@CategoryName").Value =
    > SelectCategory.Value
    >
    > 'MyCommand.SelectCommand.Parameters.Add(New

    SqlParameter("@OrdYear",
    > SqlDbType.NVarChar, 4))
    > ' MyCommand.SelectCommand.Parameters("@OrdYear").Value =
    > SelectYear.Value
    >
    > DS = new DataSet()
    > MyCommand.Fill(DS, "Customer")
    > 'Dim anyRow as DataRow =.NewRow
    > 'anyRow("CUST_NAME") = "Jay"
    > 'DS.Tables("Customer").Add(anyRow)
    >
    > Dim workRow As DataRow = DS.Tables
     
    Guest, Jan 4, 2004
    #7
  8. Guest

    Guest Guest

    >> Now I need to pass this parameter to the query from ASP code
    >This IS a classic ASP group after all. :)


    So?



    "Bob Barrows" <> wrote in message
    news:%...
    > Headdead03 wrote:
    > > Dim DS As DataSet
    > > Dim MyConnection As SqlConnection

    >
    > He asked for ASP code, not ASP.Net.
    >
    > >> Now I need to pass this parameter to the query from ASP code

    >
    > This IS a classic ASP group after all. :)
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
     
    Guest, Jan 4, 2004
    #8
  9. Guest

    Bob Barrows Guest

    The SQL syntax will be:

    Select MIN([some_field])/[qParm] ...

    I used the word "qParm" just to guarantee that it would not duplicate any
    column name in any of the tables in your FROM clause. You can use any
    parameter name you want, keeping in mind the need to avoid duplicating any
    column names or builtin VBA function names.

    Save the query as qSelectMin, then in ASP, do this:

    'open a connection using cn as the connection variable, then
    dim rs, somevalue
    somevalue = 20
    set rs=server.createobject("adodb.recordset")
    cn.qSelectMin somevalue, rs

    HTH,
    Bob Barrows

    <aa> wrote in message news:e$...
    > "What database and version?"
    > Access2000
    >
    >
    >
    > "Bob Barrows" <> wrote in message
    > news:...
    > > aa wrote:
    > > > From ASP I run a query using MIN(some_field/parameter)
    > > >
    > > > Now I need to pass this parameter to the query from ASP code
    > > >
    > > > How do I do that?
    > > > Syntax like
    > > > MIN([some_field] / [])
    > > > does not work

    > >
    > > What database and version?
    > >
    > > --
    > > Microsoft MVP - ASP/ASP.NET
    > > Please reply to the newsgroup. This email account is my spam trap so I
    > > don't check it very often. If you must reply off-line, then remove the
    > > "NO SPAM"
    > >
    > >

    >
    >
     
    Bob Barrows, Jan 4, 2004
    #9
  10. Guest

    Bob Barrows Guest

    aa wrote:
    >>> Now I need to pass this parameter to the query from ASP code

    > >This IS a classic ASP group after all. :)

    >
    > So?
    >


    HuH? Did you think this statement was directed to you? I was replying to
    Headdead03. I included the snippet from your question to show him that you
    were asking for ASP code vs ASP.Net.

    But, to answer the question I think you're asking:

    So, somebody who is ignorant of the difference between ASP and ASP.Net might
    try to use this code in an ASP application and decide not to come back here
    any more to receive "incorrect" answers.

    There are very good reasons to keep these newsgroups focussed on their
    topics. The topic for this newsgroup is classic ASP. There is a hierarchy of
    groups with "dotnet" in their names that are devoted to .Net topics.

    Bob Barrows
     
    Bob Barrows, Jan 4, 2004
    #10
  11. Guest

    Guest Guest

    Thanks, Bob, the SQL bit is exactly what I need and it works fine when
    tested on a stored query in Access

    Regarding ASP code - for calling stored parameterized queries I used to rely
    on

    CommandObject.CommandText = stored_query_name'parameter_value'

    Can I pass two parameters this way?
    For some reason
    CommandObject.CommandText = stored_query_name
    'parameter_value''another_parameter_value'
    does not work


    "Bob Barrows" <> wrote in message
    news:eJcR$...
    > The SQL syntax will be:
    >
    > Select MIN([some_field])/[qParm] ...
    >
    > I used the word "qParm" just to guarantee that it would not duplicate any
    > column name in any of the tables in your FROM clause. You can use any
    > parameter name you want, keeping in mind the need to avoid duplicating any
    > column names or builtin VBA function names.
    >
    > Save the query as qSelectMin, then in ASP, do this:
    >
    > 'open a connection using cn as the connection variable, then
    > dim rs, somevalue
    > somevalue = 20
    > set rs=server.createobject("adodb.recordset")
    > cn.qSelectMin somevalue, rs
    >
    > HTH,
    > Bob Barrows
    >
    > <aa> wrote in message news:e$...
    > > "What database and version?"
    > > Access2000
    > >
    > >
    > >
    > > "Bob Barrows" <> wrote in message
    > > news:...
    > > > aa wrote:
    > > > > From ASP I run a query using MIN(some_field/parameter)
    > > > >
    > > > > Now I need to pass this parameter to the query from ASP code
    > > > >
    > > > > How do I do that?
    > > > > Syntax like
    > > > > MIN([some_field] / [])
    > > > > does not work
    > > >
    > > > What database and version?
    > > >
    > > > --
    > > > Microsoft MVP - ASP/ASP.NET
    > > > Please reply to the newsgroup. This email account is my spam trap so I
    > > > don't check it very often. If you must reply off-line, then remove the
    > > > "NO SPAM"
    > > >
    > > >

    > >
    > >

    >
    >
     
    Guest, Jan 4, 2004
    #11
  12. Guest

    Bob Barrows Guest

    aa wrote:
    > Thanks, Bob, the SQL bit is exactly what I need and it works fine when
    > tested on a stored query in Access
    >
    > Regarding ASP code - for calling stored parameterized queries I used
    > to rely on
    >
    > CommandObject.CommandText = stored_query_name'parameter_value'


    This works?!? I've never seen this syntax. Is this what you mean:
    CommandObject.CommandText = "stored_query_name 'parameter_value'"


    You do not need an explicit command object with Access databases. The only
    reason to use an explicit command object is if you are executing a stored
    procedure that returns values either by output parameters or via the RETURN
    keyword, neither of which is available with Access saved queries.

    >
    > Can I pass two parameters this way?


    Yes

    > For some reason
    > CommandObject.CommandText = stored_query_name
    > 'parameter_value''another_parameter_value'
    > does not work


    You need to separate the parameter values with commas.

    CommandObject.CommandText = "stored_query_name 'parameter_value'" & _
    ",'another_parameter_value'"

    You only need to use the single quotes around the parameter values if they
    are string parameters.

    Again, a command object is not needed. Just use the Connection object:

    If the query does not return records:
    cn.stored_query_name 'parameter_value', 'another_parameter_value'

    If the query does return records:
    Set rs=server.createobject("adodb.recordset")
    cn.stored_query_name 'parameter_value', 'another_parameter_value', rs

    This will be more efficient that using the CommandText to concatenate a
    dynamic sql call to the saved query.

    HTH,
    Bob Barrows


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jan 5, 2004
    #12
  13. Guest

    Guest Guest

    thanks

    Thanks, Bob,
    This is exactly what the doctor ordered
     
    Guest, Jan 5, 2004
    #13
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lois
    Replies:
    1
    Views:
    3,303
    Ryan Stewart
    Dec 27, 2004
  2. juergen
    Replies:
    3
    Views:
    575
    opalinski from opalpaweb
    Sep 20, 2006
  3. Homer J. Simpson
    Replies:
    0
    Views:
    1,888
    Homer J. Simpson
    Sep 4, 2007
  4. Albert Hopkins

    When is min(a, b) != min(b, a)?

    Albert Hopkins, Jan 21, 2008, in forum: Python
    Replies:
    31
    Views:
    844
    Albert van der Horst
    Feb 4, 2008
  5. carmen

    Converting hrs and min to just min

    carmen, Aug 10, 2004, in forum: ASP General
    Replies:
    4
    Views:
    137
    carmen
    Aug 10, 2004
Loading...

Share This Page