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
– Jeroen Mostert, Commented Apr 10, 2019 at 11:12

JWallace · Accepted Answer · 2020-07-29 15:10:35Z

1

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

answered Jul 29, 2020 at 15:10

JWallace

4874 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related