Click here to Skip to main content
15,559,949 members
Please Sign up or sign in to vote.
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
Updated 12-Jan-21 12:02pm
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

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.
 
Share this answer
 
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[^]
 
Share this answer
 
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.

:)
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900