locked
DateTime Problem? RRS feed

  • Question

  • User-1104215994 posted

    Hi,

    I have a SQL Server and a linked server to Oracle 11g. I have an OpenQuery which Updates the Oracle table as follows:

    Dim str As String = "UPDATE OPENQUERY (LinkedServer, 'SELECT ApprovedBy,ApproveDate,Approved,StatusId 
    FROM SCHEMA.VENDORREQUEST WHERE RequestID ="
     + reqId.ToString + "') SET ApprovedBy = '" + appBy + "',
    ApproveDate = to_date(''"
     + appDate.ToShortDateString + "'',''DD.MM.YYYY''), Approved = " + app.ToString + ",
    StatusId ="
     + status.ToString

    The Problem is, it gives error incorect syntax near 22.10. By the way I can update the table without the ApproveDate. But I want to update the ApproveDate also so How can I fix this?

    Best Regards.

    Saturday, October 22, 2011 8:25 AM

Answers

  • User-1104215994 posted

    Hi,

    I found out.

    I can use

    comm.Parameters.AddWithValue("@approvedate", appDate)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 24, 2011 9:07 AM

All replies

  • User1013750657 posted

    hi try

    ApproveDate = to_date('" + appDate.ToString("ddMMyyyy") + "','DDMMYYYY')

    this should work

    Saturday, October 22, 2011 6:23 PM
  • User-1199946673 posted

    Are you aware of the risks of SQL injection?

    http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET

    Saturday, October 22, 2011 6:51 PM
  • User269602965 posted

    Use Oracle Parameters and BIND VARIABLE to pass date to ORacle update column instead of converting from date to string and then back again,

    espcially since the short date format can vary from user to user, server to server, etc.

    Example

    Imports System.Xml.Linq.XElement

    Dim DateTimeStamp As Date = DateTime.Now
    Dim TicketSeq As Decimal = 1

    Dim connectionString As String = ConfigurationManager.ConnectionStrings("{YourOraConnStringName").ConnectionString
    ' Insert help ticket into database '
    Try
      Dim SQL = <SQL>
                  UPDATE {YourSchemaName}.HELP_TICKET
                  SET TICKET_TIMESTAMP = :TICKET_TIMESTAMP
                  WHERE TICKET_SEQ = :TICKET_SEQ
                </SQL>
      Using conn As New OracleConnection(connectionString)
        Using cmd As New OracleCommand(SQL.Value, conn)
          cmd.Parameters.Clear()
          cmd.Parameters.Add("TICKET_TIMESTAMP", OracleDbType.Date, DateTimeStamp, ParameterDirection.Input)
          cmd.Parameters.Add("TICKET_SEQ", OracleDbType.Decimal, decTicketSeq, ParameterDirection.Input)
          conn.Open()
          cmd.ExecuteNonQuery()
        End Using
      End Using
    Catch ex As Exception
    End Try

    Saturday, October 22, 2011 7:15 PM
  • User-1104215994 posted

    Hi,

    I found out.

    I can use

    comm.Parameters.AddWithValue("@approvedate", appDate)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 24, 2011 9:07 AM