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?

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

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?

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.

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

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?

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

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

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?

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