CODEWITHSUNDEEP
sql-serverpowershell
kobi55
kobi55

Reputation: 103

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

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?

Upvotes: 1

Views: 8958

Answers (1)

JWallace
JWallace

Reputation: 487

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')

Upvotes: 1

Related Questions