Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP General (http://www.velocityreviews.com/forums/f65-asp-general.html)
-   -   passing a parameter to SQL MIN() function (http://www.velocityreviews.com/forums/t792423-passing-a-parameter-to-sql-min-function.html)

01-04-2004 01:13 PM

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 Bertrand [MVP] 01-04-2004 03:06 PM

Re: passing a parameter to SQL MIN() function
 
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 Barrows 01-04-2004 03:13 PM

Re: passing a parameter to SQL MIN() function
 
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 01-04-2004 06:07 PM

Re: passing a parameter to SQL MIN() function
 
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"



01-04-2004 06:39 PM

Re: passing a parameter to SQL MIN() function
 
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
> >
> >

>
>




01-04-2004 06:40 PM

Re: passing a parameter to SQL MIN() function
 
"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"
>
>




01-04-2004 06:46 PM

Re: passing a parameter to SQL MIN() function
 
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=ship log;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").Val ue =
> 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




01-04-2004 06:48 PM

Re: passing a parameter to SQL MIN() function
 
>> 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 Barrows 01-04-2004 06:56 PM

Re: passing a parameter to SQL MIN() function
 
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 Barrows 01-04-2004 07:02 PM

Re: passing a parameter to SQL MIN() function
 
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




All times are GMT. The time now is 11:43 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.