SQL Script to delete empty rows in a table

[Rashar]

Hello, How do you delete a row in vb.net where there is blank data? I've tried the following, and all do not work... strSQL = DELETE FROM tblProducts WHERE prodID = & " " strSQL = DELETE FROM tblProducts WHERE prodID = & Nothing Thanks in advance.

[Colin Angus Mackay]

Rashar wrote:

strSQL = DELETE FROM tblProducts WHERE prodID = & " "

Aren't your quotes in the wrong place?

Rashar wrote:

How do you delete a row in vb.net where there is blank data?

What do you mean by "blank data"? From the two examples you gave you appear to be trying to check whether prodID is an empty string (or is that a string with one space), or possibly if it is NULL.

strSQL = "DELETE FROM tblProducts WHERE prodID IS NULL"

or

strSQL = "DELETE FROM tblProducts WHERE prodID = ''"

The first checks for a null (i.e. the absense of any value), the second checks to see if the value is an empty string. One of these is likely what you want - but you'll have to be more definite about what consitutes "blank data" in your vocabulary.

---

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[Rashar]

Colin Angus Mackay wrote:

What do you mean by "blank data"? From the two examples you gave you appear to be trying to check whether prodID is an empty string (or is that a string with one space), or possibly if it is NULL.

Yes. I'm looking to delete the record where prodID is a null field. I will try the first option. Thanks

[Dave Kreskowiak]

ProgID sounds like it should be a Primary key. If that's the case, why would it ever be NULL?? If this is the case, you've got much bigger issues that just finding and delete "empty" records. In a properly designed database, this procedure your trying to put together should never be necessary. Dave Kreskowiak Microsoft MVP - Visual Basic -- modified at 8:07 Monday 3rd July, 2006

[Rashar]

Actually, I was just using it as an example for a column name. I do have it set to a primary key, it's just that my end user may save a blank form which would populate the prodID column, but the rest of the columns may all be blank. If so, I wanted to clean up the table if there are columns that are blank. I could make it so that all the columns are not null, but with my program it can't work that way, as I need to request an ID first and foremost. Thanks.

[Dave Kreskowiak]

Rashar wrote:

it's just that my end user may save a blank form which would populate the prodID column

That's what validation is for! Validate the fields before you allow the user to save the record. If all the fields are not filled in, there's no reason you should be letting the user save an incomplete record. Dave Kreskowiak Microsoft MVP - Visual Basic