Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-07
7
Medium Priority
?
149 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
[X]
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
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 18

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 18

Assisted Solution

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

Accepted Solution

by:
tliotta earned 1000 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 18

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

715 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