Adds data into sql in comma separated textbox in C#

Question

I have a textbox that contains data separated by commas See pictures:

and a button to add data.

The data is comma-separated: E013-007,E013-021,E013-022,E013-048,E013-049,V039-034

I need help to insert data from that textbox into SQL using C#.

Data in Sql by line:

E013-007

E013-021

E013-022

...

I have the code but it does not work:

SqlConnection con = new SqlConnection(DbConnect.ConnectStr);
        SqlCommand cmd = new SqlCommand("multiple", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;
        con.Open();

        if (txtKMH.Text.Contains(","))//checking for you are entered single value or multiple values
        {
            string[] arryval = txtKMH.Text.Split(',');//split values with ‘,’
            int j = arryval.Length;
            int i = 0;
            for (i = 0; i < j; i++)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@Main_KMH", arryval[j]);
                cmd.Parameters.AddWithValue("@Main_Date", txtNgay.Text);
                cmd.ExecuteNonQuery();
            }
            ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Thành công!')</script>");
        }
        con.Close();

Sql:

USE [Database]
    GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   ALTER proc [dbo].[multiple] @Main_KMH nvarchar(50),@Main_Date DateTime
   AS
   Begin
   insert into MainVotes(Main_KMH,Main_Date) values(@Main_KMH,@Main_Date);
   End

Well you're running a stored procedure, but we can't see that procedure. Maybe the problem is in there? Also what is the content of `txtNgay.Text` - you didn't mention it. — ADyson, Mar 21 '18 at 08:56
@MrTrung explain what's going on in the question itself. And *post the query*. It's impossible to guess what's wrong with it if you don't even post it. — Panagiotis Kanavos, Mar 21 '18 at 08:57
And also if the user enters only one value it will never execute the query. So if only one value is entered, it cannot save because it never gets past the `if (txtKMH.Text.Contains(","))` statement. — ADyson, Mar 21 '18 at 08:57
In any case, if you want to insert data into a table you can use SqlBulkCopy or a table-valued parameter instead of executing individual inserts. ORMs like Entity Framework and micro-ORMs like dapper can batch individual queries together and *avoid* the cost of multiple executions — Panagiotis Kanavos, Mar 21 '18 at 08:58
USE MyDataBase GO /****** Object: StoredProcedure [dbo].[multiple] Script Date: 3/21/2018 3:57:13 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[multiple] @Main_KMH nvarchar(50),@Main_Date DateTime AS Begin insert into MainVotes(Main_KMH,Main_Date) values(@Main_KMH,@Main_Date); End — Mr Trung, Mar 21 '18 at 08:58
Edit it into the question please, code in comments is very hard to read. Use the "edit" button at the bottom of your question. — ADyson, Mar 21 '18 at 08:59
`cmd.Parameters.AddWithValue("@Main_KMH", arryval[j]);` the `[j]` should be `[i]` - you are inserting the last value in each iteration — Mihai Ovidiu Drăgoi, Mar 21 '18 at 09:00

score 2 · Accepted Answer · answered Mar 21 '18 at 09:07

2

Pass the comma seperated string as nvarchar(max) parameter to sql server. Then handle this to convert that to rows and use it insert. You can create a function for this.

Something like:

CREATE FUNCTION dbo.BreakRows (@CommadelimitedString   varchar(1000))
RETURNS   @Result TABLE (Column1   VARCHAR(100))
AS
BEGIN
        DECLARE @IntLocation INT
        WHILE (CHARINDEX(',',    @CommadelimitedString, 0) > 0)
        BEGIN
              SET @IntLocation =   CHARINDEX(',',    @CommadelimitedString, 0)      
              INSERT INTO   @Result (Column1)
              --LTRIM and RTRIM to ensure blank spaces are   removed
              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation)))   
              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '') 
        END
        INSERT INTO   @Result (Column1)
        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed
        RETURN 
END
GO

Source here Now use this in your stored proc:

SELECT * FROM dbo.BreakStringIntoRows('Apple,Banana,Orange')

You will have to change this code a bit. But the idea can be used to achieve what you are trying to do

answered Mar 21 '18 at 09:07

Praneet Nadkar

823
7
16

I want to add a lot of data in a textbox into sql??? – Mr Trung Mar 21 '18 at 09:18
@MrTrung I did not get it. Could you elaborate what you mean by your question I want to add a lot of data in a textbox into sql??? – Praneet Nadkar Mar 21 '18 at 09:19
Hi @MrPraneet Nadkar.I mean insert multi data separated by commas.. – Mr Trung Mar 21 '18 at 09:21
@MrTrung this code will separate them out into individual records, but does it in SQL instead of in C#. You can just send the whole contents of the textbox into this code, and call the procedure only once. if `varchar(1000)` is too small for the input, then just make that parameter a bigger size – ADyson Mar 21 '18 at 09:39
How to apply it to C # code? – Mr Trung Mar 21 '18 at 11:00
@MrTrung just call it the same way you call your "multiple" procedure, but call it only once, and pass the whole of `txtKMH.Text` into the `@CommadelimitedString` parameter. You may need to adapt it to add your date parameter in there as well. It should be faster than calling the same procedure many times. – ADyson Mar 21 '18 at 11:12
oke. I'll try it. – Mr Trung Mar 21 '18 at 11:15
@ADyson how to insert data into my table q@result TABLE (Column1 VARCHAR(100)) = mytable? – Mr Trung Mar 21 '18 at 11:25
what @ADyson means is SqlConnection con = new SqlConnection(DbConnect.ConnectStr); SqlCommand cmd = new SqlCommand("multiple", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; con.Open(); cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Main_KMH",txtKMH.Text); cmd.Parameters.AddWithValue("@Main_Date", txtNgay.Text); cmd.ExecuteNonQuery(); – Praneet Nadkar Mar 21 '18 at 11:27
@MrTrung ah sorry this is a function, I had not seen it properly. But you could convert into a procedure, and run an INSERT into your table instead of the table object. – ADyson Mar 21 '18 at 11:28
Now call a proc.. in that, pass the comma seperated values and then use them after splitting – Praneet Nadkar Mar 21 '18 at 11:28
@ADyson I still can not apply to c #. I need the proc code. help – Mr Trung Mar 21 '18 at 11:49

score 0 · Answer 2 · answered Mar 21 '18 at 10:28

0

We can create a function to split given comma separated string in to line by line using XMl and SPlit()

CREATE FUNCTION [dbo].[udf_GetUnsplitData]
(
@string nvarchar(max)
)
RETURNS  @OutTable TABLE
(
DATA varchar(20)
)
AS
BEGIN

DECLARE @Temp AS TABLE
(
DATA nvarchar(max)
)
INSERT INTO @Temp
SELECT @string

INSERT INTO @OutTable
SELECT 
 Split.a.value('.','nvarchar(1000)') As DATA
FROM
(
SELECT 
CAST('<S>'+REPLACE(DATA,',','</S><S>')+'</S>' AS XML ) AS DATA
FROM @Temp
)A
CROSS APPLY DATA.nodes('S') AS Split(a)

RETURN
END
GO

SELECT [dbo].[udf_GetUnsplitData]('E013-007,E013-021,E013-022,E013-048,E013-049,V039-034')

RESULT

DATA
--------
E013-007
E013-021
E013-022
E013-048
E013-049
V039-034

answered Mar 21 '18 at 10:28

Sreenu131

2,476
1
7
18

How to apply it to C # code? – Mr Trung Mar 21 '18 at 11:00
@MrTrung Bro i am not c# developer i cant help you – Sreenu131 Mar 21 '18 at 12:51
@MrTrung we can apply and call function like this i am not pretty sure i just google it .try this SqlCommand Totalf = new SqlCommand("SELECT [dbo].[udf_GetUnsplitData](@string)", conex1); – Sreenu131 Mar 21 '18 at 12:55
SqlCommand Totalf = new SqlCommand("SELECT [dbo].[udf_GetUnsplitData](@string)", conex1); – Sreenu131 Mar 21 '18 at 12:55
hey @Sreenu131 Insert into table not select – Mr Trung Mar 22 '18 at 01:00

Adds data into sql in comma separated textbox in C#

2 Answers2