Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: C# query question

    Hi, sorry if this is the wrong place. I am wondering if it is possible to do this:

    Code:
    string query = "SELECT AVG(loc_pop) AS \'averagepop\' FROM POPULATION WHERE loc_pop != @blank";
    
    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    command.CommandText = query;
    
    command.Parameters.Add("@blank", OleDbType.Char).Value = "blank";
    
    textBox.AppendText("\r\n"+command.ExecuteScalar());
    This throws an error.

    loc_pop is a char (actually, it is a Microsoft Access 'Text' type, which I believe is actually a char, perhaps I am wrong). It holds numeric values except where it has a string value. That string value is always "blank". It will never be anything else.

    I would like to take the average of loc_pop for every numeric value, but not count its char values. I thought if the char values are "blank", I could tell it to average every field except where loc_pop = "blank".

    I know this is ugly database design: the string data and numerical data should be separated into two columns. But let's say I want to do it this way. Perhaps I'm dealing with an already-created database and need to make due.

    Is it possible?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It would help if you divulged the specific error.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2009
    Posts
    50
    Question: If you know your searching for everything that is not equal to "blank" then why not just put that it the query and skip the process of creating a variable that may be in conflict with Access data types?

    string query = "SELECT AVG(loc_pop) AS \'averagepop\' FROM POPULATION WHERE loc_pop != @blank";

    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    command.CommandText = query;

    command.Parameters.Add("@blank", OleDbType.Char).Value = "blank";

    textBox.AppendText("\r\n"+command.ExecuteScalar()) ;
    Try:
    string query = "SELECT AVG(loc_pop) AS \'averagepop\' FROM POPULATION WHERE loc_pop != 'blank' ";

    You may also want to try "IS [NOT]", "[NOT] LIKE", or [NOT] IN.
    != may be in conflict with the chars because the rest of the values in that field are numeric.
    But I'm just guessing at that.
    Hope this helps. Good Luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •