-1

I am having trouble using parameters in C# to insert into a SQL Database. My connection is already working

I have tried the Add, AddWithValue

static void parameter_insert(SqlConnection conn, int number)
{
    try
    {
        conn.Open();                
        SqlCommand command = new SqlCommand(@"INSERT INTO test1.dbo.shopping_list(NAME, QUANTITY, QUALITY, FOOD_GROUP, FOODS VALUES('Bread', @Quantity, 'Poor', 'Carbs', 'Good')", conn);
        command.Parameters["@Quantity"].Value = 3;
        command.ExecuteNonQuery();
        Console.WriteLine("Parameter insertion successful.");
    }
    catch
    {
        Console.WriteLine("Parameter insertion failed.");
    }
}

The output gives me the insertion failed

Igor
  • 60,821
  • 10
  • 100
  • 175
Ordinary Guy
  • 13
  • 2
  • 5
    Catch the exception and look at what it says. Your SQL is wrong. Looks like you're missing a parenthesis around the column list. –  May 22 '19 at 14:55
  • 5
    What does the exception say? That would be the first step toward finding the problem. Programming would be much more difficult if all we knew was that something went wrong, and we had to figure it out by either reading the code and guessing or trial-and-error changes. – Scott Hannen May 22 '19 at 14:55
  • Remove the `catch` with its uninformative error message. You should get a syntax error that actually tells you what the problem is. (Or first an `IndexOutOfRangeException` for the parameter you did not add to the collection.) Proper exception handling would at the very least involve something like `catch (SqlException e) { Console.WriteLine("Insertion failed: " + e.Message); }`. – Jeroen Mostert May 22 '19 at 14:56
  • Just to add something, you should close the conn as soon as you finished using it – nalnpir May 22 '19 at 14:58
  • 3
    This could probably be closed as a typographical error. They're missing the closing parenthesis around their column list. –  May 22 '19 at 14:58
  • better to wrap with `using`, no matter success or fail, the connection will be closed – LONG May 22 '19 at 15:00

1 Answers1

3

You are missing a closing parenthesis on your insert query statement:

"INSERT INTO test1.dbo.shopping_list(NAME, QUANTITY, QUALITY, FOOD_GROUP, FOODS)"+ //<--missing clossing parenthesis
" VALUES('Bread', @Quantity, 'Poor', 'Carbs', 'Good')";

In any case and as a general rule, you should better log the actual exception message on a catch block instead of swallowing it with a custom message.

catch (Exception ex)
{
   Log(ex.ToString());
   ....//Inform user
}
apomene
  • 14,282
  • 9
  • 46
  • 72