organizing and removing unwanted numbers from datagridview

Hello guys,

I have this code here that updates my gridviewdata with a json file every time i click a button.

Code:

---

  DataGridView2.Rows.Clear()
        Dim uri As Uri = New Uri("https://api.import.io/store/connector/06a2a24f-6678-407e-8ad0-c7cbe24cf33c/_query?input=webpage/url:http%3A%2F%2Ffxowebtools.saxobank.com%2Fretail.html&&_apikey=d4c36ddd8f8d4a629bdf06e47993809233ac34a0ee817f4af327f7e016e1cb0921ba78b0da4d9be881051cbf3cf672f1425214d8e687e33559e24fdd17bc5a606e8ce1eac3d6e064593e3fcc34d23344")
        Dim wc1 As System.Net.WebClient = New System.Net.WebClient()
        Dim VCItem As String = wc1.DownloadString(uri)
        Dim o As JObject = JObject.Parse(VCItem)
        Dim results As List(Of JToken) = o.Children().ToList


        Try
            For Each item As JProperty In results
                item.CreateReader()
                If item.Value.Type = JTokenType.Array Then
                    For Each subitem As JObject In item.Values


                        DataGridView2.Rows.Add(subitem("leftlabelcells_value"), (subitem("hideextra_number")), (subitem("rightpositioncells_number")))



                    Next
                End If
            Next
        Catch ex As Exception

        End Try

---

this is the data i get in my gridviewdata
Attachment 136409

there is some unwanted data in my gridview in the second and third coulmn, i need them to be like this :



EURUSD 49 51
USDJPY 16 84
GBPUSD 65 35
AUDUSD 41 59
USDCAD 45 55
USDCHF 57 43
EURJPY 58 42
EURGBP 17 83
EURCHF 28 72
GOLD 33 67
SILVER 8 92


in other words, i need to take off all the brackets and all the digits after the first comma or the first decimal point.
any help would be greatly appreciated !

Try this:

Code:

---

DataGridView2.Rows.Add(subitem("leftlabelcells_value"), Convert.ToInt32(subitem("hideextra_number").Replace(" ", String.Empty).Replace("[", String.Empty).Replace("]", String.Empty).Split(","c)(0)), Convert.ToInt32(subitem("rightpositioncells_number").Replace(" ", String.Empty).Replace("[", String.Empty).Replace("]", String.Empty.Split(","c)(0)))

---

Basically for the last two columns I replace all spaces, open brackets, and close brackets with an empty String and then I split the value by a comma and get the first item from the split.

Edit - I also convert the values returned from the process described to an Integer which will round all values down and remove the decimal place.

Quote:

---

Originally Posted by dday9

Try this:

Code:

---

DataGridView2.Rows.Add(subitem("leftlabelcells_value"), Convert.ToInt32(subitem("hideextra_number").Replace(" ", String.Empty).Replace("[", String.Empty).Replace("]", String.Empty).Split(","c)(0)), Convert.ToInt32(subitem("rightpositioncells_number").Replace(" ", String.Empty).Replace("[", String.Empty).Replace("]", String.Empty.Split(","c)(0)))

---

Basically for the last two columns I replace all spaces, open brackets, and close brackets with an empty String and then I split the value by a comma and get the first item from the split.

Edit - I also convert the values returned from the process described to an Integer which will round all values down and remove the decimal place.

---

thank you for your input dday, but unfortunetly i get a an error ; Too many arguments to 'Public Overloads Sub Replace(value As JToken)'.

Ok, I had thought that the value was a String value. What does the JToken class look like, is there a Text property?

Quote:

---

Originally Posted by dday9

Ok, I had thought that the value was a String value. What does the JToken class look like, is there a Text property?

---

hi again, the json file comes from webclient download, am using Newtonsoft.Json.Linq import

http://www.newtonsoft.com/json/help/...inq_JToken.htm

Code:

---

Dim uri As Uri = New Uri("https://api.import.io/store/connector/06a2a24f-6678-407e-8ad0-c7cbe24cf33c/_query?input=webpage/url:http%3A%2F%2Ffxowebtools.saxobank.com%2Fretail.html&&_apikey=d4c36ddd8f8d4a629bdf06e47993809233ac34a0ee817f4af327f7e016e1cb0921ba78b0da4d9be881051cbf3cf672f1425214d8e687e33559e24fdd17bc5a606e8ce1eac3d6e064593e3fcc34d23344")
        Dim wc1 As System.Net.WebClient = New System.Net.WebClient()
        Dim VCItem As String = wc1.DownloadString(uri)

---

{"offset":0,"results":[{"hideextra_number":[49.0,0.0],"hideextra_number/_source":["49","0"],"rightpositioncells_number":[51.0,0.0],"leftlabelcells_value":"EURUSD","rightpositioncells_number/_source":["51","0"]},{"hideextra_number":[16.0,0.0],"hideextra_number/_source":["16","0"],"rightpositioncells_number":[84.0,0.0],"leftlabelcells_value":"USDJPY","rightpositioncells_number/_source":["84","0"]},{"hideextra_number":[65.0,0.0],"hideextra_number/_source":["65","0"],"rightpositioncells_number":[35.0,0.0],"leftlabelcells_value":"GBPUSD","rightpositioncells_number/_source":["35","0"]},{"hideextra_number":[41.0,0.0],"hideextra_number/_source":["41","0"],"rightpositioncells_number":[59.0,0.0],"leftlabelcells_value":"AUDUSD","rightpositioncells_number/_source":["59","0"]},{"hideextra_number":45.0,"hideextra_number/_source":"45","rightpositioncells_number":[55.0,-1.0],"leftlabelcells_value":"USDCAD","rightpositioncells_number/_source":["55","-1"]},{"hideextra_number":[57.0,0.0],"hideextra_number/_source":["57","0"],"rightpositioncells_number":[43.0,0.0],"leftlabelcells_value":"USDCHF","rightpositioncells_number/_source":["43","0"]},{"hideextra_number":[58.0,0.0],"hideextra_number/_source":["58","0"],"rightpositioncells_number":[42.0,0.0],"leftlabelcells_value":"EURJPY","rightpositioncells_number/_source":["42","0"]},{"hideextra_number":[17.0,-1.0],"hideextra_number/_source":["17","-1"],"rightpositioncells_number":[83.0,1.0],"leftlabelcells_value":"EURGBP","rightpositioncells_number/_source":["83","1"]},{"hideextra_number":[28.0,0.0],"hideextra_number/_source":["28","0"],"rightpositioncells_number":[72.0,0.0],"leftlabelcells_value":"EURCHF","rightpositioncells_number/_source":["72","0"]},{"hideextra_number":[33.0,-5.0],"hideextra_number/_source":["33","-5"],"rightpositioncells_number":[67.0,5.0],"leftlabelcells_value":"GOLD","rightpositioncells_number/_source":["67","5"]},{"hideextra_number":8.0,"hideextra_number/_source":"8","rightpositioncells_number":[92.0,-1.0],"leftlabelcells_value":"SILVER","rightpositioncells_number/_source":["92","-1"]}],"cookies":[],"connectorVersionGuid":"c726af10-8618-34b7-70c2-ddbec38d34cf","connectorGuid":"06a2a24f-6678-407e-8ad0-c7cbe24cf33c","pageUrl":"http://fxowebtools.saxobank.com/retail.html","outputProperties":[{"name":"leftlabelcells_value","type":"STRING"},{"name":"hideextra_number","type":"DOUBLE"},{"name": "rightpositioncells_number","type":"DOUBLE"}]}

Quote:

---

Originally Posted by dday9

Ok, I had thought that the value was a String value. What does the JToken class look like, is there a Text property?

---

I doubt it... it looks based on its use that JToken is an enumerated type... it's checking if the type of the value of the item is an array or not...
I think we'd need to know a bit more about what each item is (beyond being a JProperty) and what it's .Value is like...

-tg

[49.0,0.0] -- means the value is an array... two elements in this case...

so subitem("rightpositioncells_number") gives you the whole array... you only want the first one:
subitem("rightpositioncells_number")(0)

should give you the first element of the array.

-tg

Do the same thing that I'm doing on add .Value(GetType(String)):

Code:

---

DataGridView2.Rows.Add(subitem("leftlabelcells_value"), _
    Convert.ToInt32(subitem("hideextra_number").Value(GetType(String)).Replace(" ", String.Empty).Replace("[", String.Empty).Replace("]", String.Empty).Split(","c)(0)), _
    Convert.ToInt32(subitem("rightpositioncells_number").Value(GetType(String)).Replace(" ", String.Empty).Replace("[", String.Empty).Replace("]", String.Empty.Split(","c)(0)))

---

Quote:

---

Originally Posted by dday9

Do the same thing that I'm doing on add .Value(GetType(String)):

Code:

---

DataGridView2.Rows.Add(subitem("leftlabelcells_value"), _
    Convert.ToInt32(subitem("hideextra_number").Value(GetType(String)).Replace(" ", String.Empty).Replace("[", String.Empty).Replace("]", String.Empty).Split(","c)(0)), _
    Convert.ToInt32(subitem("rightpositioncells_number").Value(GetType(String)).Replace(" ", String.Empty).Replace("[", String.Empty).Replace("]", String.Empty.Split(","c)(0)))

---

---

i get another error :

Error BC32050 Type parameter 'T' for 'Public Overridable Overloads Function Value(Of T)(key As Object) As T' cannot be inferred.

Attachment 136417

Quote:

---

Originally Posted by techgnome

[49.0,0.0] -- means the value is an array... two elements in this case...

so subitem("rightpositioncells_number") gives you the whole array... you only want the first one:
subitem("rightpositioncells_number")(0)

should give you the first element of the array.

-tg

---

your code works with only values that has 0 on the second or the first element such as ;

[49.0,0.0]
[51.0,0.0]
[72.0,0.0]

but if there is a value on the second element, nothing shows up in the grid :

[83.0,1.0]
[92.0,-1.0]
[33.0,-5.0]

Quote:

---

Originally Posted by hyperbell

i get another error :

Error BC32050 Type parameter 'T' for 'Public Overridable Overloads Function Value(Of T)(key As Object) As T' cannot be inferred.

Attachment 136417

---

Try changing GetType(String) to Of String.

Quote:

---

Originally Posted by dday9

Try changing GetType(String) to Of String.

---

thank you for trying, i am stuck with this code for the past 2 days.

the new change does not show anymore errors but in runtime when its fetching data, it stops and get a new error :


An unhandled exception of type 'System.InvalidCastException' occurred in Newtonsoft.Json.dll
Additional information: Cannot cast Newtonsoft.Json.Linq.JArray to Newtonsoft.Json.Linq.JToken.