SQL插入基于VB中的值的多个记录表单

Question

我有一个Visual Basic窗体，其输入字段将生成单个零件的序列号并将其写回到SQL数据库。如何根据vb表单中的数量字段生成多个连续数据？

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 

Answer 1

把你的插入过程中的循环：

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 

或者你可以如你的数据库添加几个字段serial_1，serial_2等。

然后根据数量你可以用IF Then语句更新这些字段。

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