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

You need to use SqlClient or equivalent. You can't just type an SQL query in a .NET application... — WSC
– WSC, Commented Sep 2, 2021 at 12:39
Could you post your actual VB.NET attempt? If you really did try to "just copy it in" that's never going to work. — Broots Waymb
– Broots Waymb, Commented Sep 2, 2021 at 12:41
@WSC there is an SQLClient file, but since I am not fimiliar with this, i dont really understand much of it. — Martin
– Martin, Commented Sep 2, 2021 at 12:41
Post the code where you connect to the database. It seems like you haven't done even the very basic of educating yourself about how to do this. Please do some research... Here's a good starting place: mssqltips.com/sqlservertip/5677/… — WSC
– WSC, Commented Sep 2, 2021 at 12:43

Olivier Jacot-Descombes · Accepted Answer · 2021-09-02 13:04:57Z

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")

Collectives™ on Stack Overflow

SQL Select query in VB.NET

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related