Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
170 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 36

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 19

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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 19

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 19

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

578 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