dcsimg
Results 1 to 17 of 17

Thread: Should this be programmed with VB or SQL ?.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Should this be programmed with VB or SQL ?.

    Hello everyone,

    I have a situation here and need your consultation regarding it.

    I need to make a table .. this table is devided in a way that the headers of the columns are the key values. There is some external input that is going to be taken and compared with the key values and based on it actions are taken.

    To clarify this, I'll give a small example. Let's say we got an obese guy who wants to lose weight and the doctor made for him a schedule he should commit to in order to lose weight. Let's say the schedule says that this guy needs to run 5 KM at start in order to reach the next level which is to run 10 KM and if he's running less than 5 KM, he should commit to specific meals and the meals change when he finishes 5 KM running and he should then run another 10 KM and so on. Suppose the guy ran 5 KM on the running machine and he reached 5 KM, then the machine should compare the result with the table it has and see if he exceeded 5 KM in order to change his meals. This kind of table or schedule should be programmed with which language ?. VB or SQL?.

    Looking to see your replies soon.


    Thanks..

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,878

    Re: Should this be programmed with VB or SQL ?.

    I'd suggest not doing it that way. Instead make the exercise value a row, not a field name.

    MealsTable

    Code:
    Exercise  |  Meals
    --------------------------------------------------
    5K             some meal for 5k
    10K           the meal for 10k

    This would give you much more flexibility for Adding/Edit/Delete different exercises.

    As for "Should this be programmed with VB or SQL ?."

    You don't write program code with SQL, you only use it to interact with your database ( Insert, Retrieve, Delete records ). You write the program code using VB.
    Last edited by wes4dbt; Dec 29th, 2018 at 03:45 PM.

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,805

    Re: Should this be programmed with VB or SQL ?.

    Quote Originally Posted by wes4dbt View Post
    As for "Should this be programmed with VB or SQL ?."

    You don't write program code with SQL, you only use it to interact with your database ( Insert, Retrieve, Delete records ). You write the program code using VB.
    I write 100% of my "business logic" in stored procedures. MS SQL has a powerful programming language (T-SQL) and I've used it to calculate payrolls on town halls, schedule students into classes for high school - adjudicate medical claims for payment. All my backend code on the web server is VB.Net, but it's all generic - not application specific. True CRUD with lots of added features and bells and whistles. The business layer is in STORED PROCEDURES in the database. I had a decade of my front end being VB6 and now for the past 10 years my new clients all use my web app (rich-internet application). Most of the reporting and output displays easily ported since they were all written in the database in SPROCS (as I migrate my VB6 clients to the web app).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Hello wes4dbt,

    Thanks so much for your reply.

    I need to do it the way I explained for a reason. The table I actually have is like this:
    5KM | 10KM | 15KM | 20KM
    ---------------------------------------------------
    Sausage * *
    Salad * * * *
    Yogurt * *
    Fruits * *
    Ham *
    Cheese *
    Milk * * * *
    Juice *

    So .. is it easier to program this table with VB ?. FYI .. the program that measures the number of KM is written with VB and I need to link that measurement program with the table I'm going to make and compare the measured value with the number of KM in this table. I also need to filter the components like for example if the measurement is less than 5 KM, I need the program to return back only the components under the 5 KM which are (Sausage, Salad, Cheese, Milk, and Juice) only.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Hello szlamany,

    Thanks for your reply.

    I need to do it the way I explained for a reason. The table I actually have is like this:
    5KM | 10KM | 15KM | 20KM
    ---------------------------------------------------
    Sausage * *
    Salad * * * *
    Yogurt * *
    Fruits * *
    Ham *
    Cheese *
    Milk * * * *
    Juice *

    So .. is it easier to program this table with VB ?. FYI .. the program that measures the number of KM is written with VB and I need to link that measurement program with the table I'm going to make and compare the measured value with the number of KM in this table. I also need to filter the components like for example if the measurement is less than 5 KM, I need the program to return back only the components under the 5 KM which are (Sausage, Salad, Cheese, Milk, and Juice) only.

    Your opinion on this ?.

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,805

    Re: Should this be programmed with VB or SQL ?.

    That is how you want the "output" to display in the VB app.

    To create this in a database you would want a PRODUCT table listing each product. Then you would want a SCALE table to list the scales.

    Each table would have a numeric primary key and the description as the second field in the table.

    Imagine Sausage is ProductId=1, and it happens for 5KM and 10KM (ScaleId of 1 and 2).

    You need a third table that lists "each" ProductId and ScaleId that is in "action".

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Quote Originally Posted by szlamany View Post
    That is how you want the "output" to display in the VB app.

    To create this in a database you would want a PRODUCT table listing each product. Then you would want a SCALE table to list the scales.

    Each table would have a numeric primary key and the description as the second field in the table.

    Imagine Sausage is ProductId=1, and it happens for 5KM and 10KM (ScaleId of 1 and 2).

    You need a third table that lists "each" ProductId and ScaleId that is in "action".
    ٍSo .. I need to make the table in SQL and do the comparison between the measurement value and the KMs in VB then link between the two. This what you meant ?. Or I do the comparison in SQL as well and then send the result to the VB app ?.

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,805

    Re: Should this be programmed with VB or SQL ?.

    You EXECUTE a SQL SELECT statement against your database from the VB side and it returns a series of rows with columns.

    Try this in SSMS (Management Studio)

    Code:
    Create Table #Products (ProdId int, ProdName varchar(100))
    Create Table #Scales (ScaleId int, ScaleName varchar(100))
    Create Table #ProductScales (ProdId int, ScaleId int)
    
    Insert into #Products values (1, 'Sausage')
    Insert into #Products values (2, 'Salad')
    Insert into #Products values (3, 'Yogurt')
    
    Insert into #Scales values (1, '5km')
    Insert into #Scales values (2, '10km')
    Insert into #Scales values (3, '15km')
    Insert into #Scales values (4, '20km')
    
    Insert into #ProductScales values (1,1)
    Insert into #ProductScales values (1,2)
    Insert into #ProductScales values (2,1)
    Insert into #ProductScales values (2,2)
    Insert into #ProductScales values (2,3)
    Insert into #ProductScales values (2,4)
    Insert into #ProductScales values (3,3)
    Insert into #ProductScales values (3,4)
    
    Select PR.ProdName
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=1) Then '*' Else '' End "5km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=2) Then '*' Else '' End "10km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=3) Then '*' Else '' End "15km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=4) Then '*' Else '' End "20km"
    	From #Products PR
    	Left Join #ProductScales PS on PS.ProdId=PR.ProdId
    	Group by PR.ProdId, PR.ProdName
    	Order by PR.ProdId
    
    Drop Table #Products
    Drop TAble #Scales
    Drop Table #ProductScales
    Will return the image shown below
    Name:  ps.JPG
Views: 65
Size:  15.7 KB

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Quote Originally Posted by szlamany View Post
    You EXECUTE a SQL SELECT statement against your database from the VB side and it returns a series of rows with columns.

    Try this in SSMS (Management Studio)

    Code:
    Create Table #Products (ProdId int, ProdName varchar(100))
    Create Table #Scales (ScaleId int, ScaleName varchar(100))
    Create Table #ProductScales (ProdId int, ScaleId int)
    
    Insert into #Products values (1, 'Sausage')
    Insert into #Products values (2, 'Salad')
    Insert into #Products values (3, 'Yogurt')
    
    Insert into #Scales values (1, '5km')
    Insert into #Scales values (2, '10km')
    Insert into #Scales values (3, '15km')
    Insert into #Scales values (4, '20km')
    
    Insert into #ProductScales values (1,1)
    Insert into #ProductScales values (1,2)
    Insert into #ProductScales values (2,1)
    Insert into #ProductScales values (2,2)
    Insert into #ProductScales values (2,3)
    Insert into #ProductScales values (2,4)
    Insert into #ProductScales values (3,3)
    Insert into #ProductScales values (3,4)
    
    Select PR.ProdName
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=1) Then '*' Else '' End "5km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=2) Then '*' Else '' End "10km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=3) Then '*' Else '' End "15km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=4) Then '*' Else '' End "20km"
    	From #Products PR
    	Left Join #ProductScales PS on PS.ProdId=PR.ProdId
    	Group by PR.ProdId, PR.ProdName
    	Order by PR.ProdId
    
    Drop Table #Products
    Drop TAble #Scales
    Drop Table #ProductScales
    Will return the image shown below
    Name:  ps.JPG
Views: 65
Size:  15.7 KB
    Thank you so so much for the help .. really appreciated !. The picture is way clearer now. Hope you're here if I face any other problem .

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Quote Originally Posted by szlamany View Post
    You EXECUTE a SQL SELECT statement against your database from the VB side and it returns a series of rows with columns.

    Try this in SSMS (Management Studio)

    Code:
    Create Table #Products (ProdId int, ProdName varchar(100))
    Create Table #Scales (ScaleId int, ScaleName varchar(100))
    Create Table #ProductScales (ProdId int, ScaleId int)
    
    Insert into #Products values (1, 'Sausage')
    Insert into #Products values (2, 'Salad')
    Insert into #Products values (3, 'Yogurt')
    
    Insert into #Scales values (1, '5km')
    Insert into #Scales values (2, '10km')
    Insert into #Scales values (3, '15km')
    Insert into #Scales values (4, '20km')
    
    Insert into #ProductScales values (1,1)
    Insert into #ProductScales values (1,2)
    Insert into #ProductScales values (2,1)
    Insert into #ProductScales values (2,2)
    Insert into #ProductScales values (2,3)
    Insert into #ProductScales values (2,4)
    Insert into #ProductScales values (3,3)
    Insert into #ProductScales values (3,4)
    
    Select PR.ProdName
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=1) Then '*' Else '' End "5km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=2) Then '*' Else '' End "10km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=3) Then '*' Else '' End "15km"
    		,Case When Exists(Select * From #ProductScales PS Where PS.ProdId=PR.ProdId and PS.ScaleId=4) Then '*' Else '' End "20km"
    	From #Products PR
    	Left Join #ProductScales PS on PS.ProdId=PR.ProdId
    	Group by PR.ProdId, PR.ProdName
    	Order by PR.ProdId
    
    Drop Table #Products
    Drop TAble #Scales
    Drop Table #ProductScales
    Will return the image shown below
    Name:  ps.JPG
Views: 65
Size:  15.7 KB
    I just got a one last question for now .. how do you link this database you made with a VB program which will return me the measurement value and I should take this value from the VB app and compare it with the database you made so the DB would reply with the output and show it in the VB app ?.
    Last edited by MAGMAM; Dec 31st, 2018 at 01:20 AM.

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,878

    Re: Should this be programmed with VB or SQL ?.

    You need to use ADO .Net. This would be a good start http://www.vbforums.com/showthread.p...ses&highlight=

    The problem is there's more than one way it interact with the database. For a start, here is a very basic example,

    Code:
    Imports System.Data.SqlClient
    Public Class Form6
        Private con As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BooksDB.mdf;Integrated Security=True;Connect Timeout=30")
        Private da As New SqlDataAdapter("Select BookId, BookName, Author from Books Order By BookName", con)
        Private dt As New DataTable
        Private cmdBldr As SqlCommandBuilder
        Private bindsource As New BindingSource
    
        Private Sub Form6_Load(sender As Object, e As EventArgs) Handles Me.Load
    
            Try
                con.Open()
                da.Fill(dt)
    
                cmdBldr = New SqlCommandBuilder(da)
    
                bindsource.DataSource = dt
                Me.DataGridView1.DataSource = bindsource
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub SaveDataButton_Click(sender As Object, e As EventArgs) Handles SaveDataButton.Click
    
            bindsource.EndEdit()
            da.Update(dt)
    
        End Sub
    
    End Class
    This retrieves data from a table in the database, loads the data into a DataGridView, where you can Add/Edit/Delete, then you can save the changes back to the database table.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Quote Originally Posted by wes4dbt View Post
    You need to use ADO .Net. This would be a good start http://www.vbforums.com/showthread.p...ses&highlight=

    The problem is there's more than one way it interact with the database. For a start, here is a very basic example,

    Code:
    Imports System.Data.SqlClient
    Public Class Form6
        Private con As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BooksDB.mdf;Integrated Security=True;Connect Timeout=30")
        Private da As New SqlDataAdapter("Select BookId, BookName, Author from Books Order By BookName", con)
        Private dt As New DataTable
        Private cmdBldr As SqlCommandBuilder
        Private bindsource As New BindingSource
    
        Private Sub Form6_Load(sender As Object, e As EventArgs) Handles Me.Load
    
            Try
                con.Open()
                da.Fill(dt)
    
                cmdBldr = New SqlCommandBuilder(da)
    
                bindsource.DataSource = dt
                Me.DataGridView1.DataSource = bindsource
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
            End Try
        End Sub
    
        Private Sub SaveDataButton_Click(sender As Object, e As EventArgs) Handles SaveDataButton.Click
    
            bindsource.EndEdit()
            da.Update(dt)
    
        End Sub
    
    End Class
    This retrieves data from a table in the database, loads the data into a DataGridView, where you can Add/Edit/Delete, then you can save the changes back to the database table.
    Thanks for the help, bro. I will try this one when I'm done with the database.

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,878

    Re: Should this be programmed with VB or SQL ?.

    Another method to use ADO .Net is with Stored Procedures like szlamany mentioned. It also sounds like your going to need to use a Parameter so you only retrieve the values related to what's entered in the textbox.
    Here, https://support.microsoft.com/en-us/...o-net-and-visu

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Quote Originally Posted by wes4dbt View Post
    Another method to use ADO .Net is with Stored Procedures like szlamany mentioned. It also sounds like your going to need to use a Parameter so you only retrieve the values related to what's entered in the textbox.
    Here, https://support.microsoft.com/en-us/...o-net-and-visu
    Not sure if ADO .Net is different than VB cuz I have no idea about ADO .Net to be honest, but I'll check these links and learn about it.

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Quote Originally Posted by wes4dbt View Post
    Another method to use ADO .Net is with Stored Procedures like szlamany mentioned. It also sounds like your going to need to use a Parameter so you only retrieve the values related to what's entered in the textbox.
    Here, https://support.microsoft.com/en-us/...o-net-and-visu
    Perhaps the linking code would be this way in the VB program ?.

    Code:
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand("Select * from Products", connection)
        command.Connection.Open()
        SqlDataReader reader = command.ExecuteReader()
        Dim dt As New DataTable()
        dt.Load(reader)
        command.connection.Close()
        myListBox.ItemSource = dt
     End Using
    Or .. like this ?.

    Code:
    SqlConnection1.Open()
    using table As DataTable = New DataTable
      using command as SqlCommand = New SqlCommand("SELECT blah blah", SqlConnection1)
        using adapter As SqlDataAdapter = new SqlDataAdapter(command)
          adapter.Fill(table)
        end using
      end using
    
      for each row As DataRow in table.Rows
        '  add each listbox item
        listbox1.Items.Add(row("column name"))
      next
    end using
    SqlConnection1.Close()

    Any of these are good ?.

  16. #16
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,878

    Re: Should this be programmed with VB or SQL ?.

    There are problems with your examples. Why create a datareader and then load it into a datatable? Just create a datatable.
    Code:
                Using con1 As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BooksDB.mdf;Integrated Security=True;Connect Timeout=30"),
                    da1 As New SqlDataAdapter("Select BookId, BookName, Author from Books Order By BookName", con1), dt1 As New DataTable
                    con1.Open()
                    da1.Fill(dt1)
                    Me.DataGridView1.DataSource = dt1
                End Using
    Don't know why your using a ListBox (a listbox doen't have an ItemSource method), a DataGridView seems to be more appropriate. Remember that this method would only be good for displaying data, not for modifying because End Using will dispose of the objects.

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Dec 2018
    Posts
    20

    Re: Should this be programmed with VB or SQL ?.

    Quote Originally Posted by wes4dbt View Post
    There are problems with your examples. Why create a datareader and then load it into a datatable? Just create a datatable.
    Code:
                Using con1 As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BooksDB.mdf;Integrated Security=True;Connect Timeout=30"),
                    da1 As New SqlDataAdapter("Select BookId, BookName, Author from Books Order By BookName", con1), dt1 As New DataTable
                    con1.Open()
                    da1.Fill(dt1)
                    Me.DataGridView1.DataSource = dt1
                End Using
    Don't know why your using a ListBox (a listbox doen't have an ItemSource method), a DataGridView seems to be more appropriate. Remember that this method would only be good for displaying data, not for modifying because End Using will dispose of the objects.
    It's not mandatory for me to make the data modifiable .. displaying it is just fine. But, I'll go with your way and make it an option just in case I need it later. Thanks so much for your support.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width