Quite a novice with vb.net so this might be a bit of a daft question, but I have a listview which has one column that can have multilines. I also have a set colour sub which sets row colours to red if one column has a specific value. This works fine unless the multiline column has more than one line, in which case the my setcolour sub gets a bit confused. I need to find a way to ignore the multiline in the loop.. any ideas would be much appreciated. Thanks!
Private Sub ListorderItems()
Dim itm As ListViewItem
Dim dbconnection3 As New SqlClient.SqlConnection
Dim dbcommand3 As New SqlClient.SqlCommand
Dim dreader3 As SqlClient.SqlDataReader
Dim messageline As String
Dim detailtext As String
Dim counter As Integer
Dim Permission As String
Permission = CheckPermissions("Order")
OrderItemID.Items.Clear()
dbconnection3.ConnectionString = SetConnection()
dbconnection3.Open()
dbcommand3.Connection = dbconnection3
dbcommand3.CommandText = "Select Orderitemid,qty,description,costprice,suppliertext,salesprice,vat,despatched,invoiced, Orderdate, status, Specialtext from tblorderitem where orderid = " & gloOrderID
dreader3 = dbcommand3.ExecuteReader
If dreader3.HasRows Then
Do While dreader3.Read
itm = New ListViewItem(dreader3.GetInt32(0))
itm.SubItems.Add(dreader3.GetInt32(1))
'break description text into item lines (looking for carriage returns)
detailtext = IIf(IsDBNull(dreader3("description")), 0, dreader3("description"))
messageline = ""
If Len(detailtext) > 0 Then
counter = 0
While Len(detailtext) > 0 And counter < Len(detailtext)
If Asc(detailtext.Substring(counter, 1).ToString) = 13 Then
messageline = detailtext.Substring(0, counter)
counter = counter + 2
detailtext = detailtext.Substring(counter, Len(detailtext) - counter)
Exit While
End If
counter = counter + 1
End While
End If
'if no return chars found
If Len(messageline) = 0 Then
messageline = IIf(IsDBNull(dreader3("description")), 0, dreader3("description"))
detailtext = ""
End If
itm.SubItems.Add(messageline)
itm.SubItems.Add(dreader3.GetInt32(7))
itm.SubItems.Add(dreader3.GetInt32(8))
itm.SubItems.Add(IIf(IsDBNull(dreader3("Orderdate")), "", dreader3("Orderdate")))
itm.SubItems.Add(IIf(dreader3.GetBoolean(10), "E", "A"))
If Permission = "Write" Then
itm.SubItems.Add(RemoveCarriageReturns(dreader3.GetString(3)))
itm.SubItems.Add(IIf(IsDBNull(dreader3("suppliertext")), 0, dreader3("suppliertext")))
itm.SubItems.Add(ConvertCurrency(dreader3.GetDouble(5)))
itm.SubItems.Add(ConvertCurrency(dreader3.GetDouble(6)))
End If
OrderItemID.Items.Add(itm)
'now add any additional lines (if any return chars found in detail line)
messageline = ""
If Len(detailtext) > 0 Then
counter = 0
While Len(detailtext) > 0 And counter < Len(detailtext)
If Asc(detailtext.Substring(counter, 1).ToString) = 13 Then
messageline = detailtext.Substring(0, counter)
counter = counter + 2
detailtext = detailtext.Substring(counter, Len(detailtext) - counter)
counter = 0
'write additional message lines
itm = New ListViewItem(dreader3.GetInt32(0))
itm.SubItems.Add("")
itm.SubItems.Add(messageline)
OrderItemID.Items.Add(itm)
messageline = ""
End If
counter = counter + 1
End While
End If
'if no return chars found
If Len(messageline) = 0 Then
messageline = detailtext
If Len(messageline) > 0 Then
'write additional message lines
itm = New ListViewItem(dreader3.GetInt32(0))
itm.SubItems.Add("")
itm.SubItems.Add(messageline)
OrderItemID.Items.Add(itm)
End If
End If
Loop
End If
dreader3.Close()
dbconnection3.Close()
dbconnection3.ConnectionString = SetConnection()
dbconnection3.Open()
dbcommand3.Connection = dbconnection3
dbcommand3.CommandText = "Select SUM(salesprice) as TotalValue from tblorderitem where orderid = " & gloOrderID
dreader3 = dbcommand3.ExecuteReader
If dreader3.HasRows Then
While dreader3.Read
TotalSales.Text = "£" & String.Format(dreader3("TotalValue").ToString, "{0:0.00}")
End While
End If
dreader3.Close()
dbconnection3.Close()
dbconnection3.ConnectionString = SetConnection()
dbconnection3.Open()
dbcommand3.Connection = dbconnection3
dbcommand3.CommandText = "Select SUM(vat) as TotalVat from tblorderitem where orderid = " & gloOrderID
dreader3 = dbcommand3.ExecuteReader
If dreader3.HasRows Then
While dreader3.Read
TotalVAT.Text = "£" & String.Format(dreader3("TotalVat").ToString, "{0:0.00}")
End While
End If
dreader3.Close()
dbconnection3.Close()
SetOrderColour()
End Sub
Private Sub SetOrderColour()
Dim i As Integer
For i = 0 To Me.OrderItemID.Items.Count - 1
If Me.OrderItemID.Items(i).SubItems(6).Text.Trim = "E" Then
Me.OrderItemID.Items(i).ForeColor = Color.Red
End If
Next i
End Sub