Issue to the command

Question

Hi,

In the codes, I have one insert command below but no relevant record was found to be inserted. How to find out the issue?

Answer 1

Hi @Peter_1985 , Welcome to Microsoft Q&A,

The insert statement in your screenshot is incomplete. (Do not use screenshots to submit questions.)

Before executing SQL commands, make sure that the database connection is open and that the SQL command object uses the correct connection object.

Make sure that the target table exists and that the table structure is correct, especially whether the column names, data types, etc. match the values ​​to be inserted.

For example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    sqlcommand cmd7 = new sqlcommand("INSERT INTO TableName (Column1, Column2) VALUES (@Value1, @Value2)", connection);
    cmd7.Parameters.AddWithValue("@Value1", value1);
    cmd7.Parameters.AddWithValue("@Value2", value2);
    cmd7.ExecuteNonQuery();
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I would first recommend Dapper installed via this NuGet package. The following example is setup to insert one record. We pass in an instance of a Person class, when done the Person instance has the new primary key set.

Notes

• There is no need to open/close the connection, Dapper does this for you
• The first example assumes no errors at runtime while the second assume there may be an exception.

Example 1:

public class DataOperations
{
    public static void AddPerson(Person person)
    {
        
        using SqlConnection cn = new("TODO");
        // Dapper opens the connection for you and closes it when done
        person.Id = cn.QueryFirst<int>("""
                                       INSERT INTO dbo.Person
                                       (
                                           FirstName,
                                           LastName,
                                           BirthDate
                                       )
                                       VALUES
                                           (@FirstName, @LastName, @BirthDate);
                                       SELECT CAST(scope_identity() AS int);
                                       """, person);
    }
}

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime BirthDate { get; set; }
}

Example 2:

Person person = new()
{
    FirstName = "John",
    LastName = "Doe",
    BirthDate = new DateTime(1980, 1, 1)
};

var (success, exception) = DataOperations.AddPerson(person);
if (exception is null)
{
    // person added successfully
}
else
{
    // handle exception
}

Backend

public class DataOperations
{
    /// <summary>
    /// Adds a person to the database.
    /// </summary>
    /// <param name="person">The person to add.</param>
    /// <returns>A tuple indicating the success status and any exception that occurred.   </returns>
    public static (bool success, Exception exception) AddPerson(Person person)
    {

        using SqlConnection cn = new("TODO");

        try
        {
            person.Id = cn.QueryFirst<int>("""
                                   INSERT INTO dbo.Person
                                   (
                                       FirstName,
                                       LastName,
                                       BirthDate
                                   )
                                   VALUES
                                       (@FirstName, @LastName, @BirthDate);
                                   SELECT CAST(scope_identity() AS int);
                                   """, person);
            return (true, null);
        }
        catch (Exception ex)
        {
            return (false, ex);
        }
    }
}