Need help on how to if record exists update else insert c#
-
I'm trying to do an if record exists update statement else insert statement and can't get past the line:
OracleDataReader reader = check_RID.ExecuteReader();
Keep getting ora-00096 missing expression error:
OracleConnection con = new OracleConnection(strConnection);
con.Open();
OracleCommand check_RID = new OracleCommand("SELECT COUNT(*) FROM CONTRACT_INFO WHERE (rid = @rid)", con);
check_RID.Parameters.Add("@rid", labelRID.Text);
OracleDataReader reader = check_RID.ExecuteReader();if (reader.HasRows) { OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = strConnection; conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "UPDATE... cmd.ExecuteNonQuery(); conn.Close(); Response.Redirect("primecontractor.aspx?Id=" + labelRID.Text); } else { OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = strConnection; conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "INSERT.... ...... cmd.ExecuteNonQuery(); conn.Close(); -
I'm trying to do an if record exists update statement else insert statement and can't get past the line:
OracleDataReader reader = check_RID.ExecuteReader();
Keep getting ora-00096 missing expression error:
OracleConnection con = new OracleConnection(strConnection);
con.Open();
OracleCommand check_RID = new OracleCommand("SELECT COUNT(*) FROM CONTRACT_INFO WHERE (rid = @rid)", con);
check_RID.Parameters.Add("@rid", labelRID.Text);
OracleDataReader reader = check_RID.ExecuteReader();if (reader.HasRows) { OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = strConnection; conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "UPDATE... cmd.ExecuteNonQuery(); conn.Close(); Response.Redirect("primecontractor.aspx?Id=" + labelRID.Text); } else { OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = strConnection; conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "INSERT.... ...... cmd.ExecuteNonQuery(); conn.Close();From MSDN: When using named parameters in an SQL statement called by an OracleCommand of CommandType.Text, you must precede the parameter name with a colon . However, in a stored procedure, or when referring to a named parameter elsewhere in your code (for example, when adding OracleParameter objects to the Parameters property), do not precede the named parameter with a colon. The .NET Framework Data Provider for Oracle supplies the colon automatically. Also rather than execute a reader why not use scalar as COUNT returns a single value test it non-zero.
-
I'm trying to do an if record exists update statement else insert statement and can't get past the line:
OracleDataReader reader = check_RID.ExecuteReader();
Keep getting ora-00096 missing expression error:
OracleConnection con = new OracleConnection(strConnection);
con.Open();
OracleCommand check_RID = new OracleCommand("SELECT COUNT(*) FROM CONTRACT_INFO WHERE (rid = @rid)", con);
check_RID.Parameters.Add("@rid", labelRID.Text);
OracleDataReader reader = check_RID.ExecuteReader();if (reader.HasRows) { OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = strConnection; conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "UPDATE... cmd.ExecuteNonQuery(); conn.Close(); Response.Redirect("primecontractor.aspx?Id=" + labelRID.Text); } else { OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = strConnection; conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "INSERT.... ...... cmd.ExecuteNonQuery(); conn.Close();AFAIK, the Oracle command uses a
:as the parameter prefix, not a@.OracleCommand check_RID = new OracleCommand("SELECT COUNT(*) FROM CONTRACT_INFO WHERE (rid = :rid)", con);
check_RID.Parameters.Add("rid", labelRID.Text);
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I'm trying to do an if record exists update statement else insert statement and can't get past the line:
OracleDataReader reader = check_RID.ExecuteReader();
Keep getting ora-00096 missing expression error:
OracleConnection con = new OracleConnection(strConnection);
con.Open();
OracleCommand check_RID = new OracleCommand("SELECT COUNT(*) FROM CONTRACT_INFO WHERE (rid = @rid)", con);
check_RID.Parameters.Add("@rid", labelRID.Text);
OracleDataReader reader = check_RID.ExecuteReader();if (reader.HasRows) { OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = strConnection; conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "UPDATE... cmd.ExecuteNonQuery(); conn.Close(); Response.Redirect("primecontractor.aspx?Id=" + labelRID.Text); } else { OracleConnection conn = new OracleConnection(); // C# conn.ConnectionString = strConnection; conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "INSERT.... ...... cmd.ExecuteNonQuery(); conn.Close();Check this Microsoft SQL Server and Oracle Compared[^] Will help you to understand the differences between Oracle and SQL Server.
