Solved

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

Posted on 2014-02-18
26
227 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 78

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
 
LVL 78

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 74

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 74

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 74

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 74

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now