dcsimg
Results 1 to 18 of 18

Thread: Find what materials were allocated for previous orders--SQL 2008, VS 2015

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    9

    Find what materials were allocated for previous orders--SQL 2008, VS 2015

    I work for a company that sells metal.
    There are many different specs to what the metal can be.
    example: several coatings types to choose from for front and/or back, rolls or sheets of metal, smooth or flat, several polish types, number of cuts, etc.
    When an order is entered, the specs are entered in by the sales teams. These requested specs are distributed to 4 different tables, in (2008 SQL Server) (OE_ORDER, OE_LINE, OE_ALLOCATIONS, OE_CUTTING.)

    I was told to assign each unique set of specs an ID(ID_No), and record the first day it was entered (Initial_DT) and the last time the spec was ordered(Last_Dt) and how many times this particular set of specs was ordered (Times_Used) and put all the information into 4 different tables that was created by a team member. (currently the tables do not have data)

    My team member wants me to create a VS 2015 program where the user can enter in an order number (ord_no) into a text box and click a button. The program will look in the 4 new tables to see if that combination of specs and/or ord_no was already requested.

    Spreadsheet with all tables is attached.
    Tables For Project.zip

    If the specs are already in the new tables then I need to add 1 to my Times_Used column and change Last_Dt and record the ord_no, line_no, and cus_no in the new tables. And display a message to the user "This configuration has been used 'x' amount of times on 'x' First_Dt, 'x' Last_Dt, on these 'x' ord_no's.

    If it has not been requested it will go out and look at the 4 tables where the sales order was entered and assign an ID_No to that new set of specific specs and distribute the specs to 4 tables he created that currently do not contain any data and a message will pop up saying "This is the first time these specs have been requested"

    My question is: Is there an easier way to do this? For example, can the user put in an order number and when he clicks the button it could give him the results I want? By using COUNT, or DISTINCT?


    Thank you

  2. #2
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,223

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    Hi,

    just did a test with an Access DB, it will check if the ID is there, if not it will insert

    here some Code
    Code:
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind1.mdb")
    
            Dim sql As String = "Select * From TestTable Where ID =" & TextBox1.Text
            Dim reader As OleDbDataReader
            Dim cmd As New OleDbCommand
            cmd.Connection = con
            con.Open()
            cmd.CommandText = sql
           
            reader = cmd.ExecuteReader
            If reader.HasRows = True Then
                MsgBox("Exist")
            Else
                MsgBox(" doesn't Exist!")
            End If
        End Sub
    
        Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind1.mdb")
            Dim cmd As OleDbCommand
            Dim dr As OleDbDataReader
            Dim sSql As String
            Dim iCount As Long
            con.Open()
            sSql = "Select * From TestTable Where ID =" & TextBox1.Text
            cmd = New OleDbCommand(sSql, con)
            dr = cmd.ExecuteReader()
    
            If Not dr.HasRows Then
                'ID is not in Database
                'so Insert
                sSql = "Insert into TestTable ([ID],[myName]) values ('" + TextBox1.Text + "','" + TextBox2.Text + "')"
                cmd = New OleDbCommand(sSql, con)
                iCount = cmd.ExecuteNonQuery
                MsgBox(iCount & " record added !")
            Else
                'ID is in Database so show MsgBox
                MsgBox(" Record exists")
            End If
            con.Close()
            con = Nothing
        End Sub
    HTH

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    547

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    So, you are wanting to populate 4 tables from data that you already have from 3 tables? I think there are some design considerations to be made with your solution. You may want to consider using datarelations and column expressions to produce the results you want. Here is a simple example.

    Code:
    Public Class RelationExample
        Dim DS As New DataSet
        Dim ParentBS, ChildBS As New BindingSource
    
        Private Sub RelationExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With DS
                .Tables.Add(New DataTable With {.TableName = "ParentTable"})
                .Tables.Add(New DataTable With {.TableName = "ChildTable"})
    
                ''example data
                ''Parent DATA
                With .Tables("ParentTable")
                    .Columns.Add("ParentID", GetType(System.String))
                    For i = 0 To 5
                        .Rows.Add(i.ToString)
                    Next
                End With
    
                ''Child DATA
                With .Tables("ChildTable")
                    .Columns.Add("ParentID", GetType(System.String))
                    .Columns.Add("SomeNumber", GetType(Double))
                    For Each Drow As DataRow In DS.Tables("ParentTable").Rows
                        For i = 0 To 20
                            .Rows.Add(Drow("SomeNumber"), Int(Rnd() * 5) + 1)
                        Next
                    Next
                End With
            End With
    
            ''Create relations
            Dim ParentCol As DataColumn = DS.Tables("ParentTable").Columns("ParentID")
            Dim ChildCol As DataColumn = DS.Tables("ChildTable").Columns("ParentID")
    
            DS.Relations.Add("MyRelation", ParentCol, ChildCol, False)
            ParentBS.DataSource = DS.Tables("ParentTable")
    
            ''Add columns to parent table for aggregates
            With DS.Tables("ParentTable")
                .Columns.Add("SumSomeNumber", GetType(Double), "Sum(Child.SomeNumber)")
                .Columns.Add("CountSomeNumber", GetType(Double), "Count(Child.SomeNumber)")
                .Columns.Add("AvgSomeNumber", GetType(Double), "Avg(Child.SomeNumber)")
                .Columns.Add("MinSomeNumber", GetType(Double), "Min(Child.SomeNumber)")
                .Columns.Add("MaxSomeNumber", GetType(Double), "Max(Child.SomeNumber)")
            End With
    
            DataGridView1.DataSource = ParentBS
    
            With ChildBS
                .DataSource = ParentBS
                .DataMember = "MyRelation"
            End With
    
            DataGridView2.DataSource = ChildBS
    
            ''Example of getting rows from children on first parent row
            Dim ChildRows() As DataRow = DS.Tables("ParentTable").Rows(0).GetChildRows("MyRelation")
        End Sub
    End Class
    Last edited by kpmc; Sep 14th, 2018 at 10:45 AM.

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    9

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    This works good but how do I do below:
    The Ord_no in TextBox1.text is a 10 digit number, but sometimes has leading or trailing 0's.
    I tried this: Dim sql As String = "Select * From TestTable Where ID =" "%" & TextBox1.text & "%"
    It did not work.
    I know the answer is probably simple but I'm a beginner.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,116

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    The basic (and not safe) version is like this:
    Code:
    Dim sql As String = "Select * From TestTable Where ID = '" & TextBox1.text & "'"
    Using that leaves you open to various problems (some of them far bigger than you would suspect), and the way to avoid all of those issue is to use Parameters.

    For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

  6. #6
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    547

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    Si, I would think that ID is type INT

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,116

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    I haven't checked myself (I just assumed based on the comment that it can have leading zeros), but post #2 treats that field as text based.

  8. #8
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    547

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    I guess I am even more confused than I initially thought.
    If you are following what Chris supplied I would probably drop the reader. you can accomplish the same thing with Command.ExecuteScalar method.
    Here is a function that takes a guid string argument. You can easily change column to ID and of type integer.

    Code:
     Private Function GetID(_GUID As String) As Integer
                Try
                    Dim ScalarResult As Object = Nothing
                    Using SQLConn As New SqlConnection("Data Source=*****\*****;Initial Catalog=*****;Integrated Security=True")
                        SQLConn.Open()
                        Dim CMD As New SqlCommand("SELECT FormGUID FROM Defective WHERE FormGUID = @GUID ", SQLConn)
                        CMD.Parameters.Add("@GUID", SqlDbType.VarChar).Value = _GUID
                        ScalarResult = CMD.ExecuteScalar
    
                        If ScalarResult Is Nothing Then
    
                            CMD = New SqlCommand("INSERT INTO Defective (FormGUID) VALUES (@GUID)", SQLConn)
                            CMD.Parameters.Add("@GUID", SqlDbType.VarChar).Value = _GUID
                            CMD.ExecuteNonQuery()             
    
                        End If
    
                        SQLConn.Close()
                    End Using
    				
                Catch ex As Exception
                    Return Nothing                
                End Try
    			
            End Function
    Edit- by the way, this is only part of a function (this wont return result on all paths). For my purpose it returns the ID value of the inserted record. this is only to give you an idea
    Last edited by kpmc; Sep 14th, 2018 at 12:42 PM.

  9. #9

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    9

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    This works good but how do I do below:
    The Ord_no in TextBox1.text is a 10 digit number, but sometimes has leading or trailing 0's.
    I tried this: Dim sql As String = "Select * From TestTable Where ID =" "%" & TextBox1.text & "%"
    It did not work.
    I know the answer is probably simple but I'm a beginner.

  10. #10
    Bad man! ident's Avatar
    Join Date
    Mar 2009
    Location
    Cambridge
    Posts
    5,094

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    Quote Originally Posted by kpmc View Post
    So, you are wanting to populate 4 tables from data that you already have from 3 tables? I think there are some design considerations to be made with your solution. You may want to consider using datarelations and column expressions to produce the results you want. Here is a simple example.

    Code:
    Public Class RelationExample
        Dim DS As New DataSet
        Dim ParentBS, ChildBS As New BindingSource
    
        Private Sub RelationExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With DS
                .Tables.Add(New DataTable With {.TableName = "ParentTable"})
                .Tables.Add(New DataTable With {.TableName = "ChildTable"})
    
                ''example data
                ''Parent DATA
                With .Tables("ParentTable")
                    .Columns.Add("ParentID", GetType(System.String))
                    For i = 0 To 5
                        .Rows.Add(i.ToString)
                    Next
                End With
    
                ''Child DATA
                With .Tables("ChildTable")
                    .Columns.Add("ParentID", GetType(System.String))
                    .Columns.Add("SomeNumber", GetType(Double))
                    For Each Drow As DataRow In DS.Tables("ParentTable").Rows
                        For i = 0 To 20
                            .Rows.Add(Drow("SomeNumber"), Int(Rnd() * 5) + 1)
                        Next
                    Next
                End With
            End With
    
            ''Create relations
            Dim ParentCol As DataColumn = DS.Tables("ParentTable").Columns("ParentID")
            Dim ChildCol As DataColumn = DS.Tables("ChildTable").Columns("ParentID")
    
            DS.Relations.Add("MyRelation", ParentCol, ChildCol, False)
            ParentBS.DataSource = DS.Tables("ParentTable")
    
            ''Add columns to parent table for aggregates
            With DS.Tables("ParentTable")
                .Columns.Add("SumSomeNumber", GetType(Double), "Sum(Child.SomeNumber)")
                .Columns.Add("CountSomeNumber", GetType(Double), "Count(Child.SomeNumber)")
                .Columns.Add("AvgSomeNumber", GetType(Double), "Avg(Child.SomeNumber)")
                .Columns.Add("MinSomeNumber", GetType(Double), "Min(Child.SomeNumber)")
                .Columns.Add("MaxSomeNumber", GetType(Double), "Max(Child.SomeNumber)")
            End With
    
            DataGridView1.DataSource = ParentBS
    
            With ChildBS
                .DataSource = ParentBS
                .DataMember = "MyRelation"
            End With
    
            DataGridView2.DataSource = ChildBS
    
            ''Example of getting rows from children on first parent row
            Dim ChildRows() As DataRow = DS.Tables("ParentTable").Rows(0).GetChildRows("MyRelation")
        End Sub
    End Class
    Don't use RND. It's legacy & decidedly inferior to the Random object that .NET has. You're not using VB6 so why use VB6 constructs? If you're going to use VB then use the Random class. That said it's not even being used correctly. If you even use Rnd then you are supposed to execute the Randomize statement.

  11. #11
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    547

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    well it produced a random number used correctly or not. if in the future i need to generate a random number that is used for something other than sample data, i shall refer to your consult.

  12. #12
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,223

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    Quote Originally Posted by kfink44 View Post
    This works good but how do I do below:
    The Ord_no in TextBox1.text is a 10 digit number, but sometimes has leading or trailing 0's.
    I tried this: Dim sql As String = "Select * From TestTable Where ID =" "%" & TextBox1.text & "%"
    It did not work.
    I know the answer is probably simple but I'm a beginner.
    add this to the Form

    Code:
     Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
            If Asc(e.KeyChar) <> 8 Then
                If Asc(e.KeyChar) < 48 Or Asc(e.KeyChar) > 57 Then
                    e.Handled = True
                End If
            End If
        End Sub
    and leave the Sql as it is
    Code:
     Dim sql As String = "Select * From TestTable Where ID =" & TextBox1.Text
    it will find the Number 1 or 000001

    EDIT:
    reading your Post#1 again..
    sound like a strange way to gather Data, and then look up if it's there or not

    EDIT:
    I was told to assign each unique set of specs an ID(ID_No), and record the first day it was entered (Initial_DT) and the last time the spec was ordered(Last_Dt) and how many times this particular set of specs was ordered (Times_Used) and put all the information into 4 different tables that was created by a team member. (currently the tables do not have data)
    how are you going to keep Track on amounts ..ordered ... what if somebody changes an Order 3 Day's later..
    what if sombody add's 3 Day's later somthing to an order ... I could go on an on

    I think this isn't a Task for a beginner as you said you are.


    regards
    Chris
    Last edited by ChrisE; Sep 14th, 2018 at 02:49 PM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    547

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    That is pretty clever, but this seems like a hodgepodge solution to find an ace in a house of cards that's built with tissue, but not the good tissues with the aloe, the generic kind that leave your nose red.

  14. #14
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,223

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    Quote Originally Posted by kpmc View Post
    That is pretty clever, but this seems like a hodgepodge solution to find an ace in a house of cards that's built with tissue, but not the good tissues with the aloe, the generic kind that leave your nose red.
    I've got a Red nose for over 20 Years then
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  15. #15
    Fanatic Member
    Join Date
    Sep 2017
    Posts
    547

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    lol, what i am getting at is maybe it would be in his interest to add some structure to this solution. i guess if hes happy im happy

  16. #16
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,223

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    Quote Originally Posted by kpmc View Post
    lol, what i am getting at is maybe it would be in his interest to add some structure to this solution. i guess if hes happy im happy
    more structure and some more thought how to handle Multi User input of Orders (deleting/changind Data) is not going to be easy.
    I don't know how the OP want's to keep track on the numbers.


    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  17. #17

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    9

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    I agree this is not a project that should have been given to me but the main developer is looking to retire so the projects I am being given are way out of my league. Currently there is only one person in the company who knows how to allocate material for an order and he does it from memory. They want a way to allow 1 other person to look at previous orders to see what was allocated for material so they know what to assign.

  18. #18
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,223

    Re: Find what materials were allocated for previous orders--SQL 2008, VS 2015

    perhaps you should try and create a Database first with only Materials ...
    get the Foundation/Cellar of the House done first

    example: several coatings types to choose from for front and/or back, rolls or sheets of metal, smooth or flat, several polish types, number of cuts, etc.
    what is needed for x .....
    coatings
    ->front coating (cost per inch ; meter ?)
    ->front and/or back (cost per inch ; meter ?)
    Metall
    -> sheet (cost per inch ; meter ?)
    -> roll (cost per inch ; meter ?)

    ...etc...

    get where I'm going

    might sound funny but get Paper and Pen(or in Excel) and try and draw a shema for all Material

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Tags for this Thread

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