Inserting bool into SQL database, c#

Question

I am making a C# client-server application for my college class. I have an MS Access database with a table Package, it has some values like 'name', 'dateFrom', 'dateTo', and 2 values called 'internet' and 'telephone' which are YES/NO type

In the program I made a class Package and set internet and telephone as bool type of data. Firstly, is this correct? If value is set as YES/NO in MS Access, am I supposed to set it as bool in C#? this seems logical to me..

Secondly, when I enter SQL command for inserting these values I get an error, I don't know how to send bool values to database

Here is my code

command.CommandText = "Insert into Package values ("+p.PackageID+", '"+ p.Name+"', '"+p.DateFrom.ToString()+"', '"+p.DateTo.ToString()+"', "+Convert.ToBoolean(p.Internet.ToString())+", "+Convert.ToBoolean(p.Telephone.ToString())+")";
command.ExecuteNonQuery();

I tried a lot of things, and this was my last attempt of converting values to be able to insert them.. but it doesn't work.. I always get System.InvalidOperationException

Please do not use string concatenation for creating SQL commands. Use parameterized statements to pass values in. Your program is at risk for SQL Injection Attacks. — gunr2171
– gunr2171, Commented Nov 18, 2019 at 15:21
Even if you are just learning, do not shortcut on parameters. You should learn the correct way to do it and avoid the bad habit all together. Check out - [dotnetperls.com/sqlparameter] Welcome to stackoverflow! — tomg
– tomg, Commented Nov 18, 2019 at 15:31
What is the value of "p.Internet.ToString()" and "p.Telephone.ToString()"? Is it Yes/No or True/False? — Azhar Khorasany
– Azhar Khorasany, Commented Nov 18, 2019 at 15:45

Martin · Accepted Answer · 2019-11-18 15:33:15Z

3

For various reasons, you SHOULD NOT USE string concatenation on SQL statements. Use parameters. Try like:

command.CommandText = "Insert into Package values (@param1,@param2@param3,@param4,@param5,@param6)";
command.Parameters.Add("@param1",  OleDbType.Integer).Value = p.PackageID;
command.Parameters.Add("@param2",  OleDbType.VarWChar).Value = p.Name;
command.Parameters.Add("@param3",  OleDbType.Date).Value = p.DateFrom;
command.Parameters.Add("@param4",  OleDbType.Date).Value = p.DateTo;
command.Parameters.Add("@param5",  OleDbType.Boolean).Value = bool.Parse(p.Internet.ToString());
command.Parameters.Add("@param6",  OleDbType.Boolean).Value = bool.Parse(p.Telephone.ToString());
command.ExecuteNonQuery();

edited Nov 18, 2019 at 15:33

Martin

16.5k1 gold badge33 silver badges51 bronze badges

answered Nov 18, 2019 at 15:27

apomene

14.4k10 gold badges51 silver badges76 bronze badges

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

1 Comment

tomg Over a year ago

May need to add tryparse, unless you know the data is clean?

Collectives™ on Stack Overflow

Inserting bool into SQL database, c#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related