What query syntax should I use to insert records into an Oracle database?
I am starting out and trying to create a simple C # program to insert and update records in an Oracle database. I was able to successfully connect to the database, but I am getting an exception for my SQL statement that claims the character is (?)
not supported. Why am I getting this exception and how can I fix it?
My code:
private void btnSave_Click(object sender, EventArgs e)
{
OracleConnection con = null;
try
{
con = new OracleConnection();
string constr = "Data source=XE; User ID=cloudester; Password=cloudester123;";
if (con.State != ConnectionState.Open)
{
try
{
con.ConnectionString = constr;
con.Open();
//MessageBox.Show("Successfull connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception caught");
}
}
if (con.State == ConnectionState.Open)
{
string str = "Insert into EMP_DETAIL(EmpId, Name, Age)";
str += "values (?,?,?)";
OracleCommand cmd = new OracleCommand();
cmd.CommandText = Text;
cmd.Connection = con;
cmd.Parameters.Add(new OracleParameter("EmpId", OracleDbType.Varchar2)).Value = txtEmpId;
cmd.Parameters.Add(new OracleParameter("Name", OracleDbType.Varchar2)).Value = txtName;
cmd.Parameters.Add(new OracleParameter("Age", OracleDbType.Int16)).Value = int.Parse(txtAge.Text);
cmd.ExecuteNonQuery();
}
}
catch { ... }
}
source to share
You need to use a named parameter for your command
string str = "Insert into EMP_DETAIL(EmpId, Name, Age) values (:EmpId, :Name, :Age)";
OracleCommand cmd = new OracleCommand();
cmd.CommandText = str; //cmd.CommandText = Text; not sure why did you use Text here
cmd.Connection = con;
cmd.Parameters.Add(new OracleParameter("EmpId", OracleDbType.Varchar2)).Value = txtEmpId;
cmd.Parameters.Add(new OracleParameter("Name", OracleDbType.Varchar2)).Value = txtName;
cmd.Parameters.Add(new OracleParameter("Age", OracleDbType.Int16)).Value = int.Parse(txtAge.Text);
cmd.ExecuteNonQuery();
source to share
As agent5566 said , and of the OracleCommand.Parameters
property ;
When using named parameters in an SQL statement called
OracleCommand
ofCommandType.Text
, you must precede the parameter name with a colon (:
)
Use them like:
using(var con = new OracleConnection(constr))
using(var cmd = con.CreateCommand())
{
cmd.CommandText = @"Insert into EMP_DETAIL(EmpId, Name, Age)
values (:EmpId, :Name, :Age)";
cmd.Parameters.Add(new OracleParameter("EmpId", OracleDbType.Varchar2)).Value = txtEmpId;
cmd.Parameters.Add(new OracleParameter("Name", OracleDbType.Varchar2)).Value = txtName;
cmd.Parameters.Add(new OracleParameter("Age", OracleDbType.Int16)).Value = int.Parse(txtAge.Text);
con.Open();
cmd.ExecuteNonQuery();
}
BTW, it System.Data.OracleClient
was deprecated in .NET 4. You can use the Oracle Data Provider for .NET instead .
DataDirect and DevArt also have their own oracle providers for .NET as an alternative .
source to share