Filter Null Columns

Question

I have a database table that records medical tests. Each patient only takes certain tests, so out of the 10 columns, they will only have data in 4 or 5 of them.

I want to fill a dataGridView on a new form with 2 columns, one with the Column Name, and the second where a doctor can record the lab result.

Picture of the SQL database

Patient Num WBC Cholesterol Pregnant Herpes
Jon Doe "" "" NULL NULL

Desire for WinForm

DataGrid View

WBC ______
Cholesterol _______

Once the doc enters the values I will update the database.

I thought of doing this at first with XML, and I was able to write XML to the database with blank attributes, but had a hard time updating attributes and thoguht creating this new table would be better.

Akkiraju Ivaturi · Answer

You might consider the database structure like this: Create a master table for tests ====================== Table: Test Columns: TestID int primary identity(11) TestName varchar(500) Table: Patient Columns: PatientID int primary identity(11) PatientName varchar(500) PatientDetails Varchar(1024) Table:MedicalTests Columns: PatientID TestID Other columns that you would need to add. Write a stored procedure joining the tables to get the data. If you want sample data and query let me know I will spend sometime on this.

evan liewer · Answer

This does solve it sorry i thought i clicked the problem solved button. I have been thinking about how to create all the tests from a checked list box. I am thinking now of opening a SQL connection then using a foreach loop for each checked item and doing an insert for all checked items then close the SQL connection. Thanks guys.

Akkiraju Ivaturi · Answer

So is your problem solved? Or the question is still open? If so provide some extra information which may help me to answer you properly.

evan liewer · Answer

This makes good sense. This is how I handle the individual patient visits.

Sukesh Marla · Answer

agree with Akkiraju's answer