I’m sure some of you have seen my other posts and know that I’m still learning C# slowly but surely.
I need help with Data tables tho. I’m working on creating a simple application to display appliance test results on a TV display. I am going to be collecting data from a SQL Database and placing the Data into a Data Table. Each test has several different defects that are checked and stored on this database. The display needs to show the top 5 most common defects for the current day. Can you guys give me some pointers and help of how to go about sorting the table and reading through it to find how many appliances failed for each specific defect. If you need more information just ask. Thanks people!! 
What SQL DBMS are you using (SQL Server, Oracle, etc.)? What does your schema look like (table definitions)?
I am assuming SQLite on EMX?
I’m not the one that created the database but as far as i know its an SQL Server. I don’t have access to the database right now because my co-worker who set it up is out of the office now but I will find that out. I will confirm this but i believe that the tables that ill be pulling from the database will have a few columns for identifying the appliance and whether its had the test ran yet followed by a column for each of the Defects that we test for with the data being either true or false. So pretty cut and dry.
Not using .netmf but just .net so full framework
You’ll basically be looking for something like this…
select top 5 count(*) defectCnt, defectId
from dbo.defects
where someDate > getdate() - 1
group by defectId
order by defectCnt desc
Or consider using DPWS,
It will enable to compute calculation on the SQL Server side, and only return needed results to the embedded part…
I do the same thing to retrieve datas from an SQL Server 2008 database to my chipworkx, and however I have some troubles with the DPWS Class generator tool (Mfsvcutil), the result is really nice in terms of speed !
Look at this for a start point :
Thanks for the help guys. This is one of the query’s that I ended up using to pull the data. The others look very similar to it.
sSQL = "SELECT count(DISTINCT(Serial)) AS firstTotal FROM TransferStatus WHERE Line = '" + drLine["Line"].ToString() + "' AND TransferDateTime >= '" + Date1.Text + "' AND TransferDateTime <= '" + Date2.Text + "' AND TransferStatus.Serial <> 'UNKNOWN' " + OptionalSQL;
lyYield.sSQLUsed += sSQL + "<br/>";
dt = sql.SQLSelect(sSQL);
if (dt.Rows.Count > 0)
lyYield.iOverall1 = lyYield.iOverall = int.Parse(dt.Rows[0][0].ToString());
Why this mix between SQL and HTML
?
Also, you are vulnerable to SQL injection. Your SQL programming rights have been revoked until you learn to parameterize your queries.
@ godefroi - lol
i remember the good old days… ’ or ‘1’=‘1’ – ’
That code is simply for getting the number of units that have been tested during a day. The code for the original question is this
string sSQL = "SELECT TOP (100) PERCENT COUNT(dbo.TestResult.TestCode) AS num, dbo.TestCode.Description, TestCode.TestCode, TestType FROM TestResult LEFT JOIN TestCode ON TestResult.TestCode = TestCode.TestCode";
sSQL += " WHERE (TestResult.Pass = '0') AND TestResult.TestDateTime >= '" + Date1.Text + "' AND TestResult.TestDateTime <= '" + Date2.Text + "'" + OptionalSQL;
sSQL += " GROUP BY TestCode.Description, TestCode.TestCode, TestType ORDER BY num";
DataTable dt = sqlTCI_Test.SQLSelect(sSQL);
Noooo I need rights!! How else will i learn. im working on it lol.
To answer your question though lyYield.sSQLUsed is only used if I want to send a debug message to the screen so the
is a line break. This is part of a test web server so I won’t actually end up using
. In fact I just removed it from my code.
lyYield.sSQLUsed += sSQL + "<br/>";
Trial and error and asking questions does wonders 
Start here: http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html
Trial and error is indeed good, but this kind of error leads to lawsuits, lost data, government fines (in my industry), and in the worst cases, the end of a business.