How to insert bool into database

Question

I have to add user data into database table named "employees". It has ID, Name, LastName,UserName, Password, E-mail, address, administrator_rights options.

Administator_rigts is bool option (yes or no).

How do i do that, when i make a form that has all the data, and i want to check if user has administrator rights by checkbox (checked - true , unchecked - false).

This is my statement which doesn't include admin_rights because i dunno how to do it.

Please help me out

string write = "Insert into Employees (ID, Name, Last_name, Username, Password, E_mail, Address) values('" + this.ID.Text + "','" + this.name.Text + "','" + this.lastName.Text + "','" + this.userName.Text + "','" + this.password.Text + "','" + this.eMail.Text + "','" + this.address.Text + "');";

Thank you all for your responses

Your code is extremely vulnerable to [SQL injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. Do yourself (and your users) a favor, and use prepared statements. — p.s.w.g, Jan 12 '14 at 10:00
What database? SQL Server doesn't have a `bool` type. It does have a bit type though, which values can be 0 or 1. — Andrew Barber, Jan 12 '14 at 10:00
Beside what @p.s.w.g said, storing `this.password.Text` like that in the database is probably not a good idea. See [this question](http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database), [that question](http://stackoverflow.com/questions/15742123/how-to-store-passwords-in-database-securely) and also [this article](http://www.codinghorror.com/blog/2007/09/youre-probably-storing-passwords-incorrectly.html). — O. R. Mapper, Jan 12 '14 at 10:02
@p.s.w.g i will work on that. - AndrewBarber , Access Database. - O.R.Mapper, well it's only homework so it doesn't really matter. But thanks for the tips and future references — fkr, Jan 12 '14 at 10:04
For more information, watch [Hacking Websites with SQL Injection](http://youtu.be/_jKylhJtPmI) and [How NOT to Store Passwords!](http://youtu.be/8ZtInClXe1Q) for an introductory discussion of these problems. — p.s.w.g, Jan 12 '14 at 10:07
@user3186213 It **DOES** really matter, even thought it's *just* a homework! Bad production code *arises* from bad habbits gained from poorly done homeworks. What you learn *now*, you will follow through your career. — Ondrej Tucny, Jan 12 '14 at 10:24
Thank you **all** very much for your replies. This helped me a lot. And thanks for the parameters suggestion. It will surely improve my knowledge. Thanks again. — fkr, Jan 12 '14 at 12:20

score 2 · Answer 1 · answered Jan 12 '14 at 10:21

I used access few years ago but as I can remember boolean value in access was as below:

0 = false
-1 = true

So for insert to by your check box you may use like this:

int val;
    if(CheckBox.Checked == true)
{
val = -1;
}
else
{
val =0
}

then you should add it to your query:

string write = "Insert into Employees (ID, Name, Last_name, Username, Password, E_mail, Address) values('" + this.ID.Text + "','" + this.name.Text + "','" + this.lastName.Text + "','" + this.userName.Text + "','" + this.password.Text + "','" + this.eMail.Text + "','" + this.address.Text + "'"+val");";

score 2 · Answer 2 · answered Jan 12 '14 at 10:36

Using Parameters is better for me , although i don't prefer ADO.NET :

SqlCommand Command = new SqlCommand();
            Command.Connection = MyConnection;
            Command.CommandText = "Insert into Employees (ID, Name, Last_name, Username, Password, E_mail, Address, administrator_rights)"
                              + "values(@ID, @Name, @Last_name, @Username, @Password, @E_mail,Address, @admin_rights )";

            Command.Parameters.Add("@ID", 1); // some generated number
            Command.Parameters.Add("@Name", TextBoxName.Text);
            Command.Parameters.Add("@Last_name", TextBoxLastName.Text);
            Command.Parameters.Add("@Username", TextBoxUserName.Text);
            Command.Parameters.Add("@Password", TextBoxPassword.Text);
            Command.Parameters.Add("@E_mail", TextBoxEmail.Text);
            Command.Parameters.Add("@Address", TextBoxAddress.Text);
            Command.Parameters.Add("@admin_rights", CheckBoxAdminRights.Checked);

            using (MyConnection)
            {
                Command.ExecuteNonQuery();
            }

score 1 · Answer 3 · answered Jan 12 '14 at 10:25

sql server 2008 r2 has a bit type which can b used in case of bool. sample table create script

/****** Object:  Table [dbo].[testTable]    Script Date: 01/12/2014 16:16:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[testTable](
    [test] [bit] NOT NULL,
    [testr] [int] NOT NULL
) ON [PRIMARY]

GO

Query to insert

INSERT INTO [dbo].[testTable]
           ([test]
           ,[testr])
     VALUES
           ('false','32')
GO

WHIle in c# Query sample:

string sqlQuery=@"Insert Into testTable(test,testr) VALUES("'+checkbox.checked+'","'+empId+'")";

You can't use string 'false' for a bit field in SQL Server. you have to use 1 or 0. — Orion, Nov 22 '17 at 11:45

dovid · Answer 4 · 2014-01-12T10:47:07.957

Use Parameters:

OleDbCommand command = new OleDbCommand("Insert into Employees (ID, Name, Last_name, Username, Password, E_mail, Address, administrator_rights) values(?, ?, ?, ?, ?, ?, ?, ?)",
                                        new OleDbConnection ("YourConnection"))
{
    Parameters = { new OleDbParameter("ID", OleDbType.Integer),
                   new OleDbParameter("Name", OleDbType.VarWChar ),
                   new OleDbParameter("Last_name", OleDbType.VarWChar),
                   new OleDbParameter("Username", OleDbType.VarWChar),
                   new OleDbParameter("Password", OleDbType.VarWChar),
                   new OleDbParameter("E_mail", OleDbType.VarWChar),
                   new OleDbParameter("Address", OleDbType.VarWChar),
                   new OleDbParameter("administrator_rights", OleDbType.Boolean )}
};

private bool Update()
{

    command.Parameters["ID"].Value = this.ID.Text;
    command.Parameters["Name"].Value = this.name.Text;
    command.Parameters["Last_name"].Value = this.lastName.Text;
    command.Parameters["Username"].Value = this.userName.Text;
    command.Parameters["Password"].Value = this.password.Text;
    command.Parameters["E_mail"].Value = this.eMail.Text;
    command.Parameters["Address"].Value = this.address.Text;
    command.Parameters["administrator_rights"].Value = checkBox1.Checked;

    if (command.Connection.State != ConnectionState.Open) command.Connection.Close();
    var result =  command.ExecuteNonQuery();
    command.Connection.Close();

    return result == 0 ? false : true;
}

How to insert bool into database

4 Answers4

Linked