How to insert the checkbox "check" to my MS Access database

Question

Here is my database:

Here is my code

OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = "insert into StudentTable([StudentID], [Lastname], [Transferee]) values ('" +txtStudentID.Text+ "','" +txtLastname.Text +"',"' +chk.Transferee+ '")";
command.ExecuteNonQuery();

Error is data type mismatch criteria expression

How do I add the checkbox into my database check (not value of checkbox)?

Thanks

Answer 1

Some remarks on your code:

1. Typo in your code (late update)
2. C# boolean ≠ Transact-SQL bit.
3. You make SQL injection.
4. You could use IDENTITY for the StudentID column

Typo in your code

I've also seen in your code there was a typo at the end of your insert statement, you use this:

/*...*/ "',"' +chk.Transferee+ '")";

Instead of this:

/*...*/ "'," + chk.Transferee.IsChecked + ")";

xor this:

/*...*/ "','" + chk.Transferee.IsChecked + "')";

Anyway it is SQL injection and chk.Transferee.IsChecked is a C# boolean not a Transact-SQL bit. So we can go to the next heading.

C# boolean ≠ Transact-SQL Bit

If you run code below:

using System;

public class Program
{
    public static void Main()
    {
        bool? yes = true;
        Console.WriteLine("yes: {0}", yes);

        bool? no = false;
        Console.WriteLine("no: {0}", no);

        bool? nothing = null;
        Console.WriteLine("nothing: {0}", nothing);
    }
}

It will print this:

yes: True
no: False
nothing:

You can test it on this .NET fiddle.

Transact-SQL use a bit for "true" or "false". In Transact-SQL are this respectively a 1 and a 0. What you're willing to do with this code if the typo is fixed, is respectively this:

insert into StudentTable([StudentID], [Lastname], [Transferee]) 
values (7, 'Turner', True)

xor this:

insert into StudentTable([StudentID], [Lastname], [Transferee]) 
values (7, 'Turner', 'True')

This is not valid code for Transact-SQL. Because the boolean true and the string with value True is not the bit 1.

Aside: Nullable booleans (only if you're using WPF or will have to insert a null)

If you're using WPF is the IsChecked property is nullabele or if you will just insert a null. Your code will give is an exception. This will be your SQL query:

insert into StudentTable([StudentID], [Lastname], [Transferee]) 
values (7, 'Turner', )

xor this:

insert into StudentTable([StudentID], [Lastname], [Transferee]) 
values (7, 'Turner', '')

And this is of course not valid.

Correct statement

The correct statement must be this:

insert into StudentTable([StudentID], [Lastname], [Transferee]) 
values (7, 'Turner', 1)

Test this code in this SQLfiddle.

Preventing SQL injection

Wikipedia say about SQL injection:

SQL injection is a code injection technique, used to attack data-driven applications, in which nefarious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

Instead of your code you could using this:

command.CommandText = @"insert into StudentTable([StudentID], [Lastname], [Transferee]) 
                        values (@StudentID, @LastName,@Transferee)";

command.Parameters.AddWithValue("@StudentID", txtStudentID.Text);
command.Parameters.AddWithValue("@LastName", txtLastname.Text);
command.Parameters.AddWithValue("@Transferee", chk.Transferee.IsChecked);
// @ClintJoe: Will you check code of line above I think it's not correct.
// must it not to be this: `chk.IsChecked`? Not sure.

If you use code above and solve the remark I've added, all the problems of the first (typo in your code), second heading (C# boolean ≠ Transact-SQL bit) and the aside will be solved.

This will also prevent SQL injection. But, why no SQL injection? See this cartoon:

Identity

Hint: make also the column StudentID identity.

IDENTITY creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

After doing this you could use this code:

command.CommandText = @"insert into StudentTable([Lastname], [Transferee]) 
                        values (@LastName, @Transferee)";

command.Parameters.AddWithValue("@LastName", txtLastname.Text);
command.Parameters.AddWithValue("@Transferee", chk.Transferee.IsChecked);

And it's not needed anymore to ask for or create an unique ID in the application

Note

MS-access isn't a good database to use. By a quick search on Google I've found the major disadvantages of Access:

1. Windows and Office version dependant.
2. Access doesn’t have triggers and advanced functions.
3. Access VBA is an interpreted language. As such it is slow.
4. Access tools for performance analyzing and optimizing the database are non existent.
5. Access becomes terribly slow if you have more than 5–10 concurrent users even in a split (front/back end) database.
6. Access files are prone to corruption when they become too big (>100MB per mdb).
7. Even on a split database Access always computes everything client-side.

^{Source: What are the major disadvantages of Microsoft Access? - Quora}

Answer 2

I would suggest first change your query to a paramerized one, in order to avoid sql injections:

command.CommandText = "insert into StudentTable([StudentID], [Lastname], [Transferee]) " 
                    + " values (@StudentID, @LastName,@Transferee)";

Then add the parameters with their values:

command.Parameters.AddWithValue("@StudentID", txtStudentID.Text);
command.Parameters.AddWithValue("@LastName", txtLastname.Text);
command.Parameters.AddWithValue("@Transferee", chkTransferee.Checked);