passing a parameter to SQL MIN() function

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
Sun Jan 04 09:06:16 CST 2004

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:erScwTs0DHA.3468@TK2MSFTNGP11.phx.gbl...
> 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
>
>

Bob
Sun Jan 04 09:13:57 CST 2004

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"

headdead03
Sun Jan 04 11:49:02 CST 2004

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

Bob
Sun Jan 04 12:07:36 CST 2004

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"

aa>
Sun Jan 04 12:39:58 CST 2004

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]" <aaron@TRASHaspfaq.com> wrote in message
news:eA8BPTt0DHA.1724@TK2MSFTNGP10.phx.gbl...
> 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:erScwTs0DHA.3468@TK2MSFTNGP11.phx.gbl...
> > 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
> >
> >
>
>

aa>
Sun Jan 04 12:40:44 CST 2004

"What database and version?"
Access2000



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ezBKfVt0DHA.3216@TK2MSFTNGP11.phx.gbl...
> 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"
>
>

aa>
Sun Jan 04 12:46:36 CST 2004

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" <headdead03@aol.com> wrote in message
news:20040104124902.11386.00002277@mb-m11.aol.com...
> 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

aa>
Sun Jan 04 12:48:25 CST 2004

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

So?



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23yXAh2u0DHA.2528@TK2MSFTNGP09.phx.gbl...
> 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
Sun Jan 04 12:56:46 CST 2004

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$ZjaKv0DHA.716@TK2MSFTNGP12.phx.gbl...
> "What database and version?"
> Access2000
>
>
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:ezBKfVt0DHA.3216@TK2MSFTNGP11.phx.gbl...
> > 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
Sun Jan 04 13:02:35 CST 2004

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

aa>
Sun Jan 04 13:38:19 CST 2004

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" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eJcR$Rv0DHA.4032@tk2msftngp13.phx.gbl...
> 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$ZjaKv0DHA.716@TK2MSFTNGP12.phx.gbl...
> > "What database and version?"
> > Access2000
> >
> >
> >
> > "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:ezBKfVt0DHA.3216@TK2MSFTNGP11.phx.gbl...
> > > 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
Mon Jan 05 06:48:13 CST 2004

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"

aa>
Mon Jan 05 08:29:33 CST 2004

Thanks, Bob,
This is exactly what the doctor ordered