I have a problem with my code I cant add things to my database

Question

1.00/5 (2 votes)

See more:

when I run the code and type the things I want to add into the database it gets me an error I hope someone can help

the error is in this

cmd = new OleDbCommand(@"INSERT INTO Teacher (ID ,Name ,Phone ,Gmail ,Address) VAlUS (" + Convert.ToInt32(textBox1.Text) + ",'" + textBox2.Text + "'," + Convert.ToInt32(textBox3.Text) + ",'" + textBox4.Text + ",'" + textBox5.Text + "')", con);
con.Open();
cmd.ExecuteNonQuery();

What I have tried:

  public partial class Form1 : Form
    {
        public OleDbCommand cmd;
        public Form1()
        {
            InitializeComponent();
        }
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Teacher.mdb");
        OleDbDataAdapter da = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        private void fillTable()
        {
            da = new OleDbDataAdapter("SElECT* FROM Teacher", con);
            dt.Rows.Clear();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            fillTable();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            cmd = new OleDbCommand(@"INSERT INTO Teacher (ID ,Name ,Phone ,Gmail ,Address) VAlUS (" + Convert.ToInt32(textBox1.Text) + ",'" + textBox2.Text + "'," + Convert.ToInt32(textBox3.Text) + ",'" + textBox4.Text + ",'" + textBox5.Text + "')", con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            fillTable();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}

Posted 12-Jan-21 4:28am

Member 15044093

Updated 12-Jan-21 11:02am

Add a Solution

Comments

Richard MacCutchan 12-Jan-21 11:05am

You forgot to tell us what the error is but at a gues it may be your misspelling: VAlUS instead of VALUES. But as OriginalGriff says, you should never use string concatenation in SQL. Nor should you use Convert.ToInt32(textBox1.Text), since any bad data there will crash your entire application.

Member 15044093 12-Jan-21 11:34am

I uploaded the whole thing to a google drive link I hope you can find the problem because Its a mini project our doctor asked us to do and we should finish it by today

Member 15044093 12-Jan-21 11:34am

https://drive.google.com/drive/folders/10fgMyAT_185uy_BcUQtQFNyfat_Kf2Pv?usp=sharing

Richard MacCutchan 12-Jan-21 11:39am

Sorry, both OriginalGriff and I have pointed out a number of your mistakes. It is up to you to correct them.

Member 15044093 12-Jan-21 11:46am

well I fixed values still same problem removed the whole convert thing still the problem remains and I am still new to programming so yeah I don't understand lots of things

3 solutions

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)

OriginalGriff · Answer 1 · 2021-01-12T04:47:00

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Once you fix that through your whole app, you may find your problem as gone as well. If it doesn't, come back and show us the revised code, and explain what error message you get, where and when you get it, and what the debugger showed you was going on.

Patrice T · Answer 2 · 2021-01-12T08:31:00

A few problems here:

C#

cmd = new OleDbCommand(@"INSERT INTO Teacher (ID ,Name ,Phone ,Gmail ,Address) VAlUS (" + Convert.ToInt32(textBox1.Text) + ",'" + textBox2.Text + "'," + Convert.ToInt32(textBox3.Text) + ",'" + textBox4.Text + ",'" + textBox5.Text + "')", con);

VAlUS should be VALUES as already told.
Convert.ToInt32(textBox1.Text) you are building a string, so conversion to int is an error.

Advice: Print the resulting SQL command and see what you really have as command, feed it to a beautifier/ parser on internet, to see what he think of the command.

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Luc Pattyn · Answer 3 · 2021-01-12T11:02:00

There are many mistakes in your SQL command. In fact your code is quite horrible; writing clean code would make most errors stand out much easier.

cmd = new OleDbCommand(@"INSERT INTO Teacher (ID ,Name ,Phone ,Gmail ,Address) VAlUS (" + Convert.ToInt32(textBox1.Text) + ",'" + textBox2.Text + "'," + Convert.ToInt32(textBox3.Text) + ",'" + textBox4.Text + ",'" + textBox5.Text + "')", con);

Some problems have already been mentioned by others, some haven't:
- typo in VALUES keyword;
- as cmd is a string, every part should be a string; e.g. textbox1.Text is converted to int and that is wrong; you could undo it by appending ToString() but that would be silly, the conversion is unnecessary, a sinple textbox1.Text should do (indeed, without single quotes as a numeric value is expected);
- assuming Phone is a typical phone number, it can't be a numeric field, as it might be quite long and leading zeroes would be significant, hence it must be stored as a string; Convert.ToInt32(textBox3.Text) makes no sense whatsoever;
- your quotes are all wrong, e.g. textBox4.Text gets a single quote prefix but no single quote suffix??

Here are some suggestions writing more defensively:
- don't write multiline statements if you can avoid them; the compiler doesn't mind, however the human brain has trouble seeing errors in them.
- choose your identifier names carefully, do not just use textbox1, textbox2, ...
- user inputs must always be validated; it makes no sense to create some code where any input error would result at best in a vague error message, something like "syntax error in SQL statement". Therefore, first validate all the inputs, and if errors occur report them, otherwise start the SQL stuff. This also applies to a short-lived program for a single user, make it a habit to always validate user input!
- when you use SQL parameters rather than string concatenation, you not only avoid SQL attacks, but you also automatically get more but simpler statements, and everything looks much simpler, allowing you to see your mistakes yourself.

:)

I have a problem with my code I cant add things to my database

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0