[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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

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
vcharles
Asked:
vcharles
  • 17
  • 4
  • 3
  • +1
2 Solutions
 
David Johnson, CD, MVPOwnerCommented:
Missing Namespace Microsoft.VisualBasic
Microsoft.VisualBasic.Right(
0
 
vcharlesAuthor Commented:
Can you please send me the complete text.

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

Thanks,

V.
0
 
vcharlesAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
David Johnson, CD, MVPOwnerCommented:
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
 
vcharlesAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
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
 
käµfm³d 👽Commented:
Use the "Left" that I mentioned above.
0
 
vcharlesAuthor Commented:
I still can't figure it out, can you please send me an example.

Thanks.
0
 
vcharlesAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
CodeCruiserCommented:
First of all, are you using this as a rowfilter of dataview or as part of a SQL query?
0
 
vcharlesAuthor Commented:
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
 
CodeCruiserCommented:
Try

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

Open in new window

0
 
vcharlesAuthor Commented:
Hi,

I received error message "Missing operand after '104' operator.
0
 
CodeCruiserCommented:
Add a

Msgbox SearchCriteria and show us the result.
0
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
Hi,

It worked with :

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

V.
0
 
vcharlesAuthor Commented:
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
 
käµfm³d 👽Commented:
"Cannot find table?" Are you referencing the correct table?
0
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
Help!
0
 
vcharlesAuthor Commented:
OK, Got it, after reading your initial explanation. It worked with:

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


Thank You.

V.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now