SQL Exception has been thrown by a target of an invocation

Hi!

I am completely stumped.
Currently, I am trying to make a script that puts a CSV file into a database table([Transactions].[dbo].[Payments]).
The script is:
image

fileName = “C:\\Users\\USER\\Downloads\\Payments-20210327_2214_MDT.csv”;
var qry = “DELETE FROM [Transactions].[dbo].[Payments]”;
sql.ExecSql(qry);
qry = "BULK INSERT [Transactions].[dbo].[Payments] "
+ “FROM '” + fileName + "’ "
+ "WITH "
+ "( "
+ "FIRSTROW = 2, "
+ "FIELDTERMINATOR = ‘,’, "
+ "ROWTERMINATOR = ‘\n’ "
+ ") ";
dlg.ShowMessage(qry);
sql.ExecSql(qry);

The qry is:
image
I keep getting the error:
image

Yet when I do the exact same code in SMSS:
image

DELETE FROM [Transactions].[dbo].Payments

BULK INSERT [Transactions].[dbo].[Payments]
FROM ‘C:\Users\USER\Downloads\Payments-20210327_2214_MDT.csv’
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’ )

It runs with no issue.

Here is the contents of the CSV file.(I have removed all the sensitive data and replaced it with other numbers/letters) Payments-20210327_2214_MDT.zip (576 Bytes)
(I tried pasting the CSV data, but it deletes a lot of the commas…)

Does anyone know why this happens?

Thank you!

can you post the table DDL so I can test

I mocked a table with NVARCHAR(255) as the column type for all columns and am able to import in SambaPOS without issue. When I mocked the table with some columns being INT or DECIMAL(16, 2) I get this exception:

System.Data.SqlClient.SqlException (0x80131904): Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (PaymentDate).

This happens with column types DATETIME and DATETIMEOFFSET.

27-Mar-2021 08:53 PM MDT is not a SQL DATETIMEOFFSET format. See if you can export in this format: 2021-03-27 20:53 -06:00 or if you don’t care about the offset use DATETIME for column type and format as 2021-03-27 20:53

1 Like

Reformatting the datetimes works on my end.

here’s the table DDL I used to mock:

IF OBJECT_ID('dbo.Payments') IS NOT NULL
  DROP TABLE dbo.Payments
GO

CREATE TABLE dbo.Payments
(
  PaymentDate DATETIMEOFFSET,
  PaymentId NVARCHAR(255),
  ExternalPaymentId NVARCHAR(255),
  InvoiceNumber NVARCHAR(255),
  CardAuthCode NVARCHAR(255),
  TransactionNumber NVARCHAR(255),
  Note NVARCHAR(255),
  Tender NVARCHAR(255),
  CardBrand NVARCHAR(255),
  CardNumber INT,
  CardEntryType NVARCHAR(255),
  Currency NVARCHAR(255),
  Amount DECIMAL(16, 2),
  TaxAmount DECIMAL(16, 2),
  TipAmount DECIMAL(16, 2),
  ServiceChargeAmount DECIMAL(16, 2),
  PaymentEmployeeId NVARCHAR(255),
  PaymentEmployeeName NVARCHAR(255),
  PaymentEmploteeCustomId NVARCHAR(255),
  OrderId NVARCHAR(255),
  OrderDate DATETIMEOFFSET,
  OrderEmployeeId NVARCHAR(255),
  OrderEmployeeName NVARCHAR(255),
  OrderEmplyeeCustomId NVARCHAR(255),
  Result NVARCHAR(255),
  Device NVARCHAR(255),
  NumberOfRefunds INT,
  RefundAmount DECIMAL(16, 2)
);
GO

reformatted date time columns:

Payments-20210327_2214_MDT_datetime_change.zip (599 Bytes)

here’s how to catch exceptions in-script:

function test()
{
    fileName = "c:/tmp/__temp/Payments-20210327_2214_MDT_datetime_change.csv";
    var qry = "DELETE FROM [Transactions].[dbo].[Payments]";
    sql.ExecSql(qry);
    qry = "BULK INSERT [Transactions].[dbo].[Payments] FROM '" + fileName + "' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')";
    
    var isSuccessful = host.tryCatch
    (
        function()
        {
            sql.ExecSql(qry);
        },
        function(exception) 
        {
            hostException = exception;
            return true;
        }
    );
    
    if (!isSuccessful)
    {
        exceptionMessage = hostException.GetBaseException().ToString();
        
        file.AppendToFile('c:/tmp/__temp/samba_exception.txt', exceptionMessage);
        file.AppendToFile('c:/tmp/__temp/samba_exception.txt', hostException.ToString());
        
        return exceptionMessage;   
    }
}

Also, I changed category to V5 Question as it seems SambaPOS and the script engine are working as designed.

1 Like

@Memo THANK YOU SO MUCH!
So…after you showed me how to catch the exception, and display it…
the exception was that Samba is not able to open the file(ACCESS denied)…and now I feel stupid.

I had the dates originally set as NVARCHAR.

Now I know how to catch exceptions! XD

1 Like