Database

select
Species.Name as Species_Name,
Species.CommonName as CommonName,
Family.Name as Family_Name,
v.NativeCode
from Species
left join 
(select 
Species.ID as SpeciesID,
SpeciesNativeTypeOptionList.Code as NativeCode
from Species
inner join SpeciesTypeInfo as SpeciesNative on Species.ID = SpeciesNative.SpeciesID 
inner join SpeciesTypeOptionList as SpeciesNativeTypeOptionList on SpeciesNative.SpeciesTypeOptionListID=SpeciesNativeTypeOptionList.ID
inner join SpeciesTypeList as SpeciesNativeTypeList on SpeciesNativeTypeOptionList.SpeciesTypeListID=SpeciesNativeTypeList.ID and 
SpeciesNativeTypeList.Name='Native'
) v
 on v.SpeciesID=Species.ID
inner join Family as Family on Species.FamilyID=Family.ID

declare @i as int<br />
set @i =  0<br />
declare @rowCount as int<br />
set @rowCount =(select count(SInumber) from myTable) <br />
<br />
while @i <= @rowCount <br />
begin<br />
     set @i = @i +1<br />
      <br />
     if(select count(SInumber) from myTable where SInumber =@i)=0 <br />
     begin<br />
          Update myTable set SInumber = @i where SInumber =@i+1          <br />
     end      <br />
end

<br />
Dim search_str As String = "SELECT * FROM Properties WHERE"<br />
Dim parameters As ArrayList = new ArrayList()<br />
<br />
If ddl_type.SelectedValue <> "0" Then<br />
  SqlParameter p = new SqlParameter("@Property_Type", SqlDbType.whatever)<br />
  p.Value = Server.HtmlEncode(ddl_type.SelectedItem.Text)<br />
  search_str += "Property_Type = @Property_Type"<br />
  parameters.Add(p)<br />
End If<br />
<br />
If ddl_location.SelectedValue <> "0" Then<br />
  SqlParameter p = new SqlParameter("@Property_Location", SqlDbType.whatever)<br />
  p.Value = Server.HtmlEncode(ddl_location.SelectedItem.Text)<br />
  search_str += "AND Property_Location = @Property_Location"<br />
End If<br />
<br />
' and so on and so forth....<br />
<br />
SqlCommand cmd = new SqlCommand(search_str)<br />
For i = 0 To parameters.Count - 1<br />
  cmd.Parameters.Add(CType(parameters(i), SqlParameter))<br />
Next<br />
<br />
' execute your query and return the result ....<br />

Mc--Gann wrote:

If ddl_type.SelectedValue <> "0" Then
search_str += "Property_Type =" + ddl_type.SelectedItem.Text
End If

Mc--Gann wrote:

Dim search_str As String = "SELECT * FROM Properties WHERE"

If ddl_type.SelectedValue <> "0" Then
search_str += "Property_Type =" + ddl_type.SelectedItem.Text
End If

If ddl_location.SelectedValue <> "0" Then
search_str += "AND Property_Location =" + ddl_location.SelectedItem.Text
End If

If ddl_location.SelectedValue <> "0" Then<br />
search_str += " AND Property_Location =" + ddl_location.SelectedItem.Text<br />
End If