Inserting nulls

Question

Inserting nulls

as_1234

SSCrazy

Points: 2871
More actions
August 20, 2024 at 3:17 pm

#4447690
Apologies as this has probably been asked. I searched but couldn't find it.
I have the following code:
```
        if ([string]::IsNullOrEmpty($OrgID) -eq $true)
        {
          write-host "Null found"
          $OrgID=[DBNull]::Value
        }
        write-host "insert into dbo.Org(id,code) values ($OrgID,'$orgcode')"
        $sqlcomODS.CommandText="insert into dbo.Org(id,code) values ($OrgID,'$orgcode')"
        $sqlcomODS.ExecuteNonQuery() | Out-Null
```
dbo.org.id is an int. I'm trying to deal with the fact that sometimes $orgid is null or blank and convert it to a null.
For an empty/null value the output contains "Null found" so the if statement appears to work however the second write-host gives
```
insert into dbo.Org(id,code) values (,'ABCD')
```
and therefore generates a SQL syntax error.
Is there a way of inserting nulls or do I have to switch to something like Write-DataTable?
I know I could use an if statement to decide whether to include the field in the sql statement but I'm looking for something neater especially as I have several fields to manage.
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16224 More actions · Answer 1

replace your code with

if ([string]::IsNullOrEmpty($OrgID) -eq $true)
        {
          write-host "Null found"
          #$OrgID=[DBNull]::Value # not needed neither possible if $OrgID was defined as a int in PS
        }
        write-host "insert into dbo.Org(id,code) values ($OrgID,'$orgcode')"

        # replace original line with one of the 2 options beelow
        #$sqlcomODS.CommandText="insert into dbo.Org(id,code) values ($OrgID,'$orgcode')"

        # any PS version
        #$sqlcomODS.CommandText="insert into dbo.Org(id,code) values ($(if([string]::IsNullOrEmpty($OrgID)){"NULL"}else{$OrgID}),'$orgcode')"

        # PS 7 or higher
        #$sqlcomODS.CommandText="insert into dbo.Org(id,code) values ($([string]::IsNullOrEmpty($OrgID) ? "NULL" : $OrgID),'$orgcode')"
        $sqlcomODS.ExecuteNonQuery() | Out-Null

but you should instead modify your code to use parameters instead of variable replacement - that way not only code can get better structured as it also prevents SQL Injection.

plenty of examples on net - one here

frederico_fonseca SSCoach Points: 16224 More actions · Answer 2

dup post

This reply was modified 9 months, 3 weeks ago by frederico_fonseca.

as_1234 SSCrazy Points: 2871 More actions · Answer 3

Thanks for your help.

The code you provided solved the issue and you are right I need to change it due to the SQL injection risk which I will do.

Inserting nulls

Cookies on SQLServerCentral