different cells selected depending on query result

Question

Folks,

please help.

I need a vb.net code to select cells from a mysql db. i have this so far which kind of works but it returns the whole column of a or b etc but i need only the 5 cells for each result. so for large, upto 4, north i would have

ID  7
thrist  6
panch   5
manchot 5
idly    4

this is probably so unclear but if anyone can help it would be much appreciated!

enter image description here

Using conn As New MySqlConnection(myConnectionString)
    Dim sqllayer As String = "SELECT Mid(id, 2, 2) FROM sheet1;"
    Dim dalayer As New MySqlDataAdapter(sqllayer, conn)
    Dim dslayer As New DataTable
    dalayer.Fill(dslayer)

    If dslayer.Rows.Count > 0 Then
        nrlayer = dslayer.Rows(0)(0).ToString()
    Else
    End If
End Using


Select Case Size
    case large
        select case area
            Case "south"
                Select Case count
                    Case < 5
                        Select Case area
                            Case "south"
                                Select Case letter
                                    Case 1
                                        Return "a"
                                    Case 2
                                        Return "b"
                                    Case 3
                                        Return "c"
                                    Case 4
                                        Return "c"
                                    Case 5
                                        Return "d"
                                    Case 6
                                        Return "e"
                                    Case 7
                                        Return "f"
                                    Case 8
                                        Return "g"
                                    Case 9
                                        Return "h"
                                    Case 10
                                        Return "i"
                                End Select
                        End Select
            End Select
    Case "small cell"
End Select

Why are you selecting by area twice? There is a mismatch between `Select Case` and `End Select`. From the little information here it's hard to tell, but shouldn't you use a [SQL CASE Expression](https://www.w3schools.com/sql/func_mysql_case.asp) in your SQL query? Like `SELECT CASE WHEN THEN a ELSE b END AS result FROM myTable` or something like this. — Olivier Jacot-Descombes, Jul 22 '23 at 13:42
Yeah the select case when etc expression is a work in progress, actually that part is not the issue, it's the variables that I'm having an issue getting right, the sql outputs the whole column — carl murr, Jul 22 '23 at 14:14
You didn't show us the SQL. So, we cannot give you more details. Using the CASE-expression in the SQL would probably solve the issue. — Olivier Jacot-Descombes, Jul 22 '23 at 14:30
this is the sql, just a simple select query Using conn As New MySqlConnection(myConnectionString) Dim sqllayer As String = "SELECT Mid(id, 2, 2) FROM sheet1;" Dim dalayer As New MySqlDataAdapter(sqllayer, conn) Dim dslayer As New DataTable dalayer.Fill(dslayer) but the case select i have returns the whole column not just the relevant 5 cells — carl murr, Jul 22 '23 at 17:15
I suppose there could be a case expression that I don't know of that could return the correct, data, as said, currently whatever column is selected the whole column is returned. — carl murr, Jul 22 '23 at 18:14
@carlmurr You're meant to add conditions in the SQL so that it only fetches the data you need. [Is it possible to GROUP BY multiple columns using MySQL?](https://stackoverflow.com/q/1841426/1115360) and [MySQL SELECT COUNT GROUP BY](https://linuxhint.com/mysql-select-count-group-by/) might help. — Andrew Morton, Jul 22 '23 at 19:55
Thanks Andrew, eh would you be able to expand on that? This is all quite new to me! — carl murr, Jul 22 '23 at 22:04
Ah but that's the problem, I need all the rows in the sql and then select the relevant rows from the select cases — carl murr, Jul 22 '23 at 22:20
Clearly I didn't describe my needs here. There's a loop so each run is different so I can't have an sql query that pulls the 5 lines I want. I need to select the 5 lines from the sql query each time — carl murr, Jul 23 '23 at 10:35
Soooo you want to loop over the dslayer table, extract the values of key fields into variables then do some conditional processing based on the values of those variables? — Hursey, Jul 23 '23 at 20:37

score 0 · Accepted Answer · answered Jul 24 '23 at 21:07

The trick is going to be using a for each loop to iterate over the DataTable Rows collection then. For this you probably don't need to necessarily assign the table field values to variables, there are pros and cons to that (Less variables to maintain vs longer table names).

Also, worth checking your Sql Query, as it is now you will only retrieve a single column that will have a calculated name. Looking at what code you've got so far, suspect you're probably are expecting more fields. I've tweaked it in this so you've got a known name or alternatively you can use the column index.

Using conn As New MySqlConnection(myConnectionString)
    Dim sqllayer As String = "SELECT Mid(id, 2, 2) as Area FROM sheet1;"
    Dim dalayer As New MySqlDataAdapter(sqllayer, conn)
    Dim dslayer As New DataTable
    dalayer.Fill(dslayer)

    If dslayer.Rows.Count > 0 Then
        nrlayer = dslayer.Rows(0)(0).ToString()
    Else
    End If
End Using


For Each row as DataTableRow in dsLayer.Rows
   Dim area as String = row("Area").ToString()
   Select Case area
   Case "South"
       ....
   Case "North"
       ....
   Case Else
       ....
   End Select
Next

You can also review the official docs here which might give you a bit more clarity.

Also need to be aware, string comparisons are case sensitive, sometimes easiest way to deal with that is to force both sides of the comparison to a known case. eg .ToUpper()

different cells selected depending on query result

1 Answers1