Share via

Test if a String Exists in a Column in a DataTable

  • Article

Question

Thursday, September 15, 2011 11:00 PM

I have two tables, dt & dtResults.

dt contains 3 columns named: vchInstrumentAnalyteID, bintAnalyteCodeID, & vchAnalyteCode

dtResults contains several columns including the columns in dt

I want to get vchInstrumentAnalyteID from the first row in dt and test if it exists in the vchInstrumentAnalyteID column in dtResults.  If it exists, I want to use that value from dt to filter rows in dtResults and fill all the filtered rows with data from that row in dt.  Then loop to the next row in dt.

I have this code, which is close to what I need, but I can't get it working.  Can someone help me out with this?  I am new to VB.NET, so if you see anything that looks wrong or can be improved I would greatly appreciate it.

 

            ' put analyte data in results table
            For Each drA As DataRow In dt.Rows

                If dtResults.Columns("vchInstrumentAnalyteID").find(drA("vchInstrumentAnalyteID").ToString) <> -1 Then
                    dtResults.DefaultView.RowFilter = "vchInstrumentAnalyteID = '" & drA("vchInstrumentAnalyteID").ToString & "'"
                End If

                For Each drB As DataRow In dtResults.Rows
                    drB("bintAnalyteCodeID") = drA("AnalyteCodeID")
                    drB("vchAnalyteCode") = drA("vchAnalyteCode")
                Next drB

                dtResults.AcceptChanges()

            Next drA

 

Thanks,

Ryan

All replies (6)

Friday, September 16, 2011 1:47 AM âś…Answered | 1 vote

After several hours to do a seeming easy task.  I have figured it out.  This is what I was trying to do.

Unless of course someone has a better way?

            For Each dr As DataRow In dt.Rows

                Dim dv As New DataView(dtResults, "vchInstrumentAnalyteID = '" & dr("vchInstrumentAnalyteID") & "'", "vchInstrumentAnalyteID", DataViewRowState.CurrentRows)

                For Each dvr As DataRowView In dv
                    dvr("bintAnalyteCodeID") = dr("AnalyteCodeID")
                    dvr("vchAnalyteCode") = dr("vchAnalyteCode")
                Next dvr

                dv = Nothing
            Next dr

 

Thanks for the help!

Ryan

Thursday, September 15, 2011 11:51 PM | 1 vote

Ryan,

If you're using a BindingSource, then testing for the existence is pretty easy using the BindingSource's own "Find" method:

http://msdn.microsoft.com/en-us/library/ms158165.aspx

Simply put, if it returns -1, it's not there and otherwise, it will return the row number that it's on.

Friday, September 16, 2011 12:41 AM

Frank,

Forgive me, but I'm new to VB.NET.  I don't believe dtResults is a BindingSource.  I created dtResults in a previous procedure simply using Dim dtResults As New DataTable, then added some columns to it.  I thought I was doing the right thing by using Find in my code above, but it doesn't work.  I get an error that says "There is no primary key in this table."

I basically need to grab the vchInstrumnetAnalyteID value from each row in dt and use that value to filter dtResults.  Once dtResults is filtered I want to add the matching data (bintAnalyteCodeID & vchAnalyteCode) from dt to all the filtered rows in dtResults.  I may not be doing it the best way.  Any suggestions or psuedo code?

Thanks

Ryan

Friday, September 16, 2011 12:48 AM

Frank,

Forgive me, but I'm new to VB.NET.  I don't believe dtResults is a BindingSource.  I created dtResults in a previous procedure simply using Dim dtResults As New DataTable, then added some columns to it.  I thought I was doing the right thing by using Find in my code above, but it doesn't work.  I get an error that says "There is no primary key in this table."

I basically need to grab the vchInstrumnetAnalyteID value from each row in dt and use that value to filter dtResults.  Once dtResults is filtered I want to add the matching data (bintAnalyteCodeID & vchAnalyteCode) from dt to all the filtered rows in dtResults.  I may not be doing it the best way.  Any suggestions or psuedo code?

Thanks

Ryan

I'm really not much of a database guy - maybe I'd best back out of this whole thing. ;-)

Sorry it didn't help.

Friday, September 16, 2011 12:56 AM

This code works for me.  I figure there is probably a much faster way or elegant way to do this.  Anyone has a better way?

            ' put analyte data in results table
            For Each drA As DataRow In dt.Rows
                For Each drB As DataRow In dtResults.Rows
                    If drA("vchInstrumentAnalyteID").ToString = drB("vchInstrumentAnalyteID").ToString Then
                        drB("bintAnalyteCodeID") = drA("AnalyteCodeID")
                        drB("vchAnalyteCode") = drA("vchAnalyteCode")
                    End If
                Next drB

                dtResults.AcceptChanges()

            Next drA

 

Ryan

Tuesday, September 20, 2011 7:58 AM

Hi Ryan0827,

Filtering rows by creatring a new DataView seems more faster. I think your method is OK. If you have any other concerns about this issue, please feel free to let us know.

Have a nice day.

Best regards

Liliane Teng [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.