ASP.NET
 

ASP.NET - Trying insert date and time using date & time Picker Control in oracle  ASP.NET - Trying insert date and time using date & time Picker Control in oracle

Asked By jimmy shaw
22-Feb-11 03:10 AM

I am trying to insert Date and Time in oracle using Date and Time Picker control (asp.net) 

I have a field called HireDate. Now, for this field, I am trying to insert date using date picker control and able to insert successfully but with the same field, I would like to insert Date using Date picker control and Time using Time picker control. The result what, i am expecting is -- Eg:  01-JAN-2011  2:30 PM

What should I add to below code

Dim cn As New OracleConnection("Data Source=abc; User Id=ab;Password=ab")

Dim SQL As String

'build the INSERT statement

Dim xy As New System.Text.StringBuilder

Dim strDate As Date

Dim txtHireDate As String = strDate.ToString("dd-MMM-yy").ToUpper()

xy.Append(" INSERT INTO emp")

xy.Append(" (empno, ename, job, mgr, hiredate)")

xy.Append(" VALUES")

xy.Append(" ({0},'{1}','{2}',{3},'{4}')")

SQL = String.Format(xy.ToString, Me.txtEmpno.Text, Me.txtEname.Text, Me.txtJob.Text, Me.txtMgr.Text, DateTime.Parse(Me.txtHireDate.Text).ToString("dd-MMM-yy").ToUpper())

'create command object

Dim cmd As New OracleCommand(SQL, cn)

'open the connection

cmd.Connection.Open()

'execute the command

Dim result As Integer = cmd.ExecuteNonQuery()

'close the connection

cmd.Connection.Close()

  Anoop S replied to jimmy shaw
22-Feb-11 03:23 AM
If you are picking date from datetimepicker then use the following code

Dim myDate as Date
myDate=myDatepicker.Value

while inserting write code like...........
Insert into myTable (mDate) Values (" & myDate & ")
  jimmy shaw replied to Anoop S
22-Feb-11 03:30 AM
I have two separate control . One is date picker control and one is time picker control and what I would like to do is, both value should store in one variable called HireDate. I am able to add date using date picker control in Hiredate variable but now I would like to add time also with my date. I don't think your suggestion will help me..I would like to use above to code to add date and time...Let me know if you can help me...


  Anoop S replied to jimmy shaw
22-Feb-11 05:25 AM
Do one thing, 1st convert your time to minutes(or seconds for more accurate) and the add minutes to tour datetime object like this way
DateTime later = DateTime.Now.AddMinutes( 50 );



         

  

 



     
  jimmy shaw replied to Anoop S

      

          

               
22-Feb-11 12:09 PM

               

         

         

            
Can you tell me where do you want me to change in my code






Dim cn As New OracleConnection("Data Source=abc; User Id=ab;Password=ab")


Dim SQL As String


'build the INSERT statement


Dim xy As New System.Text.StringBuilder


Dim strDate As Date


Dim txtHireDate As String = strDate.ToString("dd-MMM-yy").ToUpper()


xy.Append(" INSERT INTO emp")


xy.Append(" (empno, ename, job, mgr, hiredate)")


xy.Append(" VALUES")


xy.Append(" ({0},'{1}','{2}',{3},'{4}')")


SQL = String.Format(xy.ToString, Me.txtEmpno.Text, Me.txtEname.Text, Me.txtJob.Text, Me.txtMgr.Text, DateTime.Parse(Me.txtHireDate.Text).ToString("dd-MMM-yy").ToUpper())


'create command object


Dim cmd As New OracleCommand(SQL, cn)


'open the connection


cmd.Connection.Open()


'execute the command


Dim result As Integer = cmd.ExecuteNonQuery()


'close the connection


cmd.Connection.Close()









your help would much appreciated.

         

  

 



  

 
Create New Account
 
  


    
 

help



     
     
value should populate “y” otherwise “N” . Please,  use the below code. Dim cn As New OracleConnection( "Data Source = orcl; User Id = abc;Password = abc" )       Dim SQL As String       'build the INSERT statement       Dim xy As New System.Text.StringBuilder         Dim dtStartDate As DateTime  =  AddCurrentTime(DateTime.Parse(txtStartDate.Text))         Dim dtEndDate As   DateTime  =  AddCurrentTime(DateTime.Parse(txtEndDate.Text))         xy.Append( " INSERT INTO temp" )       xy.Append( " (startdate,  enddate,  overlap,  mintime,  flag   txtOverlap.Text,  Me .txtMinTime.Text,  Me .txtConditionMet.Text)         'create command object       Dim cmd As New OracleCommand(SQL,  cn)       'open the connection       cmd.Connection.Open()       'execute the command       Dim result As Integer  =  cmd.ExecuteNonQuery()       'close the connection       cmd.Connection.Close()       ' =  =  =  =  function     End Sub       Private Function AddCurrentTime( ByVal selectedDate As







     
     
as soon as I accept date from date picker ) sample code Dim cn As New OracleConnection("Data Source = abc; User Id = ab;Password = ab") Dim SQL As String 'build the INSERT statement Dim xy As New System.Text.StringBuilder Dim strDate As Date Dim txtHireDate As String  =  strDate.ToString("dd-MMM-yy hh:mm   Format(xy.ToString,  Me.txtEmpno.Text,  Me.txtEname.Text,  Me.txtJob.Text,  Me.txtMgr.Text,  DateTime.Parse(Me.txtHireDate.Text).ToString("dd-MMM-yy hh:mm:ss tt ").ToUpper()) 'create command object Dim cmd As New OracleCommand(SQL,  cn) 'open the connection cmd.Connection.Open() 'execute the command Dim result As Integer   cmd.ExecuteNonQuery() 'close the connection cmd.Connection.Close() I would use some trick using DateTime object's flexibility like:  The default time value for a DateTime / TimePicker is 12:00 AM (culture dependent). If you wanna give the current time to   the selected date,  you can try something like:  private DateTime AddCurrentTime(DateTime selectedDate) {    int year = SelectedDate.Year;    int month = selectedDate.Month;    int day = selectedDate.Day







     
     
rowCount];              }         }          #region Private Variables           /  /  /  <summary>            /  /  /  Defines the State           /  /  /  < / summary>           private enum State         {              Configuration,              Loading         }           private OracleConnection mConnection;          private OracleCommand mCommand;          private string mTablename;          private int mBatchSize;          private int mTotalRowCount;          private int mBatchRowCount;          private List   int BatchSize         {             get { return mBatchSize; }         }           /  /  /  <summary>            /  /  /  Gets the command.           /  /  /  < / summary>            /  /  /  <value> The command.< / value>           private OracleCommand Command         {             get { return mCommand; }         }          #endregion Private Variables           /  /  /  <summary>            /  /  /  Constructor           /  /  /  < / summary>            /  /  /  <param name = "connection"> < / param>            /  /  /  <param name = "tablename"> < / param>            /  /  /  <param name = "batchSize"> < / param>           public BatchInserter(OracleConnection connection,  string tablename,  int batchSize)         {               if ((mConnection  =  connection)  =  =  null )                  ThrowError( "BatchInserter does not yet support   Date Column           /  /  /  < / summary>            /  /  /  <param name = "columnName"> < / param>           public void AddDateColumn( string columnName)         {             AddColumn(columnName,  typeof (DateTime),  OracleDbType.Date);          }           /  /  /  <summary>            /  /  /  Adds String Column           /  /  /  < / summary>            /  /  /  <param name = "columnName"> < / param>           public void AddStringColumn( string   would be replaced with               /  / the array values.             mColumnArray  =  new Column[mColumnList.Count];             mColumnList.CopyTo(mColumnArray);             StringBuilder insertClause  =  new StringBuilder();             StringBuilder valuesClause  =  new StringBuilder();              insertClause.Append( "insert into " );             insertClause.Append(mTablename);             insertClause.Append( '(' );              valuesClause.Append( " values







     
     
like ORA-01843: not a valid month Sample code is here Dim cn As New OracleConnection("Data Source = abc; User Id = ab;Password = ab") Dim SQL As String 'build the INSERT statement Dim xy As New System.Text.StringBuilder Dim strDate As Date Dim txtHireDate As String  =  strDate.ToString("dd-MMM-yy").ToUpper() xy   txtJob.Text,  Me.txtMgr.Text,  Me.txtHireDate.Text) 'create command object Dim cmd As New OracleCommand(SQL,  cn) 'open the connection cmd.Connection.Open() 'execute the command Dim result As Integer  =  cmd.ExecuteNonQuery() 'close the connection cmd.Connection.Close()   Hi,  As per your code,  it seems that you   below updated code and try this. Let me know the results. Dim cn As New OracleConnection("Data Source = abc; User Id = ab;Password = ab") Dim SQL As String 'build the INSERT statement Dim xy As New System.Text.StringBuilder Dim strDate As Date Dim txtHireDate As String  =  strDate.ToString("dd-MM-yyyy").ToUpper() xy   txtJob.Text,  Me.txtMgr.Text,  Me.txtHireDate.Text) 'create command object Dim cmd As New OracleCommand(SQL,  cn) 'open the connection cmd.Connection.Open() 'execute the command Dim result As Integer  =  cmd.ExecuteNonQuery() 'close the connection cmd.Connection.Close() Same error. No Change. "ORA-01843: not a valid







     
     
hours. Dim SQL As String 'build the INSERT statement Dim xy As New System.Text.StringBuilder Dim dtStartDate As DateTime  =  AddCurrentTime(DateTime.Parse(txtStartDate.Text)) Dim dtEndDate As DateTime  =  AddCurrentTime(DateTime.Parse(txtEndDate.Text)) xy.Append( " INSERT INTO temp" ) xy.Append( " (startdate,  enddate,  overlap)" ) xy.Append   should show in my date diff and which I am not getting. Dim dtStartDate As DateTime  =  AddCurrentTime(DateTime.Parse(txtStartDate.Text)) Dim dtEndDate As DateTime  =  AddCurrentTime(DateTime.Parse(txtEndDate.Text)) Dim ts As TimeSpan ts  =  dtEndDate.Subtract(dtStartDate) txtOverlap.Text