ExecuteNonQuery: CommandText property has not been initialized in c#

Question

I am trying to insert data from dynamically generated textbox of gridview in the database. however, I am getting the exception of the CommandText property has not been initialized cmd.executeNonQuery();

private void InsertRecords(StringCollection sc)
{
    StringBuilder sb = new StringBuilder(string.Empty);
    string[] splitItems = null;
    const string sqlStatement = "INSERT INTO Ingredients_List (Ingredients1) VALUES";
    foreach (string item in sc)
    {
        if (item.Contains(","))
        {
            splitItems = item.Split(",".ToCharArray());
            sb.AppendFormat("{0}('{1}'); ", sqlStatement, splitItems[0]);
        }

    }
    //  conn.Open();
    using (SqlConnection connn = new SqlConnection(GetConnectionString()))

    {
        using (SqlCommand cmd = new SqlCommand(sb.ToString(), connn))
        {
            connn.Open();
            //      cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            connn.Close();
        }
        // Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);
    }
}

Answer 1

Your problem is caused by the fact that you don't check if, after the loop, you have inserted anything in the StringBuilder. In case of no string with commas, then, the internal condition into the foreach statement, leaves the stringbuilder empty and the command will never have a proper command text.

The workaround should be simple like adding a test to see if there is some text inside the StringBuilder but I prefer to suggest you a different approach using a List<SqlParameter>

StringBuilder sb = new StringBuilder(string.Empty);
List<SqlParameter> prms = new List<SqlParameter>();
string[] splitItems = null;

// Base string for creating parameter placeholders dynamically
string sqlStatement = "INSERT INTO Ingredients_List (Ingredients1) VALUES (@p{0});";

int count = 1;
foreach (string item in sc)
{
    if (item.Contains(","))
    {
        splitItems = item.Split(',');

        // Parameter name created dynamically
        prms.Add(new SqlParameter($"@p{count}", SqlDbType.NVarChar) {Value=splitItems[0]});

        // Create the placeholder for the nth parameter
        sb.AppendFormat(sqlStatement, count);
    }
}

// Don't execute anything if there are no parameters (or stringbuilder empty)
if(prms.Count > 0)
{
    using (SqlConnection connn = new SqlConnection(GetConnectionString()))
    using (SqlCommand cmd = new SqlCommand(sb.ToString(), connn))
    {
        connn.Open();

        // Add all parameters together
        cmd.Parameters.AddRange(prms);
        cmd.ExecuteNonQuery();
    }
}

Answer 2

You're not loading the first part of your query into the StringBuilder. Try this:

private void InsertRecords(StringCollection sc)
{
    const string sqlStatement = "INSERT INTO Ingredients_List (Ingredients1) VALUES";
    StringBuilder sb = new StringBuilder(sqlStatement);
    string[] splitItems = null;
    foreach (string item in sc)
    {
        if (item.Contains(","))
        {
            splitItems = item.Split(",".ToCharArray());
           sb.AppendFormat("{0}('{1}'); ", sqlStatement, splitItems[0]);
        }
    }
    //  conn.Open();
    using (SqlConnection connn = new SqlConnection(GetConnectionString()))
    {
        using (SqlCommand cmd = new SqlCommand(sb.ToString(), connn))
        {
            connn.Open();
            //      cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            connn.Close();
        }
        // Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);
    }
}

EDIT - I've built a test database and debugged the following code. It works for the test data I passed it.

    private void InsertRecords(StringCollection sc)
    {
        const string sqlStatement = "INSERT INTO Ingredients_List (Ingredients1) VALUES";
        StringBuilder sb = new StringBuilder();

        foreach (string item in sc)
        {
            if (item.Contains(","))
            {
                var splitItems = item.Split(",".ToCharArray());
                sb.AppendFormat("{0}('{1}'); ", sqlStatement, splitItems[0]);
            }

        }
        Console.WriteLine(sb.ToString());
        //  conn.Open();
        using (SqlConnection connn = new SqlConnection(ConfigurationManager.ConnectionStrings["whatever"].ConnectionString))

        {
            using (SqlCommand cmd = new SqlCommand(sb.ToString(), connn))
            {
                connn.Open();
                cmd.ExecuteNonQuery();
                connn.Close();
            }
            // Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);
        }
    }

I tested it as follows:

...
var collection = new StringCollection{"a,x", "b,y", "c,z"};

InsertRecords(collection);

Execution of the database query to see data is as follows:

SELECT [Ingredients1]
  FROM [Ingredients_List]

Results:

Ingredients1
------------
a
b
c