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

[kfink44]

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

[ChrisE]

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

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

[kpmc]

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.

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

[kfink44]

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.

[si_the_geek]

The basic (and not safe) version is like this:

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

[kpmc]

Si, I would think that ID is type INT

[si_the_geek]

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.

[kpmc]

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.

 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

[kfink44]

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.

[ident]

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.

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.

[kpmc]

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.

[ChrisE]

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

 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

 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

[kpmc]

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.

[ChrisE]

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

[kpmc]

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

[ChrisE]

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

[kfink44]

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.

[ChrisE]

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