Inserting data into Oracle database using c#

Question

I get this error "ora-00928 missing select keyword" when using a button to submit the query. I have other queries on other buttons and the select statements work but for some reason the insert statement doesnt work. I've seen other posts on this error but nothing seems to help mine

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace Oracle
{
    public partial class Register : Form
    {
        string name;
        int pass;
        int repass;
        string email;
        public Register()
        {
            InitializeComponent();
        }
        OleDbConnection con = new OleDbConnection("Provider=MSDAORA;Data Source=DESKTOP-HQCK6F1:1521/CTECH;Persist Security Info=True;User ID=system;Password=G4ming404;Unicode=True");
        OleDbCommand cmd = new OleDbCommand();

        private void button1_Click(object sender, EventArgs e)
        {
            name = txtname.Text;
            pass = Convert.ToInt32(txtpass.Text);
            repass = Convert.ToInt32(txtrepass.Text);
            email = txtemail.Text;
            cmd.Connection = con;
            cmd.CommandText = "INSERT INTO SYSTEM.CUSTOMER('CUSTOMER_ID', 'CUSTOMER_NAME', 'CUSTOMER_EMAIL', 'CUSTOMER_PASSWORD')" + "VALUES('%"+ null + "%','%'" + txtname.Text + "%','%'" + txtemail.Text + "%','%'" + txtpass.Text + "%')";
            con.Open();

            if (pass == repass)
            {
                int rowsUpdated = cmd.ExecuteNonQuery();
                if (rowsUpdated == 0)
                {
                    MessageBox.Show("Record not inserted");
                }
                else {
                    MessageBox.Show("Success!");
                   }

                MessageBox.Show("User has been created");
                this.Close();
                Form1 login = new Form1();
                login.Show();

            }
            else {
                MessageBox.Show("Password mismatch");
            }
            con.Dispose();
        }

score 1 · Accepted Answer · edited May 23 '17 at 12:32

There are some problems in your query.
First you don't need single quotes around the column names, You need double quotes only if any of your columns has the same name as a reserved keyword.

Second problem is the string concatenation of the input boxes text to the query command. This should be avoided at all cost because it is the source of parsing problems and sql injection hacks. Use parameters instead.

Finally your OleDbConnection should be local to your method and inside a using statement to ensure correct disposing of the unmanaged resources also in case of exceptions

private void button1_Click(object sender, EventArgs e)
{
    name = txtname.Text;
    pass = Convert.ToInt32(txtpass.Text);
    repass = Convert.ToInt32(txtrepass.Text);
    email = txtemail.Text;

    if (pass != repass)
    {
        MessageBox.Show("Password mismatch");
        return;
    }
    string cmdText = @"INSERT INTO SYSTEM.CUSTOMER 
                       (CUSTOMER_NAME, CUSTOMER_EMAIL, CUSTOMER_PASSWORD) 
                       VALUES(?,?,?)";
    using(OleDbConnection con = new OleDbConnection(.......))
    using(OleDbCommand cmd = new OleDbCommand(cmdText, con))
    {
         con.Open();
         cmd.Parameters.Add("p1", OleDbType.VarChar).Value = txtname.Text;
         cmd.Parameters.Add("p2", OleDbType.VarChar).Value = txtemail.Text;
         cmd.Parameters.Add("p3", OleDbType.VarChar).Value = txtpass.Text ;
         int rowsUpdated = cmd.ExecuteNonQuery();
         if (rowsUpdated == 0)
         {
             MessageBox.Show("Record not inserted");
         }
         else 
         {
            MessageBox.Show("Success!");
            MessageBox.Show("User has been created");
         }
    }
    Form1 login = new Form1();
    login.Show();
}

I have also removed the passing of a parameter for the CUSTOMER_ID field. This seems to be a field that is calculated automatically by Oracle (a Sequence?) and thus you don't need to provide a value for it.

Finally an advice. Do not store password in plain text in the database. This is a security risk very seriours. You should read Best way to store passwords in a database

Thank you very much that helped alot. One other thing. I get a new error: ORA-01400: cannot insert NULL into ("SYSTEM"."CUSTOMER"."CUSTOMER_ID"). I really new to this. Any thoughts on how to fix this? — Gerrit de Beer, Sep 05 '16 at 21:26
Then you need to provide a value for the CUSTOMER_ID column. But it is unlikely that you know which value to set for the customer_id field and neither you should try do do it. Usually this is a job for your database to provide an unique number to identify the record. [This article](http://www.orafaq.com/wiki/AutoNumber_and_Identity_columns) helps in understanding what kind of changes are required to your CUSTOMER_ID field to auto fill itself — Steve, Sep 05 '16 at 21:52

score 0 · Answer 2 · answered Sep 05 '16 at 20:56

Your CommandText seems wrong. Why do you wrap all values with '%'? And you should pass null as string. Concatination with null does not changes any string value.

I think it should be:

cmd.CommandText = $@"INSERT INTO SYSTEM.CUSTOMER
('CUSTOMER_ID', 'CUSTOMER_NAME', 'CUSTOMER_EMAIL', 'CUSTOMER_PASSWORD')
VALUES(NULL, {txtname.Text}, {txtemail.Text}, {txtpass.Text})";

Inserting data into Oracle database using c#

2 Answers2

Linked