locked
Stored Procedure for Insert Query(Dynamic Text Field Value) RRS feed

  • Question

  • User-190697402 posted

    Hi All,

    I have a ASP.net webform in which i will having dynamic textboxes for adding email id (txtemail,txtemail1,txtemail2).

    Im using stored procedures for inserting form values into database table.

    how to write a stored procedure and codebehind for inserting the dynamic textbox values into database.

    Appreciate any help on this.i'm a new bie in ASP.net webforms.So it will be helpful for me to get a clearcut explanation on this.

    Thanks in Advance!

    Thursday, June 11, 2020 10:12 AM

Answers

  • User288213138 posted

    Hi teenajohn1989,

    My requirement is, on a single button click it should save all the details ,including multiple entry for the saleschannelhead mail id.

    I have a Plus sign for adding saleschannelhead mail id textbox's dynamically(DOM ). So after adding all saleschannelheads mail id i will click on that save button only once,then it should save all the values.

    Does this demo meet your requirement?

    txtSalesChannelHead:<asp:Panel ID="pnlTextBoxes" runat="server"></asp:Panel>
                <hr />
                <asp:Button ID="btnAdd" runat="server" Text="Add New" OnClick="AddTextBox" /><br />
                eventname:<asp:TextBox ID="txtEventName" runat="server"></asp:TextBox><br />
                eventdate:<asp:TextBox ID="txtEventDate" runat="server"></asp:TextBox><br />
                costperhead:<asp:TextBox ID="txtTotCostPerHead" runat="server"></asp:TextBox><br />
                totalcost:<asp:TextBox ID="txtTotalCostEvent" runat="server"></asp:TextBox><br />
                SalesChannelHead:<asp:TextBox ID="txtSalesChannelHead" runat="server"></asp:TextBox><br />
                salesmanager:<asp:TextBox ID="txtSalesManagers" runat="server"></asp:TextBox><br />
                <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="Save" />
    
    
    protected void AddTextBox(object sender, EventArgs e)
            {
                int index = pnlTextBoxes.Controls.OfType<TextBox>().ToList().Count + 1;
                this.CreateTextBox("txtDynamic" + index);
            }
    
            private void CreateTextBox(string id)
            {
                TextBox txt = new TextBox();
                txt.ID = id;
                pnlTextBoxes.Controls.Add(txt);
    
                Literal lt = new Literal();
                lt.Text = "<br />";
                pnlTextBoxes.Controls.Add(lt);
            }
            protected void Page_PreInit(object sender, EventArgs e)
            {
                List<string> keys = Request.Form.AllKeys.Where(key => key.Contains("txtDynamic")).ToList();
                int i = 1;
                foreach (string key in keys)
                {
                    this.CreateTextBox("txtDynamic" + i);
                    i++;
                }
            }
            protected void Save(object sender, EventArgs e)
            {
                foreach (TextBox txtEventName in pnlTextBoxes.Controls.OfType<TextBox>())
                {
                    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                    SqlConnection conn = new SqlConnection(constr);
                    SqlCommand cmd = new SqlCommand("SPInsertEvent", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("EventName", txtEventName.Text);
                    cmd.Parameters.AddWithValue("EventDate", Convert.ToDateTime(txtEventDate.Text));
                    cmd.Parameters.AddWithValue("CostPerHead", txtTotCostPerHead.Text);
                    cmd.Parameters.AddWithValue("TotalCost", txtTotalCostEvent.Text);
                    cmd.Parameters.AddWithValue("SalesChannelHeads", txtSalesChannelHead.Text);
                    cmd.Parameters.AddWithValue("SalesManagers", txtSalesManagers.Text);
                    cmd.Parameters.AddWithValue("EventCreationTime", DateTime.Now);
                    conn.Open();
                    int k = cmd.ExecuteNonQuery();
                    if (k != 0)
                    {
                        string message = "Event added successfully.";
                        string script = "window.onload = function(){ alert('";
                        script += message;
                        script += "')};";
                        ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
                    }
                    conn.Close();
                }
            }
    
    CREATE PROCEDURE SPInsertEvent
           
           @eventName varchar(200),
           @eventDate date,
           @costPerHead varchar(200),
           @totalCost varchar(200),
    	   @salesChannelHeads varchar(200),
    	   @salesManagers varchar(200),
    	   @eventCreationTime datetime
    AS
    BEGIN
           
           SET NOCOUNT ON;
    
        -- Insert statements for procedure here
           INSERT INTO dbo.hp_event
                  (eventname, eventdate, costperhead, totalcost, salesChannelHeads, salesManagers, eventcreationtime)
           VALUES
                  (@eventName, @eventDate, @costPerHead, @totalCost, @salesChannelHeads, @salesManagers, @eventCreationTime)
    END
    GO
    

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 12, 2020 7:13 AM

All replies

  • User475983607 posted

    I have a ASP.net webform in which i will having dynamic textboxes for adding email id (txtemail,txtemail1,txtemail2).

    Im using stored procedures for inserting form values into database table.

    how to write a stored procedure and codebehind for inserting the dynamic textbox values into database.

    Appreciate any help on this.i'm a new bie in ASP.net webforms.So it will be helpful for me to get a clearcut explanation on this.

    The database should have a table that has at least the userId and email address.  This table has  a one-to-many relationship with a user table.  

    You did not share the table design or stored procedure which makes it difficult to answer your question.

    Thursday, June 11, 2020 10:52 AM
  • User-190697402 posted

    Hi ,

    Many Thanks for your reply.

    Here is my stored procedure

    CREATE PROCEDURE SPInsertEvent
           
           @eventName varchar(200),
           @eventDate date,
           @costPerHead varchar(200),
           @totalCost varchar(200),
    	@salesChannelHeads varchar(200),
    	@salesManagers varchar(200),
    	@eventCreationTime datetime
    AS
    BEGIN
           
           SET NOCOUNT ON;
    
        -- Insert statements for procedure here
           INSERT INTO dbo.hp_event
                  (event_name, event_date, cost_per_head, total_cost, sales_channel_head, sales_manager, event_creation_time)
           VALUES
                  (@eventName, @eventDate, @costPerHead, @totalCost, @salesChannelHeads, @salesManagers, @eventCreationTime)
    END
    

    i will be inserting mail id for saleschannelheads column. The SalesChannelsHeads column will have addmore option,so that i can create the textbox dynamically.(txtSalesChannelHeads,txtSalesChannelHeads1,txtSalesChannelHeads2, ...so on - these will be the saleschannelheads textbox id) .

    This is my C# code for inserting values.

    protected void btnEventAddFunc_Click(object sender, EventArgs e)
            {
                
                SqlCommand cmd = new SqlCommand("SPInsertEvent", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("EventName", txtEventName.Text);
                cmd.Parameters.AddWithValue("EventDate", Convert.ToDateTime(txtEventDate.Text));
                cmd.Parameters.AddWithValue("CostPerHead", txtTotCostPerHead.Text);
                cmd.Parameters.AddWithValue("TotalCost", txtTotalCostEvent.Text);
                cmd.Parameters.AddWithValue("SalesChannelHead", txtSalesChannelHead.Text);
                cmd.Parameters.AddWithValue("SalesManager", txtSalesManagers.Text);
                cmd.Parameters.AddWithValue("EventCreationTime", DateTime.Now);
                conn.Open();
                int k = cmd.ExecuteNonQuery();
                if (k != 0)
                {
                    string message = "Event added successfully.";
                    string script = "window.onload = function(){ alert('";
                    script += message;
                    script += "')};";
                    ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
                    
    
                }
                conn.Close();
                
            }

    Currently its inserting mailid for one saleschannelhead.

    Thursday, June 11, 2020 11:30 AM
  • User475983607 posted

    The stored procedure indicates the database design is not relational.  That's generally a problem in a relational database. 

    The saleschannelheads column holds an email address?  Are you trying to append email addresses?

    Thursday, June 11, 2020 12:54 PM
  • User-190697402 posted

    Im new to this.

    Yes the saleschannelheads column hold an email address. I want to insert into the database table like this.

    eventname eventdate costperhead totalcost saleschannelhead salesmanager
    event1 20-07-2020 30 300 test@abc.com bcd@abc.com
    event1 20-07-2020 30 300 test1@abc.com bcd@abc.com
    event1 20-07-2020 30 300 test2@abc.com bcd@abc.com

    I dont know how to write stored procedure to achieve this.It would be grateful if you could provide a sample code.Thanks

    Thursday, June 11, 2020 3:03 PM
  • User288213138 posted

    Hi teenajohn1989,

    Dynamic Text Field Value

    Yes the saleschannelheads column hold an email address. I want to insert into the database table like this.

    eventname eventdate costperhead totalcost saleschannelhead salesmanager
    event1 20-07-2020 30 300 test@abc.com bcd@abc.com
    event1 20-07-2020 30 300 test1@abc.com bcd@abc.com
    event1 20-07-2020 30 300 test2@abc.com bcd@abc.com

    I dont know how to write stored procedure to achieve this.It would be grateful if you could provide a sample code.Thanks

    According to your description, i made demo for you.

    eventname:<asp:TextBox ID="txtEventName" runat="server"></asp:TextBox><br />
                eventdate:<asp:TextBox ID="txtEventDate" runat="server"></asp:TextBox><br />
                costperhead:<asp:TextBox ID="txtTotCostPerHead" runat="server"></asp:TextBox><br />
                totalcost:<asp:TextBox ID="txtTotalCostEvent" runat="server"></asp:TextBox><br />
                SalesChannelHead:<asp:TextBox ID="txtSalesChannelHead" runat="server"></asp:TextBox><br />
                txtSalesChannelHead:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
                salesmanager:<asp:TextBox ID="txtSalesManagers" runat="server"></asp:TextBox><br />
                <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="Save" />
    
     protected void Save(object sender, EventArgs e)
            {
                string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                SqlConnection conn = new SqlConnection(constr);
                SqlCommand cmd = new SqlCommand("SPInsertEvent", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("EventName", txtEventName.Text);
                cmd.Parameters.AddWithValue("EventDate", Convert.ToDateTime(txtEventDate.Text));
                cmd.Parameters.AddWithValue("CostPerHead", txtTotCostPerHead.Text);
                cmd.Parameters.AddWithValue("TotalCost", txtTotalCostEvent.Text);
                cmd.Parameters.AddWithValue("salesChannelHeads", txtSalesChannelHead.Text);
                cmd.Parameters.AddWithValue("salesManagers", txtSalesManagers.Text);
                cmd.Parameters.AddWithValue("EventCreationTime", DateTime.Now);
                conn.Open();
                int k = cmd.ExecuteNonQuery();
                if (k != 0)
                {
                    string message = "Event added successfully.";
                    string script = "window.onload = function(){ alert('";
                    script += message;
                    script += "')};";
                    ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
                }
                conn.Close();
    
            }
    
    CREATE PROCEDURE SPInsertEvent
           
           @eventName varchar(200),
           @eventDate date,
           @costPerHead varchar(200),
           @totalCost varchar(200),
    	   @salesChannelHeads varchar(200),
    	   @salesManagers varchar(200),
    	   @eventCreationTime datetime
    AS
    BEGIN
           
           SET NOCOUNT ON;
    
        -- Insert statements for procedure here
           INSERT INTO dbo.hp_event
                  (eventname, eventdate, costperhead, totalcost, salesChannelHeads, salesManagers, eventcreationtime)
           VALUES
                  (@eventName, @eventDate, @costPerHead, @totalCost, @salesChannelHeads, @salesManagers, @eventCreationTime)
    END
    GO

    The result:

    Best regards,

    Sam

    Friday, June 12, 2020 5:34 AM
  • User-190697402 posted

    Hi Sam,

    Thank you so much  for your reply,

    My requirement is, on a single button click it should save all the details ,including multiple entry for the saleschannelhead mail id.

    I have a Plus sign for adding saleschannelhead mail id textbox's dynamically(DOM ). So after adding all saleschannelheads mail id i will click on that save button only once,then it should save all the values.

    Thank you so much for your effort . I can use your solution in some other context also.

    Friday, June 12, 2020 6:35 AM
  • User288213138 posted

    Hi teenajohn1989,

    My requirement is, on a single button click it should save all the details ,including multiple entry for the saleschannelhead mail id.

    I have a Plus sign for adding saleschannelhead mail id textbox's dynamically(DOM ). So after adding all saleschannelheads mail id i will click on that save button only once,then it should save all the values.

    Does this demo meet your requirement?

    txtSalesChannelHead:<asp:Panel ID="pnlTextBoxes" runat="server"></asp:Panel>
                <hr />
                <asp:Button ID="btnAdd" runat="server" Text="Add New" OnClick="AddTextBox" /><br />
                eventname:<asp:TextBox ID="txtEventName" runat="server"></asp:TextBox><br />
                eventdate:<asp:TextBox ID="txtEventDate" runat="server"></asp:TextBox><br />
                costperhead:<asp:TextBox ID="txtTotCostPerHead" runat="server"></asp:TextBox><br />
                totalcost:<asp:TextBox ID="txtTotalCostEvent" runat="server"></asp:TextBox><br />
                SalesChannelHead:<asp:TextBox ID="txtSalesChannelHead" runat="server"></asp:TextBox><br />
                salesmanager:<asp:TextBox ID="txtSalesManagers" runat="server"></asp:TextBox><br />
                <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="Save" />
    
    
    protected void AddTextBox(object sender, EventArgs e)
            {
                int index = pnlTextBoxes.Controls.OfType<TextBox>().ToList().Count + 1;
                this.CreateTextBox("txtDynamic" + index);
            }
    
            private void CreateTextBox(string id)
            {
                TextBox txt = new TextBox();
                txt.ID = id;
                pnlTextBoxes.Controls.Add(txt);
    
                Literal lt = new Literal();
                lt.Text = "<br />";
                pnlTextBoxes.Controls.Add(lt);
            }
            protected void Page_PreInit(object sender, EventArgs e)
            {
                List<string> keys = Request.Form.AllKeys.Where(key => key.Contains("txtDynamic")).ToList();
                int i = 1;
                foreach (string key in keys)
                {
                    this.CreateTextBox("txtDynamic" + i);
                    i++;
                }
            }
            protected void Save(object sender, EventArgs e)
            {
                foreach (TextBox txtEventName in pnlTextBoxes.Controls.OfType<TextBox>())
                {
                    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                    SqlConnection conn = new SqlConnection(constr);
                    SqlCommand cmd = new SqlCommand("SPInsertEvent", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("EventName", txtEventName.Text);
                    cmd.Parameters.AddWithValue("EventDate", Convert.ToDateTime(txtEventDate.Text));
                    cmd.Parameters.AddWithValue("CostPerHead", txtTotCostPerHead.Text);
                    cmd.Parameters.AddWithValue("TotalCost", txtTotalCostEvent.Text);
                    cmd.Parameters.AddWithValue("SalesChannelHeads", txtSalesChannelHead.Text);
                    cmd.Parameters.AddWithValue("SalesManagers", txtSalesManagers.Text);
                    cmd.Parameters.AddWithValue("EventCreationTime", DateTime.Now);
                    conn.Open();
                    int k = cmd.ExecuteNonQuery();
                    if (k != 0)
                    {
                        string message = "Event added successfully.";
                        string script = "window.onload = function(){ alert('";
                        script += message;
                        script += "')};";
                        ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
                    }
                    conn.Close();
                }
            }
    
    CREATE PROCEDURE SPInsertEvent
           
           @eventName varchar(200),
           @eventDate date,
           @costPerHead varchar(200),
           @totalCost varchar(200),
    	   @salesChannelHeads varchar(200),
    	   @salesManagers varchar(200),
    	   @eventCreationTime datetime
    AS
    BEGIN
           
           SET NOCOUNT ON;
    
        -- Insert statements for procedure here
           INSERT INTO dbo.hp_event
                  (eventname, eventdate, costperhead, totalcost, salesChannelHeads, salesManagers, eventcreationtime)
           VALUES
                  (@eventName, @eventDate, @costPerHead, @totalCost, @salesChannelHeads, @salesManagers, @eventCreationTime)
    END
    GO
    

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 12, 2020 7:13 AM
  • User-190697402 posted

    Hi Sam ,

    Thank you so much for your effort and time.

    There is one correction,i want to insert the mailid in saleschannelhead column,so i changed the txtEventName from foreach to txtSalesChannelHead.Now its inserting.

    foreach (TextBox txtSalesChannelHead in idSalesChannelHead.Controls.OfType<TextBox>())

    i have to do the same for salesmanagers also.Should i call another foreach inside this loop ?

    Friday, June 12, 2020 8:07 AM