0

im trying to figure out how to properly use a select query in vb.net. The query im trying to use uses a join function, to join multiple tables. I also have a where filter in there. Below is the query as it works in MS SQL Server Management Studio. Can anyone here push me in to the right direction or know of a syntax/format I can use to perform this query?

select th.*, odo.OptionCode 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' order by th.SchedID, th.UnitID, th.MasterKey
Improve this question
2
  • Usual way to work with sql in .net is using the entity framework Commented Sep 2, 2021 at 14:39
  • You already have an answer to the problem in your previous question: SQL Select query in VB.NET. Commented Sep 2, 2021 at 15:39

1 Answer 1

Reset to default
0

This is the way it is done in ADO.net. You will need at the top of your file.

Imports System.Data.SqlClient

Something is wrong with this line of the sqlString. You wouldn't want a space after a period.

join FVMASTER..MasterPartOptions mpo on mpo.Code=odo.OptionCode and mpo.

You should be able to copy and paste the Select statement from SSMS directly to vb.net code.

Separate your data access code from the user interface code. It seems all your parameters are hard coded.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim dt = GetThData() DataGridView1.DataSource = dt End Sub Private Function GetThData() As DataTable Dim sqlString = "select th.*, odo.OptionCode 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' order by th.SchedID, th.UnitID, th.MasterKey;" Dim dt As New DataTable Using cn As New SqlConnection("Your connection string"), cmd As New SqlCommand(sqlString, cn) cn.Open() Using reader = cmd.ExecuteReader dt.Load(reader) End Using End Using Return dt End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This might be what I'm after. The problem i now have is that DataGridView1 is not declared, do you know how i can fix this?
when i remove the where filter, it does do something. I can see that the application uses a lot of memory and the app gets really slow, probably because its requesting a ton of data. The thing is that it never shows the table with the data.
If you query runs in SSMS, it will run in vb.net. When you say a ton of data, how many rows are returned in SSMS? As far a DataGridView1 is concerned, add the control to your form. That is the default name of the first grid you add. Name it whatever you wish and change the name in the code.
Did you fix the line in the Select that I mentioned in my answer? The line that ends with a period.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.