locked
Parameterized queries with optional parameters (Access DB) RRS feed

  • Question

  • User407764885 posted

    Ok, so I have a web programming assignment for university where I'm building a website that uses an Access database but I have hit a brick wall with something.

    I'm using parameterized queries to query my database. For one of my queries, I want to be able to take a bunch of parameters that are optional, and if they aren't needed I'll pass in a value of null and I'd like the part of the where clause relevant to them to evaluate to true. This is straightforward in most databases, from my understanding you could do something like to make param1 an optional parameter.

    WHERE ((@param1 = ARandomColumnName) OR (@param1 = null))

    However this can't be done using Access, as all parameters in a query are represented by a ?. Every ? is assumed to refer to a different parameter, so if you want to use a parameter more than one (like the above code I wrote does) then you'd have to pass it twice. This seems hacky to me

    So what do I do? I have spent *hours* googling and banging my head on the desk. I'll cry if I missed something really obvious :(


    Thanks in advance ~Heidi

    Friday, August 13, 2010 4:21 AM

Answers

All replies

  • User-1199946673 posted

    However this can't be done using Access
     

    Did you try?

    Every ? is assumed to refer to a different parameter, so if you want to use a parameter more than one (like the above code I wrote does) then you'd have to pass it twice.

    That's the other option. But I would use named parameters. Just try, you'll notice it will work!

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 13, 2010 6:11 AM
  • User-68639941 posted

    hi, refer below code

    string SqlString = "select * from table1 where (@name is null or name=@name)";
      
      {
       using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
       {
        cmd.CommandType = CommandType.Text;
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
        //cmd.Parameters.Add("@name", OleDbType.VarChar).Value = "karthick1";
        cmd.Parameters.Add("@name", OleDbType.VarChar).Value = DBNull.Value;

        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
       }
      } 

    Friday, August 13, 2010 6:28 AM