Data type mismatch im using windows form in visual studio

Question

1.00/5 (1 vote)

See more:

>string txtQuery = "INSERT INTO Client (ID,fName,lName,address,contact,age,gender) Values('"+txtID.Text+"','"+txtFname.Text+"', '"+txtLname.Text+"','"+txtAddress.Text+"','"+txtContact.Text+"',+'"+txtAge.Text+"','"+cbGender.Text+"')"; 
```
cannot find the error in this line of code
```


      private void setConnection()
        {
            sql_con = new SQLiteConnection("Data Source = carRentalDB.db;Version = 3; New = False; Compress = True;");

        }

        private void LoadData()
        {
            setConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            string CommandText = "SELECT * FROM Client";
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            DS.Reset();
            DB.Fill(DS);
            DT = DS.Tables[0];
            UserControl2 user = new UserControl2();
            user.dgvUser2.DataSource = DT;
            sql_con.Close();

        }


        private void ExecuteQuery(String txtQuery)
        {
            setConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery; 
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }


        private void addClient_Load(object sender, EventArgs e)
        {
            LoadData();
        }


```


    CREATE TABLE "Client" (
    	"ID"	INTEGER NOT NULL UNIQUE,
    	"fName"	TEXT NOT NULL,
    	"lName"	TEXT NOT NULL,
    	"address"	TEXT NOT NULL,
    	"contact"	INTEGER NOT NULL,
    	"age"	INTEGER NOT NULL,
    	"gender"	TEXT NOT NULL,
    	PRIMARY KEY("ID" AUTOINCREMENT)
    )

What I have tried:

still looking for typo because of the error im my data type in my DB

Posted 2-Mar-22 4:02am

Ernest Jan Sandoval

Updated 2-Mar-22 5:09am

Add a Solution

2 solutions

Solution 2

In addition to solution #1 by Richard MacCutchan, i'll show you how to properly insert data into SqLite database:

C#

//create command
using var cmd = new SQLiteCommand(con);
//define insert statement - using parameters
cmd.CommandText = "INSERT INTO cars(name, price) VALUES(@name, @price)";
//add parameters with values
cmd.Parameters.AddWithValue("@name", "BMW");
cmd.Parameters.AddWithValue("@price", 36600);
cmd.Prepare();
//go!
cmd.ExecuteNonQuery();

In very similar way, you have to delete and select data.

Posted 2-Mar-22 5:09am

Maciej Los

Comments

Richard MacCutchan 2-Mar-22 11:33am

+5

Maciej Los 2-Mar-22 13:42pm

Thank you, Richard.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Accepted Answer · 2022-03-02T04:15:00

Solution 1

You have a + character inside the single quotes just before txtAge.Text. But more importantly you are leaving yourself vulnerable to SQL injection: see bobby-tables.com: A guide to preventing SQL injection[^]. You should always use parameterised queries for SQL, and also actually validate the answers before you add them to the database. As it is your users can type any garbage in those fields and it will all be inserted. And finally, storing a person's age in a database is totally pointless; it could be their birthday tomorrow so the value will be wrong.

Posted 2-Mar-22 4:15am

Richard MacCutchan

Comments

Ernest Jan Sandoval 2-Mar-22 10:22am

string txtQuery = "INSERT INTO Client (ID,fName,lName,address,contact,age,gender) Values('" + txtID.Text + "','" + txtFname.Text + "', '" + txtLname.Text + "','" + txtAddress.Text + "','" + txtContact.Text + "','" + dtpClient.Value.ToShortDateString() + "','" + cbGender.Text+"')"; i change the age to dateTimePicker.still data type mismatch

Richard MacCutchan 2-Mar-22 10:37am

Because you are trying to store text strings into fields that are not text types. And you should store Dates as Date or DateTime types, not as strings. And, as I said above, you should not be doing it like this in the first place; you should be using proper parameterised queries.

Ernest Jan Sandoval 2-Mar-22 10:23am

CREATE TABLE "Client" (
"ID" INTEGER NOT NULL UNIQUE,
"fName" TEXT NOT NULL,
"lName" TEXT NOT NULL,
"address" TEXT NOT NULL,
"contact" INTEGER NOT NULL,
"age" INTEGER NOT NULL,
"gender" TEXT NOT NULL,
PRIMARY KEY("ID" AUTOINCREMENT)
)

Maciej Los 2-Mar-22 10:58am

5ed!