Exception calling "ExecuteNonQuery" with "0" argument(s)

Question

I have problem with my powershell script. I need insert into mssql database.

I get:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '00'." At C:\powershell\ImportPism.ps1:259 char:9 + $SqlCmd.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

Here is my code:

$sql = "
     INSERT INTO [dbo].[akcja]
       ([ak_akt_id]
       ,[ak_sp_id]
       ,[ak_kolejnosc]
       ,[ak_interwal]
       ,[ak_zakonczono]
       ,[ak_sc_id]
       ,[ak_pr_id])
 VALUES
       (
       $($item.akt_id)
       ,$($item.sp_id)
       ,0
       ,0
       ,$($item.data_dodania)
       ,NULL
       ,NULL)
select SCOPE_IDENTITY() as [InsertedId]
     " 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database =       $SQLDBName; User Id=$login; Password=$password; Integrated Security =     false";
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $sql
    $SqlCmd.Connection = $sqlConnection
    $SqlConnection.Open()
    $InsertedId = $SqlCmd.ExecuteScalar()      

    $SqlConnection.Close()

Why this not working?

Because there's a syntax error in your SQL after string replacement, evidently. It's not possible to tell exactly what without seeing the result. Use parameters instead of textually replacing values in the statement. String replacement looks convenient, until you run into issues like these (or worse, SQL injection). — Jeroen Mostert

JWallace JWallace · Accepted Answer · 2020-07-29T15:10:35

I got this error when not enclosing datetime parameters in single quotes.

E.g. make sure you add quotes in the string.format

String.Format("INSERT INTO [dbo].tbl VALUES ({0})

INSERT INTO [dbo].tbl VALUES (0001-01-01 00:00:00.000)

needed to be:

String.Format("INSERT INTO [dbo].tbl VALUES ('{0}')

INSERT INTO [dbo].tbl VALUES ('0001-01-01 00:00:00.000')

Exception calling "ExecuteNonQuery" with "0" argument(s)

1 Answers