CODEWITHSUNDEEP
sql-servervb.netvisual-studio-2015
dustinw
dustinw

Reputation: 13

SQL insert muliple records based on value in VB Form

I have a Visual Basic Form with input fields that will generate a serial number for a single part and write it back to the SQL Database. How can I generate multiple serials based on a quantity field within the vb form?

SQL DB:

create table serialnumbers (
    serial int IDENTITY(10000,1),
    workorder varchar(50),
    partnumber varchar(50),
    employeeid int,
    [day] varchar(50)
)

VB:

Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Form2.Show()
End Sub

Public Sub ClearTextBoxes(frm As Form)

    For Each Control In frm.Controls
        If TypeOf Control Is TextBox Then
            Control.Text = ""     'Clear all text'
        End If
    Next Control

End Sub


Private Sub BTNSUBMIT_Click(sender As Object, e As EventArgs) Handles BTNSUBMIT.Click
    datetime.Text = Date.Now.ToString
    If workorder.Text = "" Or partnumber.Text = "" Or employeeid.Text = "" Or quantity.Text = "" Or datetime.Text = "" Then
        MsgBox("Please Enter All Required Fields")
    Else

        Try
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = "Insert Into famem1 Values ('" & workorder.Text & "', '" & partnumber.Text & "', '" & employeeid.Text & "', '" & datetime.Text & "') "

            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            MsgBox("Successfully Added", MsgBoxStyle.Information, "add")

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
        Call ClearTextBoxes(Me)
    End If
End Sub
End Class

Upvotes: 1

Views: 71

Answers (1)

user6120842
user6120842

Reputation:

Put your insert procedure in a loop:

For a = 1 to 20 'or whatever qty the user inputs

    Using con As New OleDb.OleDbConnection

            con.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
                                   "Data Source = " & auth_path

            Dim sqlcommand As New OleDb.OleDbCommand

            'set work order number
            'set part number
            'set employee id

            'generate serial here

            con.Open()

            With sqlcommand
                .CommandText = "Insert Into ..... "
                .Connection = con
                .ExecuteNonQuery()

            End With

    End Using



Next

Or you could add a few fields to your database such as serial_1, serial_2 etc.

And then based on the qty your could update those fields with an IF Then statement.

If Cint(txt_qty.text) >= 2 Then

 .... ' update the record with serial_2

End If

If Cint(txt_qty.text) >= 3 Then

 .... ' update the record with serial_3

End If

Upvotes: 2

Related Questions