2 Replies - 764 Views - Last Post: 17 September 2012 - 08:39 AM Rate Topic: -----

#1 rjosori  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 10-May 09

Casting a C# String into a date type that matches Oracle's date Fo

Posted 16 September 2012 - 02:54 PM

I'm trying to write Oracle SQL queries executed via a C#/ASP.NET program to insert data into an Oracle Db. I'm having problems finding a C# method that would cast a string (coming from a form's user input in format 11/11/2012) to match Oracle's date data type. I tried to cast it via the Convert.ToDateTime(object) method (http://msdn.microsoft.com/en-us/library/ky38w316.aspx) without any success. any help would certainly be appreciated.

Here's my code

<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
    
   void Page_Load(Object sender, EventArgs e) {
       
       //Get user's input form form fields
       var saleID = Convert.ToInt32(Request.Form["saleID_aspx"]);
       var custID = Convert.ToInt32(Request.Form["custID_aspx"]);
       var agentID = Convert.ToInt32(Request.Form["agentID_aspx"]);
       var saleDate = Convert.ToDateTime("11/11/2012"); //Originally Request.Form["saleDate_aspx"]
       var homeID = Convert.ToInt32(Request.Form["homeID_aspx"]);
       var actualAmount = Convert.ToInt32(Request.Form["actualAmount_aspx"]);
       var contractID = Convert.ToInt32(Request.Form["contractID_aspx"]);
       var valueCommand = "VALUES(" + saleID + "," + custID + "," + agentID + "," + saleDate + "," + contractID + "," + homeID + "," + actualAmount + ")";
      // Declaration section
      OleDbConnection objDBConn;
      OleDbCommand    objCmd;
      OleDbCommand objCmdSelect;
      OleDbDataReader objDR;
      
      // Set up OLE DB Connection object
      objDBConn = new OleDbConnection("Provider=*****1;" +
                                      "User ID=********;" +
                                      "Password=*******;" +
                                      "Data Source=****");
       
     
      // Open DB connection
      objDBConn.Open();
      
      // Create OleDbCommand object with SQL to execute
      objCmd = new OleDbCommand("INSERT INTO Sale (saleID, cust_ID, agent_ID, saleDate, contractID, homeID, actualamount)" +
            valueCommand, objDBConn);


      // Create a DataReader and execute the command
      objDR = objCmd.ExecuteReader();

      // Copy results from DataReader to DataGrid object
      gridCusts.DataSource = objDR;
      gridCusts.DataBind();

      // Close all objects
      objDR.Close();
      objCmd.Dispose();
       
      ////////////////////////////////////////////////////////////
      // Create OleDbCommand object with SQL to execute
      objCmdSelect = new OleDbCommand("SELECT * " +
                                "  FROM Sale " +
                                " ORDER BY saleID", objDBConn);

      // Create a DataReader and execute the command
      objDR = objCmdSelect.ExecuteReader();

      // Copy results from DataReader to DataGrid object
      gridCusts.DataSource = objDR;
      gridCusts.DataBind();

      // Close all objects
      objDR.Close();
      objCmdSelect.Dispose();
      /////////////////////////////////////////////////////////// 
      objDBConn.Close();        
   }

</script>
<html>
<head>
<title>CUSTOMERS table</title>
<link href="bootstrap/css/bootstrap.css" type="text/css" rel="stylesheet">
</head>
<body>
    <div id="container">
        <h2>Oracle SALES table contents via C#.NET and OLE DB</h2>
        <div style="margin:0 auto text-align:center;">
            <asp:DataGrid id="gridCusts" class='table' runat="server" />
        </div>
        <a href="index.html" target="_self" class="btn">Go Back</a>
    </div>
</body>
</html>



Is This A Good Question/Topic? 0
  • +

Replies To: Casting a C# String into a date type that matches Oracle's date Fo

#2 rjosori  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 10-May 09

Re: Casting a C# String into a date type that matches Oracle's date Fo

Posted 16 September 2012 - 03:37 PM

I was able to resolve this with Oracle's to_date function:
var valueCommand = "VALUES(" + saleID + "," + custID + "," + agentID + ", to_date('" + saleDate + "', 'mm/dd/yyyy')," + contractID + "," + homeID + "," + actualAmount + ")";

Was This Post Helpful? 0
  • +
  • -

#3 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4422
  • View blog
  • Posts: 7,690
  • Joined: 08-June 10

Re: Casting a C# String into a date type that matches Oracle's date Fo

Posted 17 September 2012 - 08:39 AM

Please read the following article before you continue working with databases:

http://www.dreaminco...ery-a-database/

You're asking for real trouble the way you're doing it.
Was This Post Helpful? 3
  • +
  • -

Page 1 of 1