CODEWITHSUNDEEP
c#sqloracle-databasewinforms
Kim Somers
Kim Somers

Reputation: 123

How to insert text box values from C# into Oracle Database

Okay I'm new to all of this and am struggling to insert TextBox values (user input) in Windows Form Application and storing these into an Oracle Database Table.

C# code:

string oradb = "DATA SOURCE = larry.uopnet.plymouth.ac.uk:1521/orcl.fost.plymouth.ac.uk;PERSIST SECURITY INFO = True;USER ID = xxxxxxxxxx;password = xxxxxxxxx";

OracleConnection con = new OracleConnection(oradb);
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "Insert into USER.Client VALUES (txtBoxClientName, txtBoxClientCity, txtBoxClientCountry, txtBoxClientNumber, txtBoxClientURL, comboClientStatus)";

int rowsUpdated = cmd.ExecuteNonQuery();
if (rowsUpdated == 0)
    MessageBox.Show("Please fill in required fields");
else
    MessageBox.Show("Client has been added");
con.Dispose();

This error appears at the int rowsUpdated = cmd.ExecuteNonQuery(); line - An unhandled exception of type Oracle.DataAccess.Client.OracleException occurred in Oracle.DataAccess.dll

Trigger code in Oracle:

create or replace TRIGGER trg_client_ClientID
BEFORE INSERT ON client FOR EACH ROW
BEGIN
  :NEW.ClientID := seq_client_ClientID.nextval;
END;

Upvotes: 1

Views: 4266

Answers (2)

Kim Somers
Kim Somers

Reputation: 123

I've managed to fix it with the help of adding in the OracleException and ArgumentException and by using the Parameter code from Elkhan - here is my altered and working code:

    private void btnClientSave_Click(object sender, EventArgs e)
    {

        string oradb = "DATA SOURCE = larry.uopnet.plymouth.ac.uk:1521/orcl.fost.plymouth.ac.uk;PERSIST SECURITY INFO = True;USER ID = xxxxxxxxx;password = xxxxxxxx";
        string insertquery = "Insert into Client VALUES (:1, :2, :3, :4, :5, :6, :7)";


        OracleConnection con = new OracleConnection(oradb);
        con.Open();
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = con;
        cmd.CommandText = insertquery;
        try
        {

            cmd.Parameters.Add(new OracleParameter("1", OracleDbType.Decimal, ParameterDirection.ReturnValue));
            cmd.Parameters.Add(new OracleParameter("2", OracleDbType.Varchar2, txtBoxClientName.Text, ParameterDirection.Input));
            cmd.Parameters.Add(new OracleParameter("3", OracleDbType.Varchar2, txtBoxClientCity.Text, ParameterDirection.Input));
            cmd.Parameters.Add(new OracleParameter("4", OracleDbType.Varchar2, txtBoxClientCountry.Text, ParameterDirection.Input));
            cmd.Parameters.Add(new OracleParameter("5", OracleDbType.Varchar2, txtBoxClientNumber.Text, ParameterDirection.Input));
            cmd.Parameters.Add(new OracleParameter("6", OracleDbType.Varchar2, txtBoxClientURL.Text, ParameterDirection.Input));
            cmd.Parameters.Add(new OracleParameter("7", OracleDbType.Varchar2, comboClientStatus.Text, ParameterDirection.Input));

            cmd.ExecuteNonQuery();

            MessageBox.Show("Client has been added");
            con.Close();
            Close();
        }
        catch (OracleException e1)
        {
            MessageBox.Show("Error: " + e1.Message);
        }
        catch (ArgumentException e2)
        {
            MessageBox.Show("Error: " + e2.Message);
        }
        finally
        {                
            cmd.Dispose();
            con.Dispose();
        }

Upvotes: 3

Elkhan Ibrahimov
Elkhan Ibrahimov

Reputation: 234

like that:

    cmd.CommandText = "Insert into USER.Client VALUES (:param_txtBoxClientName, :param_txtBoxClientCity, :param_txtBoxClientCountry, :param_txtBoxClientNumber, :param_txtBoxClientURL, :param_comboClientStatus)";

    cmd.Parameters.Add(new OracleParameter("param_txtBoxClientName", OracleDbType.Varchar2, txtBoxClientName.Text, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("param_txtBoxClientCity", OracleDbType.Varchar2, txtBoxClientCity.Text, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("param_txtBoxClientNumber", OracleDbType.Varchar2, txtBoxClientNumber.Text, ParameterDirection.Input));
    cmd.Parameters.Add(new OracleParameter("param_txtBoxClientURL", OracleDbType.Varchar2, txtBoxClientURL.Text, ParameterDirection.Input));

Upvotes: 0

Related Questions