Filter Null Columns

evan liewer

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akkiraju Ivaturi14y ago
Accepted answer
You might consider the database structure like this:

Create a master table for tests
======================

Table: Test
Columns:
TestID int primary identity(1,1),
TestName varchar(500)

Table: Patient
Columns:
PatientID int primary identity(1,1),
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 liewer14y ago
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 Ivaturi14y ago
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 liewer14y ago
This makes good sense. This is how I handle the individual patient visits.
Sukesh Marla14y ago
agree with Akkiraju's answer