Allowing users to run querys using webforms? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Allowing users to run querys using webforms?

Author

Topic

goingcrazy
Starting Member

6 Posts

Posted - 2003-05-30 : 22:40:58

What is involved in this?

I have a simple database, I want users to go to an intranet website and be able to fill in a simple search form box with a submit button that will run a query on what they enter.

Example,

I have a table called watches
I have a "productName" column and a "productID" column

So from the website I want form and let's say a user typed "rolex" into this form and hit submit, this would run displaying the results in the persons webbrowser:

select ProductName as 'Watches', productID as 'Serial Number'
from products
where ProductName = 'rolex'
order by productid

The problem is, I can't just make a static proc because I want the user to have control over what they search on. So in the form if they typed "omega" instead of "rolex" and hit submit this would run...

select ProductName as 'Watches', productID as 'Serial Number'
from products
where ProductName = 'omega'
order by productid

Do I need to look into something like XML for this? I can run querys using the SQL XML setup for IIS but I can't find any docs on setting variables for forms in XML.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-05-31 : 00:59:25

What you need is a parameterized query. Your stored procedure might look like this...

create proc sprFindByProduct
  @ProductName varchar(20)
as
select ProductName as 'Watches', productID as 'Serial Number' 
from products 
where ProductName = @ProductName 
order by productid

This is the preferred way for most users that are comfortable with SQL Server. Alternate methods would be to build a dynamic string in your weboform, for example...

StringBuilder whereClause = new StringBuilder();

whereClause.Append("select ProductName ''Watches'', productID as ''Serial Number'' 
from products 
where ProductName = ");
-- add your criteria
whereClause.Append(txtBrand.Text);
whereClause.Append("order by productid");

Pass that string to your data adapter. I believe, though have not used it, there is a filter property for the data adapter as well that you may want to look into.

hth,
Justin

"Contrary to its syntax GETDATE() still leaves you alone on Friday night."

<edit> to fix display </edit>

Edited by - robvolk on 05/31/2003 10:29:09

sgraessle
Starting Member

1 Post

Posted - 2003-05-31 : 19:04:57

You have to refer to the textbox name. Otherwise you are sunk. Here is how to do it:
IF document.form1.chapter.value="" and document.form1.verse.value="" then
sql="Select * from newbible where book like '" & book & "%' order by booknum, chapter, verse"
objRS.Open sql, objConn , adOpenForwardOnly , adLockReadOnly , adCmdText
----
Book is the variable name that you passed from the textbox where they put the book they want to search. Good Luck. Steve Graessle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources