如何在单个表中插入来自不同文本框的多条记录

Question

我有一个由八个Textboxes组成的视图，用于从用户输入问题的选项文本以在DataBase表中提交。这些值将进入名为Question_Options的表，具有相同的Question_Id(F.K)，我保存在一个隐藏的字段中，现在我必须在DataBase中提交所有TextBoxes值，我想通过循环....来完成有什么想法吗....我只是个初学者。

我的HTML代码是

 <table>
        <tr>
            <td>Option 1
                <asp:HiddenField ID="HF_QidOpt" runat="server" />
            </td>
            <td>
                <asp:TextBox ID="txt_OptText" runat="server" Height="54px" TextMode="MultiLine" Width="310px"></asp:TextBox>
            </td>
            <td>Option 1 Description</td>
            <td>
                <asp:TextBox ID="txt_OptDesc" runat="server" TextMode="MultiLine" Height="54px" Width="310px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Option 2</td>
            <td>
                <asp:TextBox ID="txt_OptText2" runat="server" Height="54px" TextMode="MultiLine" Width="310px"></asp:TextBox>
            </td>
            <td>Option 2 Description</td>
            <td>
                <asp:TextBox ID="txt_OptDesc2" runat="server" TextMode="MultiLine" Height="54px" Width="310px"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Option 3</td>
            <td>
                <asp:TextBox ID="txt_OptText3" runat="server" Height="54px" TextMode="MultiLine" Width="310px"></asp:TextBox></td>
            <td>Option 3 Description</td>
            <td>
                <asp:TextBox ID="txt_OptDesc3" runat="server" TextMode="MultiLine" Height="54px" Width="310px"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Option 4</td>
            <td>
                <asp:TextBox ID="txt_OptText4" runat="server" Height="54px" TextMode="MultiLine" Width="310px"></asp:TextBox></td>
            <td>Option 4 Description</td>
            <td>
                <asp:TextBox ID="txt_OptDesc4" runat="server" TextMode="MultiLine" Height="54px" Width="310px"></asp:TextBox></td>
        </tr>
        <tr>
            <td>
                <asp:Button ID="btn_Create_Option" runat="server" Text="Create Option" /></td>
        </tr>
        </table>

和我已经尝试过这个，但无法获得值

Try
            Dim DB As New SQLDBDataContext
            Dim NGUID As New System.Guid(HF_QidOpt.Value.ToString)
            Dim boxes As TextBox() = New TextBox() {txt_OptText, txt_OptText2, txt_OptText3, txt_OptText4, txt_OptDesc, txt_OptDesc2, txt_OptDesc3, txt_OptDesc4}
            For Each t As TextBox In boxes
                If t IsNot Nothing Then
                    Dim CO As New Question_Option
                    With CO
                        .UID = System.Guid.NewGuid
                        .Question_ID = NGUID
                        .Option_Text = 'How to get value of txt_OptText, txt_OptText2, txt_OptText3, txt_OptText4'
                        .Option_Description = 'How to get value of txt_OptDesc, txt_OptDesc2, txt_OptDesc3, txt_OptDesc4'
                    End With
                    DB.Question_Options.InsertOnSubmit(CO)
                End If
            Next
                    DB.SubmitChanges()
                    MV_Default.SetActiveView(V_Success)
        Catch ex As Exception
            lbl_Failure.Text = ex.Message
            MV_Default.SetActiveView(V_Failure)

结束尝试

Answer 1

您必须在SQL Server中使用UNION all在单个语句中插入SQL脚本中的多行。

   INSERT INTO Table 
     (Name, Location) 
  SELECT 'Name1', 'Location1' 
  UNION ALL
   SELECT 'Name2', 'Location2'
   UNION ALL
   SELECT 'Name3', 'Location3' 

ref this and this