Insert multiple records into a table from a TextBox

Sometimes you may require to insert multiple records from one text field (textarea). This can be done for multiple user choices or voting controls etc. In this example I will show how to insert multiple records at one click and abstructing data from textfield.

First I have created two tables in database for voting system and created default.aspx page like below –

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">  �
<title>Untitled Page</title>
</head>
<body>
<form id="form1">
<div>
<div style="text-align: left;"><span style="font-size: 30px; color: #3333ff; font-family: Verdana;"><strong><span style="color: #990000;">Add a new poll</span>
</strong></span></div>
</div>
<div style="text-align: left;">
<table>
<tbody>
<tr>
<td style="width: 100px;" valign="top"></td>
<td style="width: 100px;"></td>
</tr>
<tr>
<td style="width: 100px;" valign="top"></td>
<td style="width: 100px;"></td>
</tr>
<tr>
<td style="width: 100px;"></td>
<td style="width: 100px; text-align: center;"></td>
</tr>
</tbody>
</table>
</div>
</form>
</body>

Here is the code for the default.aspx page. I have included an onClick event to process the request.

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub btnUpdatePoll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdatePoll.Click
        Try
            '   Adds the question
            addPollQuestion()

            '   adds the options
            Dim strOptions() As String = txtOptions.Text.Split(vbCrLf)

            For Each strOption As String In strOptions
                addPollOptions(strOption)
            Next
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Private Sub addPollQuestion()
        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
        Dim sqlConn As New SqlConnection(strConnString)

        sqlConn.Open()
        Dim sqlCmd As New SqlCommand()

        sqlCmd.CommandText = "NewPoll"
        sqlCmd.CommandType = Data.CommandType.StoredProcedure
        sqlCmd.Connection = sqlConn

        '   Creation parameters
        Dim sqlParamQuestion As New SqlParameter("@v_Question", Data.SqlDbType.VarChar)

        sqlParamQuestion.Value = txtQuestion.Text

        sqlCmd.Parameters.Add(sqlParamQuestion)

        '   Execute stored procedure
        sqlCmd.ExecuteNonQuery()

        '   Close connection
        sqlConn.Close()
    End Sub

    Private Sub addPollOptions(ByVal strOption As String)
        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
        Dim sqlConn As New SqlConnection(strConnString)

        sqlConn.Open()
        Dim sqlCmd As New SqlCommand()

        sqlCmd.CommandText = "SetPollOption"
        sqlCmd.CommandType = Data.CommandType.StoredProcedure
        sqlCmd.Connection = sqlConn

        '   Creation parameters
        Dim sqlParamOption As New SqlParameter("@v_option", Data.SqlDbType.VarChar)

        sqlParamOption.Value = strOption

        sqlCmd.Parameters.Add(sqlParamOption)

        '   Execute stored procedure
        sqlCmd.ExecuteNonQuery()

        '   Close connection
        sqlConn.Close()
    End Sub
End Class

Here is the stored procedure I have used to insert new records into the SQL Server table.

ALTER PROCEDURE dbo.SetPollOption
	(
	@v_option	VARCHAR(100)
	)
AS
--	FINDING THE ID OF THE NEW QUESTION
	DECLARE @i_NextPollOptionID	INT,
			@i_PollId			INT

--	GETS THE ACTIVE POLL ID
	SELECT @i_PollId = PK_PollId FROM Polls WHERE Active = 1

--	INITIALISIG QUESTION ID
	SET @i_NextPollOptionID = 1

--	IF THERE ARE MORE POLLS, THEN SET THE QUESTION ID TO MAX ID + 1
	IF ((SELECT COUNT(*) FROM PollOptions) > 0)
	BEGIN
		SET @i_NextPollOptionID = (SELECT MAX(PK_OptionId) + 1 FROM PollOptions)
	END

--	INSERT THE NEW QUESTION IN THE TABLE POLLS
	INSERT INTO PollOptions
			(PK_OptionId, FK_PollId, Answer, Votes)
	VALUES	(@i_NextPollOptionID, @i_PollId, @v_option, 0)

	RETURN

Now I have inserted new records into the table. Lets display them in another page. I have created a new page polls.aspx. The page got the following controls and code –

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Poll.aspx.vb" Inherits="Poll" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">    <title>Untitled Page</title></head>
<body>    <form id="form1" runat="server">
<div>
<div>
<div style="text-align: left">
<span style="font-size: 30px; color: #3333ff; font-family: Verdana"><strong><span style="color: #990000">Please take a vote...</span><br />                </strong></span>
</div>
</div>
<div style="text-align: left">
<br />
<table width="100%">
<tr> <td style="width: 100px; border-top: thin solid;">   <asp:Label ID="lblPollQuestion" runat="server" Font-Bold="True" Font-Names="Verdana"  Font-Size="10pt" ForeColor="#CC3333" Text="Poll Question" Width="500px"></asp:Label></td></tr>
 <tr>
<td style="width: 100px">   <asp:RadioButtonList ID="rdoPollOptionList" runat="server" Font-Names="Verdana" Font-Size="9pt" ForeColor="#993300" Width="500px">   </asp:RadioButtonList></td> </tr>
<tr>
<td style="width: 100px; text-align: left">
<asp:Button ID="btnVote" runat="server" Text="Vote" Width="71px" /><br />  <br />
 <asp:Label ID="lblError" runat="server" Font-Names="Verdana" Font-Size="Smaller"
ForeColor="Red" Text="You cannot vote more than once..." Visible="False" Width="500px"></asp:Label></td>  </tr>
</table>        </div>        </div>    

</form>

</body>

</html>

Here is the code for polls.aspx page.

Imports System.Data
Imports System.Data.SqlClient

Partial Class Poll
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            DisplayPoll()
        End If
    End Sub

    Private Sub DisplayPoll()
        Try
            Dim ds As DataSet = getActivePoll()

            '   Displays the poll
            lblPollQuestion.Text = ds.Tables(0).Rows(0)("Question")

            Dim i As Integer = 0
            For Each dr As DataRow In ds.Tables(1).Rows
                rdoPollOptionList.Items.Add(dr("Answer"))
                rdoPollOptionList.Items(i).Value = dr("PK_OptionId")
                rdoPollOptionList.SelectedIndex = 0

                i = i + 1
            Next
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Private Function getActivePoll() As DataSet
        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
        Dim sqlConn As New SqlConnection(strConnString)

        '   Opens the connection
        sqlConn.Open()
        Dim sqlCmd As New SqlCommand()

        sqlCmd.CommandText = "GetActivePoll"
        sqlCmd.CommandType = Data.CommandType.StoredProcedure
        sqlCmd.Connection = sqlConn

        '   Gets the dataset from the sp
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(sqlCmd)

        '   Execute stored procedure
        da.Fill(ds)

        '   Close connection
        sqlConn.Close()

        Return ds
    End Function

    Protected Sub btnVote_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVote.Click
        If Response.Cookies("Voted") Is Nothing Then
            Response.Cookies("Voted").Value = "Voted"
            Response.Cookies("Voted").Expires = DateTime.Now.AddDays(1)

            lblError.Visible = False

            '   Checks if the user can still vote by using cookie
            RecordVote()
        Else
            lblError.Visible = True

        End If
    End Sub

    Private Sub RecordVote()
        Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
        Dim sqlConn As New SqlConnection(strConnString)

        sqlConn.Open()
        Dim sqlCmd As New SqlCommand()

        sqlCmd.CommandText = "IncrementVote"
        sqlCmd.CommandType = Data.CommandType.StoredProcedure
        sqlCmd.Connection = sqlConn

        '   Creation parameters
        Dim sqlParamQuestion As New SqlParameter("@i_OptionId", Data.SqlDbType.Int)

        sqlParamQuestion.Value = rdoPollOptionList.SelectedValue

        sqlCmd.Parameters.Add(sqlParamQuestion)

        '   Execute stored procedure
        sqlCmd.ExecuteNonQuery()

        '   Close connection
        sqlConn.Close()
    End Sub
End Class

I have used a few more stored procedure to display data in the polls.aspx page.

create PROCEDURE dbo.GetActivePoll
AS
--	GETS THE ACTIVE QUESTION FROM THE POLL TABLE
	SELECT	PK_PollId, Question
	FROM	Polls
	WHERE	Active = 1

--	GETS THE OPTIONS LINKED WITH THE ACTIVE POLL
	SELECT	PK_OptionId, Answer, Votes
	FROM	PollOptions
	WHERE	Fk_PollId IN
				(SELECT PK_PollID FROM Polls WHERE Active = 1)

	RETURN

Hope this gives some idea about multiple data entry from a text field.

Insert multiple records into a table from a TextBox

Bytajuddin

By tajuddin

Related Post

Setting up CORS in Azure APIM policy

jQuery Datatable with Blazor in ASP.NET 6

Send message to Azure Service Bus Topic using dotnet core application

Leave a Reply Cancel reply

You missed

Backup and restore Azure APIM

Why we should not save passwords in browsers – Cyber security awareness

Setting up CORS in Azure APIM policy

Add Azure DevOps agent IP address to Storage Account firewall using PowerShell and CI/CD pipeline