A LIKE statement does not return any data with Oracle character data type

Article translations Article translations
Article ID: 307514 - View products that this article applies to.
This article was previously published under Q307514
This article has been archived. It is offered "as is" and will no longer be updated.
This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System
  • System.Data
  • System.Data.OleDb
Expand all | Collapse all

On This Page

SYMPTOMS

When you use Microsoft OLE DB Provider (MSDAORA) or the OLE DB .NET Data Provider for Oracle, if you use a LIKE statement to search fixed-length character types, Oracle does not find any rows if their padding is different.

CAUSE

If you type a value into a fixed-length field that is shorter than the length of that field, the value is padded with trailing spaces to fill the fixed length. When Oracle matches values for fixed-length fields, it matches the entire length of the string, including any trailing spaces.

NOTE: This behavior is not specific to ADO.NET and also occurs when you use Microsoft ActiveX Data Objects (ADO).

RESOLUTION

To resolve this problem, always use the percentage ("%") wildcard character when you use LIKE statements to compare values in fixed-length fields in an Oracle database. For example:
cmd.Parameters.Add("@p1", OleDbType.Char, 3).Value = "a%"
				

MORE INFORMATION

Steps to Reproduce the Behavior

  1. In Oracle, create a table named TestTable as follows:
    Create Table TestTable (c1 char(3));
    					
  2. Insert the value 'a' into the table as follows:
    Insert into TestTable c1 values('a');
    					
  3. Open Microsoft Visual Studio .NET or Visual Studio 2005.
  4. Create a new Windows Application project in Visual Basic .NET.
  5. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
  6. Place a Command button on Form1, and change its Name property to btnTest.
  7. Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code.
    Imports System
    Imports System.Data
    Imports System.Data.OleDb
    					
  8. Copy and paste the following code in the code window after the "Windows Form Designer generated code" region:
    Private Sub btnTest_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnTest.Click
        Dim sConnectionString As String _
            = "Provider=MSDAORA.1;User ID=scott;password=tiger;"_
              "Data Source=myOracleServer;Persist Security Info=False"
        Dim mySelectQuery As String _
            = "SELECT * FROM TestTable where c1 LIKE ?"
        Dim myConnection As New OleDbConnection(sConnectionString)
        Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)
        
        'To resolve this problem, change the parameter
        'in the next line to use "%" wildcard character.
        myCommand.Parameters.Add("@p1", OleDbType.Char, 3).Value = "a"
        myConnection.Open()
        Dim myReader As OleDbDataReader = myCommand.ExecuteReader()
        Dim RecordCount as Integer
        Try
            While myReader.Read()
                RecordCount = RecordCount + 1
                MessageBox.Show(myReader.GetString(0).ToString())
            End While
            If RecordCount = 0 then
                MessageBox.Show("No data returned")
            Else
                MessageBox.Show("Number of records returned: " & RecordCount)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
           'Always call Close when done reading.
            myReader.Close()
            myConnection.Close()
        End Try
    End Sub
    					
  9. Save your project.
  10. On the Debug menu, click Start, and run your project.
  11. Click the button. Notice that no data is returned.
  12. Change the parameter to use "%" wildcard character as follows:
    myCommand.Parameters.Add("@p1", OleDbType.Char, 3).Value = "a%"
    					
  13. Run the project again. Notice that the data is returned successfully.

REFERENCES

For more information on ADO.NET objects and syntax, refer to the following topic in the Microsoft .NET Framework Software Development Kit (SDK) documentation:
Accessing Data with ADO.NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaccessingdatawithadonet.asp

Properties

Article ID: 307514 - Last Review: February 28, 2014 - Revision: 3.9
APPLIES TO
  • Microsoft ADO.NET 2.0
  • Microsoft ADO.NET 1.0
  • Microsoft ADO.NET 1.1
  • Microsoft .NET Framework 1.0
  • Microsoft .NET Framework 1.1
  • Microsoft .NET Framework 2.0
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic 2005
Keywords: 
kbnosurvey kbarchive kbtshoot kbnofix kbprb kbreadme KB307514

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com