Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.0 and 1.1 Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 26th, 2005, 02:36 PM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default narrowing down the search

I use webmatrix/VB and I have a bunch of textboxes. The user is to fill out as many as needed to narrow down his search. So far I have
   Dim queryString As String = "SELECT [CSULOG05].[CUST_NAME] [CSULOG05].[office], [CSULOG05].[STATUS] FROM
[CSULOG05] WHERE (([CS ULOG05].[NOUN] like @NOUN) AND ([CSULOG05].[CUST_NAME] like @CUST_NAME) AND ([CSULOG05].[OFFICE]
like @OFFICE) AND ([CSULOG05].[STATUS] = @STATUS))"

but what if they keep the status textbox blank so it SHOULD return results regardless of status?


  #2 (permalink)  
Old September 27th, 2005, 05:27 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

I don't know webmatrix and use c# myself, but i know that i use in visual studio.Net (2003) the regularfieldvalidator and bind it to the textbox that must have a value. The code does not execute and the validator shows the field that must be completed.

In that way, the user will be warned about fields not completed and your coding is nothing, just set the control in design time.

Futhermore, by using the RegularExpressionValidator and setting the control to validate to the specific textbox (txtStatus) and by setting the expression to [a-zA-Z,0-9].{6,10} you can be sure that the user enters only valid characters and numbers. The last part in curly brackets after the dot indicates that the field must be a minimum of 6 characters and a maximum of 10 to be valid. Either ommit this part or ammend it to what you want it to be.

Regards
Helga

  #3 (permalink)  
Old September 27th, 2005, 08:17 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

But the thing is in forming my sql select statement. If some textboxes(variables) are blank I need to account for this. Does this sound confusing or can you figure out what I'm trying to say?


  #4 (permalink)  
Old September 27th, 2005, 08:23 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

I think i know what you mean. The status field may be blank. In this case you will have to use a if statement prior to making a sql querie i.e.
if (txtStatus.Text.ToString() = "") then
   'SQL Query without the where clause of status
else
   'Sql Query with the where clause of status
end if

Note: it will be usefull to ensure that the status is supplied correct, to use the Status as a drop-down list or something in this line so that the user can select iso typing the status. This will narrow your code and ensure more consistant results.


  #5 (permalink)  
Old September 27th, 2005, 08:26 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

Just one more remark. You've got two search fields called office & status thus your if statement will be a little bit bigger for example:
if (txtStatus.Text.ToString() = "") then
  if (txtOffice.Text.ToString() = "") then
    'plase your query here without any like clause
  else
    'plase query here with only like on the office
  end if
else
  if (txtOffice.Text.ToString() = "") then
    'place query here with only like re the status field
  else
    'place your complete query here
  end if
end if

Regards
Helga

  #6 (permalink)  
Old September 27th, 2005, 08:27 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, that what I meant. Thing is there are about 12 textboxes and user can fill as little or as many as he wishes. Do I have a mess on my hands with this?


  #7 (permalink)  
Old September 27th, 2005, 08:34 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, you do have a bit of a messup. You need to look at your database. Start implementing normalization for example.

1. Have a table with Offices
2. Have a table with Statusses
3. Look at your other fields, and see where you can implement the same.

Then, all the fields (i.e. the office & status) -> change them from textbox to drop-down list box (combobox) and force (via the regulator or by placing a default value in each drop-down) to select one of the options in the drop-downs.

This will ensure much better code and furthermore, it will limit the time the user takes to receive the data for example as your code currently is, all office can be retrieved leaving maybe on big grid, but by selecting a office and status, your queries is better, your code smaller and the returned records more what the user accepts.

Does this make sense? Remember a good app starts with a good database design.

Also try to user store procedures iso queries in your code, but first concentrate on the above and i'll help you through store procedures.

Regards
Helga

  #8 (permalink)  
Old September 27th, 2005, 08:47 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I was just thinking, there is another way to do this (but i'm not saying this is the best coding and db practice as per the books) but here you go:

private function MakeSQLStatement() as string
 dim sqlString as String
 dim sqlLike as String = ""
 'put here the beginning of the query until the like clause.
 sqlString = "SELECT...."
 'now build the query as you should go futher
 if (txtStatus.Text.ToString() <> "") then
  if (sqlLike = "") then
    sqlLike = " LIKE (Status = '" & txtStatus.Text.ToString() & "'"
  else
    sqlLike = sqlLike & " (Status = '" & txtStatus.Text.ToString() & "'"
  end if
 end if
 if (txtOffice.Text.ToString() <> "") then
  if (sqlLike="") then
    sqlLike = " LIKE (Office = '" & txtOffice.Text.Tostring() & "'"
  else
    sqlLike = sqlLike & " (Office = '" & txtOffice.Text.ToString() & "'"
  end if
end if
'Do same here for Each text boxes with data to search on

'Return value
MakeSQLStatement = sqlString & sqlLike

end function

OK, just check the VB syntax -> i'm used to the C# but it should be ok.

Do you understand this function?

Helga

  #9 (permalink)  
Old September 27th, 2005, 08:50 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, this database is long established and the fields are regular fields, item, office, cust_name, price, status, job_ord_numb,Po_nbr, qty,date_rec,date_close,cred_numb, etc. So this does mean a lot of "if" statements?


  #10 (permalink)  
Old September 27th, 2005, 08:51 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Use the second example i posted and let me know if you don't understand.

 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search button doesn't search Access DB cbones Visual Studio 2008 1 October 27th, 2008 08:36 PM
File Search / Indexing Search with .net 2.0 maulik77 ASP.NET 1.0 and 1.1 Basics 2 March 15th, 2007 01:45 AM
narrowing conversion - please help treasacrowe ASP.NET 1.0 and 1.1 Basics 0 October 18th, 2005 05:42 AM
Search Engine for Full-text Search Kala ASP.NET 1.0 and 1.1 Professional 2 August 29th, 2004 03:16 AM
Search engine that search through local drive! wenzation Classic ASP Basics 0 August 26th, 2003 10:15 PM



All times are GMT -4. The time now is 02:52 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.