SQL Select query in VB.NET

Question

I want to pull some stuff out of a database using a select query. I have the query i need already written in SQL Server management studio and it works. I tried just copy it in but it doesn't work. I have some other queries that do work and i tried to make it formatted like those, but it'll just see it as string since its between "" those things. Below is the query, it uses the join function that doesn't appear to be recognized by vb.net. the query also uses certain tables from other parts of the database, those also dont seem to get recognized.

the query:

select th.Thickness, count(th.Thickness)
from dbname..trackinghistory th
join dbname..OrderDetailOptions odo on odo.odKey=th.odKey
join dbname..MasterPartOptions mpo on mpo.Code=odo.OptionCode and mpo.[Group]=odo.optiongroup 
and mpo.QuestionKey='KGLASS' and OptionType=5
where th.DateTime>DATEADD(DAY,-4,getdate()) and th.Code='__A__' and th.StationID='HO4' and 
left(odo.OptionCode,1) = 'H'
group by th.Thickness

I think i just need a push in the right direction, can someone here help me with how to properly format these type of queries?

my VB.Net code:

Sub SetButtonColor()

    btnIsClicked = False

    Dim iWeek As Integer = tbWeek.Text
    Dim iYear As Integer = tbYear.Text

    If sql.hasconnection And iWeek <> 0 Then
        Dim dtimeStartDate As DateTime = GetWeekStartDate(tbWeek.Text, tbYear.Text) 'get the startdate from the textboxes week and year
        Dim dtimeEndDate As DateTime = DateAdd(DateInterval.Day, 7, dtimeStartDate) 'add 7 days to the startdate to set the enddate


        sql.runquery("SELECT th.Thickness, count(th.Thickness)" +
                        "from FVMASTER..trackinghistory th" +
                        "join FVMASTER..OrderDetailOptions odo on odo.odKey=th.odKey" +
                        "join FVMASTER..MasterPartOptions mpo on mpo.Code=odo.OptionCode and mpo.[Group]=odo.optiongroup and mpo.QuestionKey='KGLASS' and OptionType=5" +
                        "where th.DateTime>DATEADD(DAY,-4,getdate()) and th.Code='__A__' and th.StationID='HO4' and left(odo.OptionCode,1) = 'H'" +
                        "group by th.Thickness")


        If sql.sqldataset.Tables(0).Rows.Count > 0 Then
            For Each row As DataRow In sql.sqldataset.Tables(0).Rows
                Select Case row("Thickness")
                    Case 3
                        btn3.BackColor = Color.Red
                    Case 4
                        btn4.BackColor = Color.Red
                    Case 5
                        btn5.BackColor = Color.Red
                    Case 6
                        btn6.BackColor = Color.Red
                    Case 8
                        btn8.BackColor = Color.Red
                    Case 10
                        btn10.BackColor = Color.Red
                    Case 12
                        btn12.BackColor = Color.Red
                    Case 15
                        btn15.BackColor = Color.Red
                    Case 19
                        btn19.BackColor = Color.Red
                    Case 24
                        btn24.BackColor = Color.Red
                End Select
            Next
        End If

end sub

i have for now just pasted in the query, i know that that wont work

Answer 1

The problem are the missing spaces in the SQL string. Just because the concatentated strings are on different lines, this does not add line breaks to the string.

E.g.,

"from FVMASTER..trackinghistory th" +
"join FVMASTER..OrderDetailOptions odo on odo.odKey=th.odKey"

results in

"from FVMASTER..trackinghistory thjoin FVMASTER..OrderDetailOptions odo on odo.odKey=th.odKey"

As you can see, you get a thjoin in there, which should be th join.

Just write it as a single multiline string (which now includes the line breaks)

sql.runquery("SELECT th.Thickness, count(th.Thickness)
    from FVMASTER..trackinghistory th
    join FVMASTER..OrderDetailOptions odo on odo.odKey=th.odKey
    join FVMASTER..MasterPartOptions mpo on mpo.Code=odo.OptionCode and mpo.[Group]=odo.optiongroup and mpo.QuestionKey='KGLASS' and OptionType=5
    where th.DateTime>DATEADD(DAY,-4,getdate()) and th.Code='__A__' and th.StationID='HO4' and left(odo.OptionCode,1) = 'H'
    group by th.Thickness")

Alternatively, you can keep your original approach and add the missing spaces at the line end

sql.runquery("SELECT th.Thickness, count(th.Thickness) " +
    "from FVMASTER..trackinghistory th " +
    "join FVMASTER..OrderDetailOptions odo on odo.odKey=th.odKey " +
    "join FVMASTER..MasterPartOptions mpo on mpo.Code=odo.OptionCode and mpo.[Group]=odo.optiongroup and mpo.QuestionKey='KGLASS' and OptionType=5 " +
    "where th.DateTime>DATEADD(DAY,-4,getdate()) and th.Code='__A__' and th.StationID='HO4' and left(odo.OptionCode,1) = 'H' " +
    "group by th.Thickness")