Is not the syntax of the SQL (i.e. whether ? is allowed or not) dependant on the data access provider? If you are connecting to Access with the jet engine then shouldn't the ? be the character you always use for parameters? If you are connecting with ADO.Net, is the protocol the same? I have never connected to Access from .net, only used MSSQL. But I would speculate that it's all the same because of the common data provider.

Guys,

I am using parameters, what I want to happen is if a user selects a particular area from a drop down list then only houses from that area are displayed, this works fine within my code. I also have an "all" selection which should display all the houses from every area. This is my code: -

private void GridDataFiller(object sender, EventArgs e)
{
  if(Page.IsValid)
  {
    string strSmokingFilter = Smoking.SelectedItem.Text.ToString();
    string strAreaFilter = ddlAreas.SelectedItem.Text.ToString();
    string strMixed = Furnished.SelectedItem.Text.ToString();
    String strShowDetails;


    lblDescription.Visible = false;
  String strConnect = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = \\\\premfs3\\sites\\premium8\\adnanarab66\\databas e\\House.mdb;";
  OleDbConnection objConnection = new OleDbConnection(strConnect);
  if(strAreaFilter == "all")
  {
      strShowDetails = "SELECT * FROM Houses WHERE RentingPrice <= ? AND HouseHold = 'Baffins' AND Smoking = ? AND Gender = ?";
  }
  else
  {
       strShowDetails = "SELECT * FROM Houses WHERE RentingPrice <= ? AND HouseHold = ? AND Smoking = ? AND Gender = ?";
  }

  OleDbCommand objCommand = new OleDbCommand(strShowDetails, objConnection);

  objCommand.Parameters.Add("RentingPrice", OleDbType.Integer, 6, "RentingPrice");
  objCommand.Parameters.Add("HouseHold", OleDbType.VarChar, 32, "HouseHold");
  objCommand.Parameters.Add("Smoking", OleDbType.VarChar, 32, "Smoking");
  objCommand.Parameters.Add("Gender", OleDbType.VarChar, 32, "Gender");

  objCommand.Parameters["RentingPrice"].Value = Payment.Text;
  objCommand.Parameters["HouseHold"].Value = strAreaFilter;
  objCommand.Parameters["Smoking"].Value = strSmokingFilter;
  objCommand.Parameters["Gender"].Value = strMixed;

  OleDbDataReader objDataReader = null;

  try
  {
      if(objConnection.State != ConnectionState.Open)
    {
    objConnection.Open();
    }
      objDataReader = objCommand.ExecuteReader();
          if(objDataReader.HasRows)
          {
              dgHouse.Visible = true;
              dgHouse.DataSource = objDataReader;
              dgHouse.DataBind();
            btnSaveHouses.Visible = true;
          }
          else
          {
              dgHouse.Visible = false;
            lblDescription.Visible = true;
            lblDescription.Text = "Sorry but there are no houses with your criteria, please try again";
            btnSaveHouses.Visible = false;
          }
    }
      catch(Exception ex)
      {
    Response.Write(ex.Message);
      Response.End();
      }
      finally
      {
    objDataReader.Close();
    objConnection.Close();
    }
  }
}

When "all" is selected, nothing is returned, but when "Baffins" is selected a value is returned. What am I doing wrong??

Any ideas

Adz - The World is not enough

I have managed to solve this here is my code: -

if(strAreaFilter == "all")
  {
      strShowDetails = "SELECT * FROM Houses WHERE RentingPrice <= " + IntegerPayment + " AND Smoking = '" + strSmokingFilter + "' AND Gender = '" + strMixed + "'" ;
  }
  else if(strSmokingFilter == "both")
  {
      strShowDetails = "SELECT * FROM Houses WHERE RentingPrice <= " + IntegerPayment + " AND HouseHold = '" + strAreaFilter + "' AND Gender = '" + strMixed + "'" ;
  }
  else if(strAreaFilter == "all" && strSmokingFilter == "both")
  {
      strShowDetails = "SELECT * FROM Houses WHERE RentingPrice <= " + IntegerPayment + " AND Gender = '" + strMixed + "'" ;
  }
  else
  {
       strShowDetails = "SELECT * FROM Houses WHERE RentingPrice <= ? AND HouseHold = ? AND Smoking = ? AND Gender = ?";
  }

guys is there anything wrong with this line of code: -

else if(strAreaFilter == "all" && strSmokingFilter == "both")
  {
      strShowDetails = "SELECT * FROM Houses WHERE RentingPrice <= " + IntegerPayment + " AND Gender = '" + strMixed + "'" ;
  }
I dont get a result for this, if I use the select statement by itself then it works. Any ideas??

Adz - The World is not enough

In your drop-down list, do you have All or all? Comparing strings is case sensitive.... Are you sure your code hits the statement at all?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar,

yes I have, the other two selections work but when I put bot together they do not work??

Adz - The World is not enough

I could be wrong, but I think you never hit the third statement, because one of the first two has already been hit.

If strAreaFilter equals all and strSmokingFilter equals both, then the first of your SQL statements will be used, not the third.
I think you should swap them around. First, check for both conditions. If not both of them match, then hit one of the two others.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Nice one,

thats what is was

Thanks Imar

Adz - The World is not enough

Yes, thats it,

thanks for your help Imar

Adz - The World is not enough