Question How to concatenate SQL using XML files?

[victor64]

Hello,

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.

Thanks,

Victor

Code for xml:

Dim dt As New DataTable()
dt.Columns.Add("Link_ID")
dt.Columns.Add("Receiver")
dt.Columns.Add("Propellant")
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.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.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.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("Propellant") = 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


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"

 

[cjard]

Maybe I missed something in that massive block of code (that isnt using code tags and hence looks a mess. Please use code tags) but what has this got to do with XML?