How can I use C# parameters to insert into a SQL Database? [duplicate]

Question

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

Catch the exception and look at what it says. Your SQL is wrong. Looks like you're missing a parenthesis around the column list. — user47589
– user47589, Commented May 22, 2019 at 14:55
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
– Scott Hannen, Commented May 22, 2019 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
– Jeroen Mostert, Commented May 22, 2019 at 14:56
Just to add something, you should close the conn as soon as you finished using it — nalnpir
– nalnpir, Commented May 22, 2019 at 14:58
This could probably be closed as a typographical error. They're missing the closing parenthesis around their column list. — user47589
– user47589, Commented May 22, 2019 at 14:58

apomene · Accepted Answer · 2019-05-22 14:56:48Z

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
}

answered May 22, 2019 at 14:56

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

apomene Over a year ago

@Igor ToString() call ensures that all the Stack Trace is logged

Igor Over a year ago

not sure why but I thought you had .Message instead of ToString, comment withdrawn.

Ordinary Guy Over a year ago

Thank you so much... stupid oversight on my part

Ordinary Guy Over a year ago

Thank you so much Amy... really appreciate it,

Collectives™ on Stack Overflow

How can I use C# parameters to insert into a SQL Database? [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related