Solved

C#, Oracle .NET Update Datefield

Posted on 2009-05-04
2
867 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24295629
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_
ID: 24297615
Thanks mrjoltcola.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

729 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