C# query question

shinygold · #1 (**permalink**) 06-24-09, 13:05

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?

Teddy · #2 (**permalink**) 01-22-10, 12:39

It would help if you divulged the specific error.

jbedson · #3 (**permalink**) 01-22-10, 20:32

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?

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech