CODEWITHSUNDEEP
excelvbadatabaseoracle-database
Aaron Delos Angeles
Aaron Delos Angeles

Reputation: 11

How can I solve ORA-01861 error in VBA code when retrieving data from Oracle database based on title name?

My VBA code is showing an error of "ORA-01861: literal does not match format string". However, when I key this title name "711_23PS" the code is working. Other available title name, the code is getting an error. This VBA code is to get data from oracle database by using "title name" under the "TITLE" column in the database.

Sub RetrieveSpecificTitleFromOracle()
    ' Declare variables
    Dim conn As Object ' Connection
    Dim rs As Object ' Recordset
    Dim strSql As String ' SQL query
    Dim i As Integer ' Column counter
    Dim titleName As String ' Title name
    Dim colCount As Integer ' Number of columns

    ' Set up Oracle connection
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=DATASOURCEXXX;User ID=USERIDXXX;Password=PASSWORDXXX;"
    conn.Open

    ' Get the title name from the user
    titleName = InputBox("Enter the title name:")
    If titleName = "" Then Exit Sub ' If the user cancels, exit the macro

    ' Set up SQL query to retrieve specific row by title name
    strSql = "SELECT * FROM FAR_OWNER.FAR_VX_REQUESTS WHERE TITLE = '" & titleName & "'"

    ' Execute the query
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSql, conn

    ' Check if any data is returned
    If Not rs.EOF Then
        ' Get the number of columns
        colCount = rs.Fields.Count

        ' Copy data to Excel sheet
        For i = 1 To colCount
            Cells(1, i).Value = rs.Fields(i - 1).Name ' Copy column headers
            Cells(2, i).Value = rs.Fields(i - 1).Value ' Copy data from the selected row
        Next i

        ' Auto-fit columns
        Columns.AutoFit
    Else
        MsgBox "No data found for the specified title name."
    End If

    ' Clean up
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

    ' Inform user about the completion
    MsgBox "Data retrieval completed."
End Sub

Upvotes: 1

Views: 102

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

A far larger concern is the following piece of code

' Get the title name from the user
titleName = InputBox("Enter the title name:")
If titleName = "" Then Exit Sub ' If the user cancels, exit the macro

' Set up SQL query to retrieve specific row by title name
strSql = "SELECT * FROM FAR_OWNER.FAR_VX_REQUESTS WHERE TITLE = '" & titleName & "'"

Search this site, or google for "SQL Injection" and you'll discover that the code you have is virtually a guarantee that you're going to get hacked.

Sort out this first, and then worry about dates.

Upvotes: 0

Related Questions