Checking if a column is null with SQL

Question

I'm trying to find the first null column in a record and then save data to it.

My code will work the first time and save data to the first column, after that it doesn't seem to do anything. Any thoughts?

var col = 1;

        var emptyCheck = "SELECT * FROM Pictures WHERE P" + col + " IS NULL AND ID=" + WebSecurity.CurrentUserId;

                   while (db.QueryValue(emptyCheck) == 0) {

                    col++;     

                    };

                    if (db.QueryValue(emptyCheck) == 1) {
                        var update = "UPDATE Pictures SET P" + col + "='" + path + "' WHERE ID=" + WebSecurity.CurrentUserId;
                        db.Execute(update);
                    };

"the first null column in a record and then save data to it" --- that's a sign that you haven't designed your DB model properly. — zerkms
– zerkms, Commented Oct 5, 2014 at 22:23
Just write down what you are trying to achieve - it's a good start. Then look at how to solve it. — Peter Smith
– Peter Smith, Commented Oct 5, 2014 at 22:31
@Blake well, you have not really explained what you are trying to do so we can't suggest much other than "allow multiple rows in Pictures with the same ID but different pictures". Also you really should be using parameters not string concatenation. — Scott Chamberlain
– Scott Chamberlain, Commented Oct 5, 2014 at 22:31
Read up on "database normalization". It will make it clear why the approach you're taking is bad database design. Also look up "parameterized queries" and "SQL injection" while you're at it. — Daniel Mann
– Daniel Mann, Commented Oct 5, 2014 at 23:25

DeadZone · Accepted Answer · 2014-10-06 18:19:12Z

Based on your sample code and the comments, I'm going to suggest a more normalized approach. It appears as though your Pictures table looks something like:

Pictures { 
    ID Int,
    P1 varchar(??),
    P2 varchar(??),
    ...
}

... Where, I assume, P1..Pn is n columns that will contain paths to some image files. As previous comments said, this is not normalized and is a poor design. It might get the job done, but it's not scalable or efficient. Look at this article about normalization as a starter. If you change to something like:

Pictures { 
    UserID Int,
    PictureID Int,
    PicturePath varchar(??)
}

...then you would have one row per image per user. (The combination of UserID and PictureID will be unique. So each user can have PictureID of 1, 2, etc.) Then you don't have to worry about trying to find the "next null column". You just add a new row for each new picture.

Next point... Assuming that you were going to keep the current table structure for some reason. (not recommended.) Your logic shows that you are making (potentially) a lot of unnecessary calls to the database. Each query that you run is a round-trip to the database. Each round-trip takes up resources and time. Whenever possible, you should attempt to reduce the number of round-trips that your application needs to make. You will get better performance and better scalability.

Last point... One of the comments mentioned SQL Injection. The code that you provided is vulnerable to SQL Injection attacks. It's a fairly simple matter to fix that. But you do need to look it up and understand what it is and why your code is vulnerable.

Good luck!

beautifulcoder · Accepted Answer · 2014-10-05 22:33:01Z

0

First, your issue is:

var col = 1;

That hard codes it to only the first column. You need to wrap your logic around a for loop.

for (var col = 1; col < MANUAL_LIMIT; col++) {
    // Rest of your logic goes here
}

As mentioned, if you provide more info on the problem you are trying to solve we may be able to provide recommendations.

answered Oct 5, 2014 at 22:33

beautifulcoder

11.4k3 gold badges21 silver badges31 bronze badges

Collectives™ on Stack Overflow

Checking if a column is null with SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related