none
How to search xml file using multiple text boxes? RRS feed

  • Question

  • I am trying to build an SQL using multiple text boxes based on logical (or, and, Not IN) conditions; How do I modify the DV statement usng xml  files instead of ACCESS? The SQL code for an access database is provided below.

    Code for xml:

     Dim dt As New DataTable()
            dt.Columns.Add("Link_ID")
            dt.Columns.Add("Receiver")
            dt.Columns.Add("Prop")
            dt.Columns.Add("Donor")  'Add (1)

            Dim linker As XElement = XElement.Load(Application.StartupPath + "\Link.xml")
            Dim propmsn As XElement = XElement.Load(Application.StartupPath + "\prop.xml")
            Dim receiver As XElement = XElement.Load(Application.StartupPath + "\Receiver.xml")
            Dim Donor As XElement = XElement.Load(Application.StartupPath + "\Donor.xml") 'Add (2)
            For Each item As XElement In linker.Elements("Row")
                Dim linkID As String = item.Element("Link_ID").Value
                Dim receiverId As String = item.Element("Receiver_ID").Value
                Dim propId As String = item.Element("Prop_ID").Value
                Dim DonorID As String = item.Element("Donor_ID").Value 'Add (3)

                Dim propVal As String = String.Empty
                Dim xe As XElement = propmsn.Elements("Row").Cast(Of XElement)().Where(Function(n) n.Element("Prop_ID").Value = propId).FirstOrDefault()
                If xe IsNot Nothing Then
                    propVal = xe.Element("Prop").Value
                End If
                Dim receiverVal As String = String.Empty
                xe = receiver.Elements("Row").Cast(Of XElement)().Where(Function(n) n.Element("Receiver_ID").Value = receiverId).FirstOrDefault()
                If xe IsNot Nothing Then
                    receiverVal = xe.Element("Receiver").Value
                End If
                Dim DonorVal As String = String.Empty 'Add (4)
                xe = Donor.Elements("Row").Cast(Of XElement)().Where(Function(n) n.Element("Donor_ID").Value = DonorID).FirstOrDefault()
                If xe IsNot Nothing Then
                    DonorVal = xe.Element("Donor").Value
                End If
                Dim dr As DataRow = dt.NewRow()
                dr("Link_ID") = linkID
                dr("Receiver") = receiverVal
                dr("Prop") = propVal
                dr("Donor") = DonorVal 'Add (5)
                dt.Rows.Add(dr)
            Next
            Dim bs As New BindingSource()
            bs.DataSource = dt
            C1TrueDBGrid1.DataSource = bs
            Dim DV As New DataView
            DV = (dt, "Receiver like " & "'" & C1TrueDBGrid1.Columns(1).Value & "'" & "", Nothing, DataViewRowState.CurrentRows)
            Dim FilteredDT As DataTable
            FilteredDT = DV.ToTable
            C1TrueDBGrid1.DataSource = FilteredDT
            Me.C1TrueDBGrid1.Splits(0).DisplayColumns(0).Width = Me.C1TrueDBGrid1.Splits(0).DisplayColumns(0).Width - 100


    Code with problem with concatenating sql statement
    *************************************************************
            If Not ((TextBox1.Text = String.Empty) And (TextBox2.Text = String.Empty) And (TextBox3.Text = String.Empty) And (TextBox4.Text = String.Empty)) Then
                If Not (TextBox1.Text = String.Empty) Then
             DV = (dt, "Receiver like " & "'" & TextBox1.Text & "'" & "", Nothing, DataViewRowState.CurrentRows)
                End If

                If (TextBox1.Text = String.Empty) Then
               DV = (dt, "Receiver Not like " & "'" & TextBox1.Text & "'" & "", Nothing, DataViewRowState.CurrentRows)
                End If

                If Not (TextBox2.Text = String.Empty) Then
                    If astrixState1 = 0 Then
                     DV = DV + ("Prop like " & "'" & TextBox2.Text & "'" & "", Nothing, DataViewRowState.CurrentRows)
                    ElseIf astrixState1 = 1 Then
                    DV = DV + (" and Prop like " & "'" & TextBox2.Text & "'" & "", Nothing, DataViewRowState.CurrentRows)
                    ElseIf astrixState1 > 1 Then
                     DV = DV + (" or Prop like " & "'" & TextBox2.Text & "'" & "", Nothing, DataViewRowState.CurrentRows)
                    End If
                End If

                If Not (TextBox3.Text = String.Empty) Then
                    If astrixState1 = 0 Then
                     DV = DV + ("Donor like " & "'" & TextBox3.Text & "'" & "", Nothing, DataViewRowState.CurrentRows)
                    ElseIf astrixState1 = 1 Then
                    DV = DV + (" and Donor like " & "'" & TextBox3.Text & "'" & "", Nothing, DataViewRowState.CurrentRows)
                    ElseIf astrixState1 > 1 Then
                     DV = DV + (" or Donor like " & "'" & TextBox3.Text & "'" & "", Nothing, DataViewRowState.CurrentRows)
                    End If
                End If
            End If
    *******************************************************

    Working Code used for MS ACCESS:

     mySQL_Statement = "SELECT Link_Table.Link_ID, " _
           & "Prop.Description, Receiver.Description, Donor.Description" _
          & "FROM (((Link_Table " _
            & "INNER JOIN Prop ON Link_Table.Prop_ID = Prop.Prop_ID) " _
            & "INNER JOIN Receiver ON Link_Table.Receiver_ID = Receiver.Receiver_ID) " _
            & "INNER JOIN Donor ON Link_Table.Donor_ID = Donor.Donor_ID) "

    If Not ((TextBox1.Text = String.Empty) And (TextBox2.Text = String.Empty) And (TextBox3.Text = String.Empty)) Then
                mySQL_Statement = mySQL_Statement + " WHERE " '(2)

                If Not (TextBox1.Text = String.Empty) Then
                    mySQL_Statement = mySQL_Statement + "LINK_TABLE.Receiver_ID IN (" + TextBox1.Text & ")"
                End If

                If (TextBox1.Text = String.Empty) Then
                    mySQL_Statement = mySQL_Statement + "isnull(LINK_TABLE.Receiver_ID) = false"
                End If

                If Not (TextBox2.Text = String.Empty) Then
                    If astrixState2 = 0 Then
                        mySQL_Statement = mySQL_Statement + " OR LINK_TABLE.Donor_ID IN (" + TextBox2.Text & ")"
                    ElseIf astrixState2 = 1 Then
                        mySQL_Statement = mySQL_Statement + " AND LINK_TABLE.Donor_ID IN (" + TextBox2.Text & ")"
                    ElseIf astrixState2 > 1 Then
                        mySQL_Statement = mySQL_Statement + " OR LINK_TABLE.Donor_ID NOT IN (" + TextBox2.Text & ")"
                    End If
                End If

                If Not (TextBox3.Text = String.Empty) Then
                    If astrixState1 = 0 Then
                        mySQL_Statement = mySQL_Statement + " OR LINK_TABLE.PROP_ID IN (" + TextBox3.Text & ")"
                    ElseIf astrixState1 = 1 Then
                        mySQL_Statement = mySQL_Statement + " AND LINK_TABLE.PROP_ID IN (" + TextBox3.Text & ")"
                    ElseIf astrixState1 > 1 Then
                        mySQL_Statement = mySQL_Statement + " OR LINK_TABLE.PROP_ID NOT IN (" + TextBox3.Text & ")"
                    End If
                End If
            End If

    'connect to database
            Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AOP2.mdb"
            Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

            'data adapter
            Dim objDataAdapter As New OleDb.OleDbDataAdapter(mySQL_Statement, objConnection)

            'dataset object
            Dim objDataSet As New DataSet
            ''fill dataset
            objConnection.Open()
            objDataAdapter.Fill(objDataSet, "SN")
            objConnection.Close()

            ''set dgv
            C1TrueDBGrid1.DataSource = objDataSet
            C1TrueDBGrid1.DataMember = "SN"

    Thanks,

    Victor

     


    Monday, January 4, 2010 3:20 PM

Answers

  • Hello Victor,

     

    After I researched your sample codes, I have several points which I don’t quite understand.  Could you please help me to clarify these points?

     

    ·         The constructor and the concatenation of DataView

    I think you called one of the constructors of the DataView, DataView(DataTable, String, String, DataViewRowState).  However, these lines of codes make me confused:

    DV = (dt, “…”, Nothing, DataViewRowState.CurrentRows)

    DV = DV + (dt, “…”, Nothing, DataViewRowState.CurrentRows)

    The VB.NET compiler cannot recognize these codes, or am I wrong?   (I am not a VB.NET language expert.  J)

     

    But, if I understand correctly, these codes will concatenate the RowFilter string.   The RowFilter of DataView has its own standard which is not equal to SQL.  For detail, please see http://www.csharp-examples.net/dataview-rowfilter/ & http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx.

     

     

    ·         The query in Access is not consist with the one in the RowFilter

    In the Access query, you used the “WHERE … IN()” query, while in the RowFilter the “LIKE” operator is used.  Based on my understanding, the “WHERE IN()” will check whether a certain value is included in a collection of values, and the “LIKE” operation is checking whether a String value meets a specific format.  

     

    For the LIKE operator, such a expression “Name LIKE ‘j*’” will get values that start with “j”.   So in the sample codes, I don’t think we need to the last “” when concatenating the RowFilter string, such as the one in yellow, DV = DV + (“ and Prop like “ & “’” & TextBox2.Text & “’” & “”, Nothing…

     

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, January 5, 2010 4:49 AM
    Moderator

All replies

  • Hello Victor,

     

    After I researched your sample codes, I have several points which I don’t quite understand.  Could you please help me to clarify these points?

     

    ·         The constructor and the concatenation of DataView

    I think you called one of the constructors of the DataView, DataView(DataTable, String, String, DataViewRowState).  However, these lines of codes make me confused:

    DV = (dt, “…”, Nothing, DataViewRowState.CurrentRows)

    DV = DV + (dt, “…”, Nothing, DataViewRowState.CurrentRows)

    The VB.NET compiler cannot recognize these codes, or am I wrong?   (I am not a VB.NET language expert.  J)

     

    But, if I understand correctly, these codes will concatenate the RowFilter string.   The RowFilter of DataView has its own standard which is not equal to SQL.  For detail, please see http://www.csharp-examples.net/dataview-rowfilter/ & http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx.

     

     

    ·         The query in Access is not consist with the one in the RowFilter

    In the Access query, you used the “WHERE … IN()” query, while in the RowFilter the “LIKE” operator is used.  Based on my understanding, the “WHERE IN()” will check whether a certain value is included in a collection of values, and the “LIKE” operation is checking whether a String value meets a specific format.  

     

    For the LIKE operator, such a expression “Name LIKE ‘j*’” will get values that start with “j”.   So in the sample codes, I don’t think we need to the last “” when concatenating the RowFilter string, such as the one in yellow, DV = DV + (“ and Prop like “ & “’” & TextBox2.Text & “’” & “”, Nothing…

     

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, January 5, 2010 4:49 AM
    Moderator
  • Hello Victor,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, January 7, 2010 4:04 AM
    Moderator