C
C#•2y ago
Zil

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
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
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
Zil
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

Did you find this page helpful?