Working with a database

Question

0.00/5 (No votes)

See more:

I'm using C#, and an Access database (will eventually be sql). What I'm attempting to do is read data from two columns. One column is the identifier, and the other is the data I need.

I only need data from specific rows though. The data (measurements) comes in from a machine automatically. I'd like to be able to read this data in real time, each time it comes in, and then have a rectangle change color, gradient style, based on the measurement. The idea is that someone could look at the screen and easily see if their number is acceptable, based on its color. Green, yellow and red, intensity changes based on how close measurement is to its min/max limits.

I do not actually need to see the data, controlling colors in the object would be more important.

Here's an example:

1. Data gets read into the database
2. My program is alerted (or is just set to check every 60 seconds)
3. The program iterates through a loop, checking the data against a standard

(i.e., should be 1.0 +/-.5. Is actually .51 so a rectangle shows yellow)
or
(i.e., should be 1.0 +/-.5. Is actually 1.48 so a rectangle shows red)

A number close to nominal would show green, the lower it goes it would start to become more yellow. The higher it goes, it gets more red. There are probably 20 different dimensions this would apply to, and there would be a rectangle for each.

That's the basic idea.

What I have tried:

I have connected to the database and found the table and columns I need. I have tried many numerous things to make this work, but databases are not something I'm used to working with, so I cannot even express clearly what I've tried. I'm hoping someone can point me in the right direction.

Posted 28-Feb-19 8:07am

Smeezy

Updated 1-Mar-19 1:58am

v2

Add a Solution

4 solutions

Solution 1

Maybe you can use OleDbDataReader, see example here: Using Access databases in C#? - Stack Overflow[^]

Posted 28-Feb-19 9:13am

RickZeeland

Solution 2

A few parts to this.

The first is going to be getting the value from the database. Really not too much to work with on the specifics from your question, but from what I can tell is that you working with MS Access. This is a pretty generic sample as I know nothing about the tables or column names. What this will do is just get the value back from whatever instrument identifier you pass in. You will obviously need to change some values and possible types.

C#

public Decimal GetInstrumentValue(int InstrumentID) {
     string AccessConnection = "ACE12 connection string";
     string AccessQuery = "SELECT Reading FROM Table WHERE Identifier = ?";
     OleDbConnection conn = new OleDbConnection(_con)) {
          OleDbCommand cmd = new OleDbCommand(AccessQuery, conn);
          cmd.Parameters.AddWithValue("@Identifier", YourIdentifierValue);
          conn.Open();
          Decimal RetrievedValue = (Decimal)cmd.ExecuterScalar();
          conn.Close();
     }
}

Now for the coloring portion. The simple yellow-green-red with absolute thresholds is simple if-then logic

// NO specific language here, proto-code
if (value < 0.5) set color to yellow
else if (value >  1.5) set color to red
else set color to green

Doing the gradual color change is going to be a little more complicated without a million if-then lines.

Posted 28-Feb-19 11:19am

MadMyche

Solution 4

Am I able to add images with these questions?

There are 80 tables in this database. All of my data is coming from about 4 tables. This one provides the actual measurements.

datapage.mbd (Database)
dbo_FullView_Transaction_Data (Table)
Variable_SID (column with unique number for each row in the Data_Value column)
Data_Value (column with data results)

Posted 1-Mar-19 1:59am

Smeezy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Smeezy · Accepted Answer · 2019-03-01T01:15:00

Thanks for that. I thought seeing as how I was already connected to the database that none of this:

"

string AccessConnection = "ACE12 connection string";
      string AccessQuery = "SELECT Reading FROM Table WHERE Identifier = ?";
      OleDbConnection conn = new OleDbConnection(_con)) {
      OleDbCommand cmd = new OleDbCommand(AccessQuery, conn);

would have been necessary. Or should I be looking at disregarding my current connection, and just pulling the data I need with the code you provided? This is just in a testing phase. The actual program will be using an SQL database.

As for everything else, I only need to use standard SQL queries to achieve my goal?