Solved

C#, Oracle .NET Update Datefield

Posted on 2009-05-04
2
851 Views
Last Modified: 2012-05-06
Hello experts,

I am trying update one of my oracle table. one field is a datatime. How do I do that?

PPeriodStart - it is datetime
PPeriodEnd - it is datetime


                    OracleTransaction myOracleTransaction = Program.g_DBO.cnDB.BeginTransaction(); ;
                    OracleCommand oraCmd = new OracleCommand();
                    try
                    {
                        sqlText = "UPDATE pc.pcy set " +
                                " PAC = " + PcyAss + ", " +
                                " PPeriodStart = '" + To_date(PPeriodStart, "yyyymmdd") + "', " +
                                " PPeriodEnd = '" + To_date(PPeriodEnd, "yyyymmdd") + "'
                                " WHERE CoverageID = " + CoverageID + "";
                        oraCmd.Connection = Program.g_DBO.cnDB;
                        oraCmd.CommandType = CommandType.Text;
                        oraCmd.CommandText = sqlText;
                        oraCmd.ExecuteNonQuery();
                        myOracleTransaction.Commit();
                        return true;
0
Comment
Question by:listings_
2 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
Comment Utility
You can use bind variables, and add parameters via OracleCommand.Parameters.Add

Somethign like this (untested), I don't have my VS up with a project to test.

Also read:


http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleparameter.aspx

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28844/building_odp.htm#CEGCGDAB

http://download.oracle.com/docs/html/B28089_01/OracleParameterClass.htm#i1010881

                    OracleTransaction myOracleTransaction = Program.g_DBO.cnDB.BeginTransaction(); ;

                    OracleCommand oraCmd = new OracleCommand();

                    try

                    {

                        sqlText = "UPDATE pc.pcy set PAC = " + PcyAss + ", " +  // I would also convert PcyAss to a bind parameter

                                " PPeriodStart = :PPeriodStart, " +

                                " PPeriodEnd = :PPeriodEnd " +

                                " WHERE CoverageID = :CoverageID ";

                        oraCmd.Connection = Program.g_DBO.cnDB;

                        oraCmd.CommandType = CommandType.Text;

                        oraCmd.CommandText = sqlText;

                        oraCmd.Parameters.Add("PPeriodStart", OracleType.Date, PPeriodStart, ParameterDirection.Input);

                        oraCmd.Parameters.Add("PPeriodEnd", OracleType.Date, PPeriodEnd, ParameterDirection.Input);

                        oraCmd.Parameters.Add("CoverageID", OracleType.Int, CoverageID, ParameterDirection.Input);

                        oraCmd.ExecuteNonQuery();

                        myOracleTransaction.Commit();

                        return true;

Open in new window

0
 

Author Comment

by:listings_
Comment Utility
Thanks mrjoltcola.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
one-way data "masking" MD5 sql 26 95
Encryption Decryption in Oracle 12 59
Oracle Next Available Number 2 11
COnsume rest client 6 8
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now