CODEWITHSUNDEEP
sql-serverpowershell
Nathan McKaskle
Nathan McKaskle

Reputation: 3083

SQL ExecuteNonQuery Error in Powershell Query with Boolean

I'm getting an error in my powershell script that is the following:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Invalid column name 'False'.

function InsertData {
    Param (
    [string]$username,
    [string]$fullname,
    [string]$email,
    [string]$phone
    )
    $DBServer = "SERVERNAME"
    $DBName = "Tool"
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=False;User Id = User;password = password"
    $sql = "INSERT INTO Employee (LocationId,FullName,username,email,phone,EquipId,SystemDetailId,migrationdate,UAT,bdpaccess) VALUES ('" + $location + "','" + $fullname + "','" + $username + "','" + $email + "','" + $phone + "',5,4," + $migrationdate + ",False,False)"

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection = $sqlConnection

    $sqlCommand.CommandText = $sql

    write-host $sql
    $sqlConnection.Open()
    $sqlCommand.ExecuteNonQuery()
    $sqlConnection.Close()
}

I'm apparently doing something wrong where I'm typing "False,False" as part of the statement. I tried all caps, lowercase, 0 and 1, nothing works. When I write it to the powershell command window using Write-Host $sql, it looks fine.

Upvotes: 0

Views: 1700

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416111

Sql Server (like most other DBs) does not have a real boolean type. There is no False. It does have a bit type with values 0 (false) and 1 (true). I've also seen char(1) (ie: 'Y' and 'N') and DateTime types (where NULL is false and any other value is the date on which the field became true) used as popular alternatives.

While I'm here, that code looks scary. Sql Injection is a real thing, and it's a big deal. Even benign data like some with the last name "O'Brien" could throw this query off in a big way. You need to look into parameterized queries. At minimum, looking more at the query text, I suspect the $migrationdate value needs single quotes around it.

Upvotes: 2

Related Questions