dcsimg
Results 1 to 4 of 4

Thread: Looking for how to save a Batch of a Production in Database

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2017
    Posts
    21

    Looking for how to save a Batch of a Production in Database

    Hello Stuck again,

    Would like to know how i would achieve this, what would seem simple but i am unsure what i am searching for on Google. I have looked for Batch, Bulk Insert, loop Row, all things.

    I am very sure in know a guy that would do this in a flash.. "jmcilhinney" Comes to mind right away.. I wish i had what he forgot.

    Code:
    Module Module1
    
        Sub CreateBatch()
            Dim Number As Integer = 0
            Dim Value As Integer = 10
            Dim CurrentDate As Date = Now
    
            Do While (Number < Value)
                Number += 1
                'print the array elements
                Console.WriteLine("Bag number: " & Number & "  " & CurrentDate)
            Loop
            ' this loop prints out all the array elements
    
        End Sub
    
        Sub Main()
            CreateBatch()
        End Sub
    
    End Module
    Ctrl F5

    Using a form of course

    Basically i want to save that in a database formatted like that. The number ten will be a Textbox to amount the qty. I want to insert the rows into the database each on separate row. Bag1, Bag2, and so on. Its a production stock tracking application. I was looking into dumping it to a dataset and then writing to Database. but to be honest i am lost. still learning how to do this without the wizards and things.

    Thank you for looking

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Sep 2017
    Posts
    21

    Re: Looking for how to save a Batch of a Production in Database

    This is what i have started with

    Code:
     Private Sub AddProductionButton_Click(sender As Object, e As EventArgs) Handles AddProductionButton.Click
    
            Dim DBDT = New DataTable
            Dim DBDA As New OleDbDataAdapter
    
            Try
                'Query Parameters
                Dim INSERTProd As String = "INSERT INTO Product (ProductType, Quantity, UsedFor) VALUES (@ProductType, @Quantity, @UsedFor)"
    
                Dim DBCon As New OleDbCommand(INSERTProd, Connection)
                DBCon.CommandType = CommandType.Text
                DBDA.InsertCommand = DBCon
    
                Connection.Open()
    
                Dim i As Integer
                Dim Value = CInt(NumberOfBagsTextBox.Text)
                Dim Type As String = (ProductComboBox.Text)
                Dim CurrentDate As Date = Now
    
                Do While (i < Value)
                    i += 1
    
                Loop
    
                Me.Close()
    
                MessageBox.Show(i & " BAGS of  " & Type & "  Have Been added to Stock", "Production Run", MessageBoxButtons.OK)
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                Connection.Close()
            End Try
        End Sub
    Loads missing but gives the database table and columns


    Probably totally wrong, but i am open to criticism.. i am learning in my defence.
    Last edited by Interplain; Jan 24th, 2018 at 03:07 PM. Reason: adding info

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2017
    Posts
    21

    Re: Looking for how to save a Batch of a Production in Database

    Anyone Please help..

  4. #4
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    855

    Re: Looking for how to save a Batch of a Production in Database

    For one, you never execute the insert.

    I'll give you a possible example of a more efficient way for bulk inserting, but I'm not sure if transactions work for OLEDB. For SQLite this greatly speeds things up if using traditional hard drives because each insert is a transaction. However, if wrapped within a transaction, it does just a single larger call to the database instead of a call for each element in the loop.

    Code:
                    Dim cmd2 As SQLite.SQLiteCommand = conData.CreateCommand
                    cmd2.CommandText = "Delete from SP_keybrd"
                    cmd2.ExecuteNonQuery()
                    Dim sql As String = "Insert into SP_KEYBRD (SK_TYPE, SK_PLU, SK_DESC, SK_PRTY, SK_FCOL, SK_BCOL, SK_FUNC, SK_PNLID, SK_SIZE, SK_SETID) VALUES ( '{0}', '{1}', '{2}', {3}, '{4}', '{5}', '{6}', {7}, '{8}', {9})"
                    Dim trans As SQLite.SQLiteTransaction = conData.BeginTransaction 'START TRANSACTION HERE
                    For Each r As DataRow In dbkeybrd.Rows
                        'CREATE ALL THE INSERTS NEEDED WITHIN THE LOOP
                        cmd2.CommandText = String.Format(sql, r("sk_type").ToString.toSQLstring, r("sk_plu").ToString.toSQLstring, r("sk_desc").ToString.toSQLstring, r("sk_prty").ToString.toSQLstring, r("sk_fcol").ToString.toSQLstring, r("sk_bcol").ToString.toSQLstring, r("sk_func").ToString.toSQLstring, r("sk_pnlid").ToString.toSQLstring, r("sk_size").ToString.toSQLstring, r("sk_setid").ToString.toSQLstring)
                        cmd2.ExecuteNonQuery()
    
                    Next
    
                    trans.Commit() 'COMMIT THE TRANSACTION AFTER THE LOOP TO PROCESS ALL THE INSERTS
    Note that if you have LOTS of rows (or really long ones) you may want to also commit after every X times through the loop, for example.

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