Solved

Help with creating serch string using "Right" for partial sring search

Posted on 2014-02-18
26
236 Views
Last Modified: 2014-02-20
Hi,

I tried to build a Query string to search a table using the code below


 Dim SearchCriteria As String = "Right (SN,3) IN (" & xyz & " ) AND CA LIKE " & xyzwa & ""

But received the following error:

The expression contains undefined function call Right().

How do I avoid this error?

Thanks,

Victor
0
Comment
Question by:vcharles
  • 17
  • 4
  • 3
  • +1
26 Comments
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 39868708
Missing Namespace Microsoft.VisualBasic
Microsoft.VisualBasic.Right(
0
 

Author Comment

by:vcharles
ID: 39868746
Can you please send me the complete text.

I tried to import Microsoft.VisualBasic.Right( ) but Right couldn't be found.

Thanks,

V.
0
 

Author Comment

by:vcharles
ID: 39868957
Hi,

To clarify my question,

For example the table contains SN contains 10411 and xyz contains '104', '105', I'm trying to search for the first three digits in the table.



 Dim SearchCriteria As String = "Left(SN,3) IN (" & xyz & " ) AND CA LIKE " & xyzwa & ""

Both Right() and Left() are not recognize in this code, but they work in Select statements.

Victor
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 39869047
SQL server has those commands as well
Public Class Form1

    Dim searchCriteria As String
    Dim SN As String = "Thisisthestring"
    Dim xyz As String
    Dim CA As String
    Dim xyzwa As String
    End Sub

    Private Sub Form1_Click(sender As Object, e As EventArgs) Handles Me.Click
        TextBox1.Text = Microsoft.VisualBasic.Right(SN, 6)
    End Sub


End Class

Open in new window

0
 

Author Comment

by:vcharles
ID: 39869077
Hi,

I'm getting the following error:

The expression contains undefined function call Microsoft.VisualBasic.Left().

On line:

Dim DVA As New DataView(dtsetLinkBELBB.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)


Code:
 Dim SearchCriteria As String = "Microsoft.VisualBasic.Left(SN, 3) IN (" & xyz & " ) AND CA LIKE " & xyzwa & ""

                Dim FilteredDTA As DataTable
                Dim DVA As New DataView(dtsetLinkBELBB.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
                FilteredDTA = DVA.ToTable
                If FilteredDTA.Rows.Count > 0 Then
                    'C1Screen1.DataSource = FilteredDTA
                    Me.C1Screen1.DataSource = FilteredDTA.DefaultView.ToTable(True, New String() {"SN"})
                Else
                    MsgBox("No Records Found")

                End If
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 250 total points
ID: 39869522
You don't use a namespace when working with a RowFilter. Neither Right nor Left are supported by the RowFilter property--only SubString is. To achieve a "Right" using the SubString function you can do:

SUBSTRING(SN, LEN(SN) - 2, 3)

Open in new window


...where your 2nd argument is the length of the value minus one less than how many you want to take. The last parameter is the number you want to take.

A "Left" is just a SubString call whose 2nd parameter is always 1.
0
 

Author Comment

by:vcharles
ID: 39870131
Hi,

I tried the code below, but received error message:

The expression has too many closing parentheses.

On Line:
Dim DVA As New DataView(dtsetLinkBELBB.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)


How can I fix this error? As mentioned SN field containn 5 characters (i.e. 10411) and I need to only seacrh the first three character to match against xyz.

Code:

Dim SearchCriteria as String = "Substring(SN, LEN(SN) -2,3)) IN (" & xyz &") AND CA LIKE " & XYZWA &""
Dim FilteredDTA As DataTable
Dim DVA As New DataView(dtsetLinkBELBB.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
FilteredDTA = DVA.ToTable
                If FilteredDTA.Rows.Count > 0 Then
                    'C1Screen1.DataSource = FilteredDTA
                    Me.C1Screen1.DataSource = FilteredDTA.DefaultView.ToTable(True, New String() {"SN"})
                Else
                    MsgBox("No Records Found")

                End If

Thanks,

Victor
0
 

Author Comment

by:vcharles
ID: 39870180
Hi,

I fixed the error and tried SUBSTRING(SN, LEN(SN) - 3, 1), but it did not work. What is the proper syntax to consider only the first three characters?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39870211
Use the "Left" that I mentioned above.
0
 

Author Comment

by:vcharles
ID: 39870303
I still can't figure it out, can you please send me an example.

Thanks.
0
 

Author Comment

by:vcharles
ID: 39870367
I tried using the Synstring without the LEN() part, but received error message "expression
contains undefined function call Substring()"

SUBSTRING(SN, - 3, 1),

What is the  proper syntax?

Thanks,

Victor
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39870388
As I mentioned above, the 2nd parameter needs to be a 1 when you want to perform a "Left":

SUBSTRING(SN, 1, 3)

Open in new window


The first parameter is the string being substringed, the second parameter is where you will start from, and the third parameter is how many you will take. A "Left" would start from the beginning of the string, hence it would always start from position 1.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39870403
First of all, are you using this as a rowfilter of dataview or as part of a SQL query?
0
 

Author Comment

by:vcharles
ID: 39870432
Hi,

I tried the code below but received the same error message:

Dim SearchCriteria as String = "Substring(SN, 1,3)) IN (" & xyz &") AND CA LIKE " & XYZWA &""


I'm using this as a rowfilter of dataview.

V.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 250 total points
ID: 39870506
Try

Dim SearchCriteria as String = "Substring(SN, 1,3) IN ('" & xyz &"') AND CA LIKE '" & XYZWA &"'"

Open in new window

0
 

Author Comment

by:vcharles
ID: 39870551
Hi,

I received error message "Missing operand after '104' operator.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39870679
Add a

Msgbox SearchCriteria and show us the result.
0
 

Author Comment

by:vcharles
ID: 39870726
I took out the second part of the code but still got the same error:

When I try to use:

Dim SearchCriteria as String = "Substring(SN, 1,3) IN ('" & xyz &"')

Message box Returns:"

Substring(SN, 1,3) IN ("104")


I think I'm supposed to only a single quote in 104 because xyz returns '104'.

V.
0
 

Author Comment

by:vcharles
ID: 39870759
It works when I remove the quotes between 104.

Dim SearchCriteria as String = "Substring(SN, 1,3) IN (" & xyz &")


Would the code below still apply for search the last 5 digits?

SUBSTRING(SN, LEN(SCOUNTRY) - 2, 5)

For example if my data fileds containd BEL 10411, I want to search for 10411 only?

Also How do you search for Mid values?  For example If I have

1306-00-623-9897

I would like to search for 00.

Thank You for all the Help

V.
0
 

Author Comment

by:vcharles
ID: 39870781
Hi,

I ran into another problem when I added the second part of the code:

Dim SearchCriteria as String = "Substring(SN, 1,3) IN (" & xyz &") AND CA LIKE LIKE '" & XYZWA &"'"

Message Box for XYZWA returns CA LIKE '*BEL*'

Error:

Cannot find column [BEL]

v.
0
 

Author Comment

by:vcharles
ID: 39870796
Hi,

It worked with :

Dim SearchCriteria as String = "Substring(SN, 1,3) IN (" & xyz &") AND (" & XYZWA & ")".

V.
0
 

Author Comment

by:vcharles
ID: 39871097
Hi again,

When I try to retreive the last 5 digits from my data field (BEL 10411) using the following code.

SUBSTRING(SCOUNTRY, LEN(SCOUNTRY) - 3, 5)

I receive error message, cannot find table(0).

Also tried Substring(SCOUNTRY, 3,5) but it didn't work.

V.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39871164
"Cannot find table?" Are you referencing the correct table?
0
 

Author Comment

by:vcharles
ID: 39871551
I fixed that error but the codes below do not work, what is the proper syntax?  

For example, I am trying to match 10411 from (BEL 10411) against '10411'

"SUBSTRING(SCOUNTRY, LEN(SCOUNTRY) - 3, 5) IN (" & XYZWA & " )"

"SUBSTRING(SCOUNTRY,  3, 5) IN (" & XYZWA & " )"

Thanks,

Victor
0
 

Author Comment

by:vcharles
ID: 39873361
Help!
0
 

Author Comment

by:vcharles
ID: 39873880
OK, Got it, after reading your initial explanation. It worked with:

SUBSTRING(SN, LEN(SN) - 4, 5)


Thank You.

V.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

713 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