799

Parsing DateTime From VB.NET to Oracle Stored Procedure

Question:

I've been struggling with Oracle's Date Time Function.

I have stored procedure which runs properly and well done but I don't have any idea how can I parsing the date from VB.net so that the stored procedure can receive the value from VB.net?

Does anyone know how to parsing the value from VB.net, in this case I talk about the Date Time so it will be a hour, second and minute.

Thank you so much

Here's the .NET code

Try Dim conn As New OracleConnection(connectionString(data(0), data(1), data(2))) conn.Open() Dim cmd As New OracleCommand cmd.Connection = conn cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "transactionme" Dim p1 As OracleParameter Dim p2 As OracleParameter Dim p3 As OracleParameter Dim p4 As OracleParameter Dim p5 As OracleParameter Dim p6 As OracleParameter Dim p7 As OracleParameter Dim p8 As OracleParameter Dim p9 As OracleParameter Dim p10 As OracleParameter p1 = cmd.Parameters.Add("param1", OracleDbType.NChar) p1.Value = "" p2 = cmd.Parameters.Add("param2", OracleDbType.NChar) p2.Value = "STD2" p3 = cmd.Parameters.Add("param3", OracleDbType.NChar) p3.Value = "RK001" p4 = cmd.Parameters.Add("param4", OracleDbType.NChar) p4.Value = "EK001" p5 = cmd.Parameters.Add("param5", OracleDbType.Date) p5.Value = "here is the problem" p6 = cmd.Parameters.Add("param6", OracleDbType.Date) p6.Value = "here is the problem" p7 = cmd.Parameters.Add("param7", OracleDbType.Date) p7.Value = "here is the problem" p8 = cmd.Parameters.Add("param8", OracleDbType.NChar) p8.Value = "Master Card" p9 = cmd.Parameters.Add("param9", OracleDbType.Int32) p9.Value = 30000 Dim n As Integer n = cmd.ExecuteNonQuery() MessageBox.Show(n.ToString) conn.Close() Catch ex As Exception MessageBox.Show(ex.ToString) End Try

The parameter should be like this, have tried many things like concat and others

to_date('2012-01-23 22:00', 'YYYY-MM-DD hh24:mi')

Answer1:

According to this <a href="http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/99acc52c-6eb3-48be-8453-907deefc9d8a" rel="nofollow">MSDN Forum Posting</a> the Oracle Date data type corresponds to the .Net Date data type.

From above Link:

<blockquote>

The parameter value should be a Date data value and not a string. There is no need to format the date when you're passing it in as a parameter since the underlying representation is numeric.

</blockquote>

i.e.

p5 = cmd.Parameters.Add("param5", OracleDbType.Date) p5.Value = Convert.ToDateTime("Your Date here")

Edit try this to Parse your Date/time information and Convert your string.

Private Function GetDateTime(dateFormat As String) As Date Dim split() As String = New String() {","} Return Convert.ToDateTime(dateFormat.Split(split, 2, StringSplitOptions.None)(0)) End Function

usage:

p5.Value = GetDateTime("2012-01-23 22:00, YYYY-MM-DD hh24:mi") 'or what ever your string variable is.

Recommend

  • Pass datatable to refcursor of Oracle stored procedure
  • ORA-00911: invalid character?
  • Exception while executing stored procedure odp.net
  • How to schedule two processes if arrival time is same
  • Parameter issue with Oracle RefCursor
  • Oracle DataAccess related: “The invoked member is not supported in a dynamic assembly.”
  • Oracle DB and SecureString
  • binary input with an ASCII text header, read from stdin
  • Postgres vs MongoDB on modeling recursive multi-child relations
  • Integrating Biometric FingerPrint to PHP code
  • Using ExceptionFilterAttribute in Web API
  • Neo4j Variable Depth not working
  • Intersection in Firebase
  • Perforce Integration: Not able to submit the changes to streams from classic work space
  • Updated: Applying a Math Formula in a more elegant way (maybe a recursive call would do the trick) [
  • Entity framework, referential integrity constraint violation occurred error on updating entity from
  • Unable to perform range queries in Cassandra 1.2
  • Why does EF Core always return -1 with this stored procedure?
  • How to generate a bar chart of occurrences per year in matplotlib python?
  • In x86 assembly, is it better to use two separate registers for imul?
  • How to maximize the goal in prolog?
  • Pass code to a method as an argument
  • Looking up a value in a range that is between two given values round 2
  • plot if col A has substring
  • com.jcraft.jsch.JSchException: Algorithm negotiation fail
  • sort decimal in php or mysql
  • Google Contacts API get phone number (PHP)
  • How to change the margins of a correlation matrix plot
  • reshape error - invalid 'row.names' length
  • Making more efficient Matlab ismember for large matrices: any faster suggestion than logical indexin
  • View Paypal shopping cart contents on my site
  • Initialization section of the package
  • Timeout a query
  • VSCode change debug shell to bash on windows
  • Error processing multiple files
  • Check for zero lines output from command over SSH
  • nonblocking BIO_do_connect blocked when there is no internet connected
  • Unable to install Git-core+svn by MacPorts
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • Display Images one by one with next and previous functionality