Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Problem with a LIKE clause  with a "*" inside in VB.Net accessing a DB2/400 table

Posted on 2016-10-07
7
97 Views
Last Modified: 2016-11-04
Hi

I get an error with a SQL statement getting data in a DB2/400 database when the WHERE clause contains a LIKE clause where the content of the LIKE includes the star character, "*", and this in a VB.Net application.
When my SELECT statement looks like this:

SELECT ... FROM MyTable WHERE Field1 LIKE 'ABC* %'

i.e. when the content of the like clause includes a star character.  The content of the LIKE clause is actually the value of  a field from an other table, so I do not control the presence or absence of the star character.

I get the error: "Error in Like operator: the string pattern 'ABC* %' is invalid."

The connection is done over IBM's own ODBC driver for the iSeries.

I *guess* it's because the star character is the wild card character in the Microsoft world, and the ODBC driver gets confused with 2 wild characters, maybe ? This is not an IBM DB2 error message, therefore probably a Microsoft error message.

Has anybody had this combination and knows how to avoid the error ?

Thanks for help on this
Bernard
0
Comment
Question by:bthouin
7 Comments
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 41833720
Please show your code.

Not a fix but a workaround: Field1 LIKE 'ABC* %'  is functionally equivalent to LEFT(Field1,4) = 'ABC* '
0
 
LVL 15

Expert Comment

by:John Tsioumpris
ID: 41833721
Since you are using ODBC you could do a test by using Ms Access(if you know/have) to establish connection to your DB2 and perform the query (pass through)
0
 
LVL 1

Author Comment

by:bthouin
ID: 41833770
@Gary
My code:

Dim dsCT As New DataSet
Dim selRow as DataRow()
selRow = dsCT.Tables("MyTable").Select("Fild1 = 'Bla' " & _
                                                                              "AND Field2 LIKE '" & MyTextWithStar & " %' " & _
                                                                              "AND Field3 = 'Blo'")

Open in new window


if MyTextWithStar contains a "*", I get the error. If there is no star, it works fine.

@John
I frankly don't see how Access is going to help. I'm developing also with Access and VBA, and I am accessing the same tables in the DB2/400 with Access, so that's not a problem to try, but what's the goal ?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 15

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 250 total points
ID: 41833832
Can you please check if this applies to your situation .
0
 
LVL 27

Accepted Solution

by:
tliotta earned 250 total points
ID: 41833926
John Tsioumpris essentially has what could be the answer. You should try enclosing the asterisk in square brackets. However, that probably requires validating any MyTextWithStar value to see if it contains an asterisk (or a percent sign, "%", since both have special meaning in a LIKE clause in your case).

You'll want to try replacing every embedded instance of "*" (or "%") with "[*]" (or "[%]") in MyTextWithStar before concatenating it into your LIKE clause.
0
 
LVL 15

Expert Comment

by:John Tsioumpris
ID: 41833929
There is probably an issue with in between characters (the * char)  thats why i pointed the Ms Article in order to be precise...
0
 
LVL 1

Author Comment

by:bthouin
ID: 41836772
Ah, OK, I can easily enclose the * in square brackets as I already detect it and replace it with nothing for the moment to avoid the error. Let's see what happens then.

And to be honest, the MS article is so longwinded and precise that even if I had looked at it (how should I know that I should search for data column expressiion ?..), I would have given up looking long before finding the one reference to the bracket stuff...

Thanks, and I'll revert as soon as I can test the change.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question