Hello,
I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
users have ability to construct their own select criteria (for a where
clause). I already developed a user control where user can select field name,
operator (equals, like, begins with, etc) and value of the search criteria.
This control can already build where clause part. Now I am at the point,
where I need to execute SQL query, but I would really like to be able to use
stored procedures.
As far as I know, I have 2 options: (1) Pass in “where clause” string to SP
(2) Execute SQL Query inside ASP.NET
Are there any other options? If anyone ever had to implement something
similar, I want to hear your comments. Thanks 5 4196
Hi,
"WebMatrix" <We*******@disc ussions.microso ft.com> wrote in message
news:02******** *************** ***********@mic rosoft.com... Hello,
I am developing ASP.NET 1.1 application against SQL Server 2000 db, where users have ability to construct their own select criteria (for a where clause). I already developed a user control where user can select field name, operator (equals, like, begins with, etc) and value of the search criteria. This control can already build where clause part. Now I am at the point, where I need to execute SQL query, but I would really like to be able to use stored procedures.
You cannot use a SP for this, the query will change all the time therefore
the engine cannot precalculate the execution path of it. you will have to
submit it as CommandType = Text
One warning I give you is taht you have to be careful with SQL injection,
the query can be injected both in the select as well as in the where clauses
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
Sure you can. stored procedures can dynamically execute sql via a number of
functions, include exec and sp_execute.You do lose out on some of the sproc
benefits though (such as cached execution plans)
I'm not sure which I'd use...the difference between the two at the point you
are at is pretty minimal...
Here's an *must read* guide for anyone writing dynamic sql :) http://www.sommarskog.se/dynamic_sql.html
Karl
-- http://www.openmymind.net/ http://www.fuelindustries.com/
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.mach in AT dot.state.fl.us > wrote
in message news:uI******** ******@tk2msftn gp13.phx.gbl... Hi,
"WebMatrix" <We*******@disc ussions.microso ft.com> wrote in message news:02******** *************** ***********@mic rosoft.com... Hello,
I am developing ASP.NET 1.1 application against SQL Server 2000 db, where users have ability to construct their own select criteria (for a where clause). I already developed a user control where user can select field name, operator (equals, like, begins with, etc) and value of the search criteria. This control can already build where clause part. Now I am at the point, where I need to execute SQL query, but I would really like to be able to use stored procedures.
You cannot use a SP for this, the query will change all the time therefore the engine cannot precalculate the execution path of it. you will have to submit it as CommandType = Text
One warning I give you is taht you have to be careful with SQL injection, the query can be injected both in the select as well as in the where clauses -- Ignacio Machin, ignacio.machin AT dot.state.fl.us Florida Department Of Transportation
Check this article: http://www.sqlservercentral.com/colu...terproblem.asp
(which I wrote)
You're going to get some "dynamic sql is ok", but I don't like it. Please,
I know the arguments, and don't need another round of it. (or others
reading).
You can use your select screen to create the Xml, which gets passed into the
procedure.
...
If you're using Access. then you have no choice, but to build the SQL.
If you're using Oracle, the last time I used Oracle 9, it was very very
cumbersome to use their XML model.
...
"WebMatrix" <We*******@disc ussions.microso ft.com> wrote in message
news:02******** *************** ***********@mic rosoft.com... Hello,
I am developing ASP.NET 1.1 application against SQL Server 2000 db, where users have ability to construct their own select criteria (for a where clause). I already developed a user control where user can select field
name, operator (equals, like, begins with, etc) and value of the search
criteria. This control can already build where clause part. Now I am at the point, where I need to execute SQL query, but I would really like to be able to
use stored procedures.
As far as I know, I have 2 options: (1) Pass in "where clause" string to
SP (2) Execute SQL Query inside ASP.NET
Are there any other options? If anyone ever had to implement something similar, I want to hear your comments. Thanks
You can definitely create dynamic SQL in an SPROC. And I don't beleive you
do suffer from the caching issue (an urban myth these days as SQL no longer
"precompile s" procs, but instead stores execution plans for all queries).
Here is a working example.
create proc RepMetaStateBoo kList
@year char(5),
@range varchar(10)
as
declare @sql varchar(2000)
set @sql = '
select top 100
Author,
Title,
count(*) Lists
from list l
join list_detail d on l.listID = d.listID
join book b on d.bookID = b.bookID
join author a on b.authorID = a.authorID
where ( list_name like ''%:%' + @year + '%'' )
and left(list_name, 2) in (select code from arlist_dev..sta te)
' +
case
when @range = 'PK-2' then ' and cast(level_atos as float) <= 2.9 '
when @range = '3-5' then ' and cast(level_atos as float) between 3.0 and
5.9 '
when @range = '6-8' then ' and cast(level_atos as float) between 6.0 and
8.9 '
when @range = '9-12' then ' and cast(level_atos as float) >= 9.0 '
when @range = 'ALL' then ''
end
+ '
group by author, title
having count(*) > 1
order by count(*) desc, author, title '
exec ( @sql )
"WebMatrix" <We*******@disc ussions.microso ft.com> wrote in message
news:02******** *************** ***********@mic rosoft.com... Hello,
I am developing ASP.NET 1.1 application against SQL Server 2000 db, where users have ability to construct their own select criteria (for a where clause). I already developed a user control where user can select field name, operator (equals, like, begins with, etc) and value of the search criteria. This control can already build where clause part. Now I am at the point, where I need to execute SQL query, but I would really like to be able to use stored procedures.
As far as I know, I have 2 options: (1) Pass in "where clause" string to SP (2) Execute SQL Query inside ASP.NET
Are there any other options? If anyone ever had to implement something similar, I want to hear your comments. Thanks
Hi,
"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:%2******** ********@TK2MSF TNGP14.phx.gbl. .. Sure you can. stored procedures can dynamically execute sql via a number of functions, include exec and sp_execute.You do lose out on some of the sproc benefits though (such as cached execution plans)
IMO it would be the same, just adding another layer ( has to invoke the
SP ).
I'm not sure which I'd use...the difference between the two at the point you are at is pretty minimal...
Here's an *must read* guide for anyone writing dynamic sql :)
http://www.sommarskog.se/dynamic_sql.html
Very good article, thanks for the link
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: alanstew |
last post by:
With the body tag calling out 'window onload', a function with a
'window.open' fails at the 'window.open' line. If I cut out the body
tag, the function executes as normal.
At first I thought it was the entire function failing, but I tested
with alerts and found that it was only the 'window.open' that fails to
execute.
The function is being called by a link, and I suspected some problem
with the body alink/vlink but after cutting that out...
|
by: Matt Kruse |
last post by:
http://www.JavascriptToolbox.com/bestpractices/
I started writing this up as a guide for some people who were looking for
general tips on how to do things the 'right way' with Javascript. Their code
was littered with document.all and eval, for example, and I wanted to create
a practical list of best practices that they could easily put to use.
The above URL is version 1.0 (draft) that resulted. IMO, it is not a
replacement for the FAQ,...
|
by: audleman |
last post by:
I'm attempting to build a query that involves a table called "All
Students".
The query is simply
sqlString = "SELECT * FROM All Students"
but I get "Syntax error in FROM clause" when I try to run it. I assume
this is because my table is two words. How do I get Access to recognize
it?
|
by: WebMatrix |
last post by:
Hello,
I am developing ASP.NET 1.1 application against SQL Server 2000 db, where
users have ability to construct their own select criteria (for a where
clause). I already developed a user control where user can select field name,
operator (equals, like, begins with, etc) and value of the search criteria.
This control can already build where clause part. Now I am at the point,
where I need to execute SQL query, but I would really like to...
|
by: roger23 |
last post by:
I get this error
C:\Program
Files\MSBuild\Microsoft\WebDeployment\v8.0\Microsoft.WebDeployment.targets(526,9): error MSB6006: "aspnet_compiler.exe" exited with code 1.
at the end of my build step of my web deployment project. it does not give
me any error messages to debug.
the only place where i can see some problem is this:
Dependency "System.Configuration, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a".
| |
by: Nathan Gilbert |
last post by:
I am wanting to use javascript to select between different *.css files
dependent on the user's browser. I am also wanting to generate the
html document containing this javascript dynamically using PERL.
So far, I have the javascript that does what I want to do, and it works
fine as long as the the page was not generated by PERL.
My problem is that when the javascript/html is generated by
my PERL scripts the javascript never gets...
|
by: uncensored |
last post by:
Hi,
Sort of new with the whole treeview control and I was wondering is there a way to build a treeview menu using my SQL data I pull from a database instead of having to hard code it into the webpage. Pasted below is my current code but what I would like to do is instead make a table in SQL that data can be added or modified to and have it generate the menu according to the data instead. Thanks for the help,
Mike
<mytree:treeview...
|
by: mowsen |
last post by:
Hello Group,
i'm using a little "ajax" loader script to dynamically load files into
different "div" tags on my main site. the code for this part looks
like:
function loader() {
var args = loader.arguments;
switch (args) { case
|
by: NeoAlchemy |
last post by:
I am starting to find more web pages that are using a query parameters
after the JavaScript file.
Example can be found at www.opensourcefood.com. Within the source
you'll see: <script src="/shared/scripts/common.js?revision=1.6"
type="text/javascript">.
I am trying to see if there is any big deal to this or a best practice
that is starting to creep up in the JavaScript community. If this is
used only as a way to distinguish what...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |