0

I am setting up a Web API and currently only have Person that I can get with id or get a list from SQL-Server. But when I try to insert into the database it throws an exception

System.Data.SqlClient.SqlException: 'Invalid column name 'firstname1'. Invalid column name 'lastname1'. The multi-part identifier "somethinggmail.com" could not be bound.'

This A class library .Net.Framework and so far when I request information there has been no problem.

I'm a newbie in this so I have no previous experience

using DataClassLibrary.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;

namespace DataClassLibrary
{
    public static class DataAccess
    {
        public static List<Person> GetPeople()
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConnectionVal("FirstDB")))
            {
                return connection.Query<Person>($"SELECT [ID] ,[FirstName] ,[LastName] ,[Email] FROM [FirstDB].[dbo].[Person]").ToList();
            }

        }

        public static void InsertPerson(Person p)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConnectionVal("FirstDB")))
            {
                connection.Query<Person>($"INSERT INTO [FirstDB].[dbo].[Person] (FirstName ,LastName ,Email) VALUES (" + p.FirstName + "," + p.LastName + "," + p.Email + ")");
//connection.Query<Person>($"INSERT INTO [FirstDB].[dbo].[Person] (FirstName ,LastName ,Email) VALUES ( { p.FirstName } , { p.LastName } , { p.Email } )");
            }
        }
    }
}

The result I want is to insert a person into my database

j.doe
  • 662
  • 4
  • 19
user11928522
  • 11
  • 2
  • 7
    You are using string concatenation to pass values yo sql queries. You should use parameterized queries to avoid such issues. https://stackoverflow.com/questions/5957774/performing-inserts-and-updates-with-dapper. – Chetan Aug 18 '19 at 15:46
  • 4
    First - your connection string should determine the database used by your queries. Embedding the database name in your queries only makes your code more difficult to use in different environments and configurations. Next. learn how to parameterize your sql statements. Lastly, the string delimiter in tsql is a single quote character - do not use double quote characters. That is the source of your problem. – SMor Aug 18 '19 at 15:55
  • 2
    As @SMor already mentioned TSQL uses a single quotes as the delimiter for string but the problem is not that you are using double-quotes. The problem is that you are not using ANY delimiter characters when you concatenate the variable strings in the `VALUES (` clause in the SQL statement. Without the single quotes around the string values the `INSERT` will fail. – David Tansey Aug 18 '19 at 16:26

0 Answers0