C#•2y ago

DotNet and Dapper. Incorrect syntax near ','.

Hello folks, Im trying to execute a sql query:

c++
[HttpPost]
        public async Task<ActionResult<List<Step>>> PostNewStepInfo(string databaseName, List<Step> steps)
        {
            try
            {
                using var connection = new SqlConnection(_config.GetConnectionString("Default"));

                foreach (var step in steps)
                {
                    var test = await connection.ExecuteAsync($@"
                    INSERT INTO[{databaseName}].[dbo].[StepTable] ([Type], [ParentKey], [SubWorkflowCode], [Sequence], [WorkflowKey]) 
                    OUTPUT INSERTED.[Key] 
                    VALUES('{step.Type}', {step.ParentKey}, {step.SubWorkflowCode}, {step.Sequence}, {step.WorkflowKey});"
                        );

                    foreach (var parameter in step.Parameters)
                    {
                        await connection.ExecuteAsync($@"
                        INSERT INTO [{databaseName}].[dbo].[ParameterTable] ([Name], [Source], [Value], [WorkflowStepKey], [GroupCode], [EditedByCustomer]) 
                        VALUES ('{parameter.Name}', '{parameter.Source}', '{parameter.Value}', {test}, 'NULL', 0);"
                            );
                    }
                }
                return Ok(steps);
            }
            catch (SqlException ex) {
                Console.WriteLine($"SQL Exception: {ex.Message}");
                throw;
            }
        }

c++
[HttpPost]
        public async Task<ActionResult<List<Step>>> PostNewStepInfo(string databaseName, List<Step> steps)
        {
            try
            {
                using var connection = new SqlConnection(_config.GetConnectionString("Default"));

                foreach (var step in steps)
                {
                    var test = await connection.ExecuteAsync($@"
                    INSERT INTO[{databaseName}].[dbo].[StepTable] ([Type], [ParentKey], [SubWorkflowCode], [Sequence], [WorkflowKey]) 
                    OUTPUT INSERTED.[Key] 
                    VALUES('{step.Type}', {step.ParentKey}, {step.SubWorkflowCode}, {step.Sequence}, {step.WorkflowKey});"
                        );

                    foreach (var parameter in step.Parameters)
                    {
                        await connection.ExecuteAsync($@"
                        INSERT INTO [{databaseName}].[dbo].[ParameterTable] ([Name], [Source], [Value], [WorkflowStepKey], [GroupCode], [EditedByCustomer]) 
                        VALUES ('{parameter.Name}', '{parameter.Source}', '{parameter.Value}', {test}, 'NULL', 0);"
                            );
                    }
                }
                return Ok(steps);
            }
            catch (SqlException ex) {
                Console.WriteLine($"SQL Exception: {ex.Message}");
                throw;
            }
        }

This is the data im trying to insert

[
  {
      "type": "a test",
      "parentKey": null,
      "subWorkflowCode": null,
      "sequence": 4,
      "workflowKey": 1,
      "parameters": [
          {
              "name": "od1",
              "source": "od1",
              "value": "od1"
          },
          {
              "name": "od2",
              "source": "od2",
              "value": "od2"
          }
      ]
  }
]

[
  {
      "type": "a test",
      "parentKey": null,
      "subWorkflowCode": null,
      "sequence": 4,
      "workflowKey": 1,
      "parameters": [
          {
              "name": "od1",
              "source": "od1",
              "value": "od1"
          },
          {
              "name": "od2",
              "source": "od2",
              "value": "od2"
          }
      ]
  }
]

Error:

(0x80131904): Incorrect syntax near ','.

(0x80131904): Incorrect syntax near ','.

Thanks in advance!

3 Replies

Tvde1•2y ago

Where do you think the error is? Which line is causing it? There is a lot of code in your snippet, irellevant to the error :p

Tvde1•2y ago

Also, give this a read :) https://www.learndapper.com/parameters

Dapper Parameter, SQL Injection, Anonymous and Dynamic Parameters

Dapper allows specifying parameters in querying methods to avoid SQL Injection. Learn more about how to use anonymous, dynamic, string, and output parameter

ZilOP•2y ago

ParentKey and SubworkflowKey are both empty when handled in the SQL query i just found out They are null in the request but empty strings in sql query. This is what the query looks like when its getting executed

c++
VALUES ('a test', , '', 4, 1);

c++
VALUES ('a test', , '', 4, 1);

yea my bad, i had no idea where the error was coming from thats why i just posted the whole endpoint

Gaming

Programming

DotNet and Dapper. Incorrect syntax near ','.