Click here to Skip to main content
15,884,739 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
In a text box a user can eneter multiple values.On a button click I want to save these
values in the database but with the same id in different rows.(one by one).

I tried but they are getting stored in the same line.

Can someone help me please.
Posted
Comments
Nandakishore G N 10-Apr-13 5:10am    
what have you done..paste it?..generally insert to the database will not go into a same column if id is autogenerated..

-- Here i assume is your destination table is
--CREATE TABLE ActualTable (ID int, name varchar(100))

--Create the proc which will process multiple names and save in actual table

CREATE PROC proc_ins_names_from_collection
@strNameString VARCHAR(400)
-- this will be string having all names (I assume they are seperated by , camma)
AS
BEGIN

-- Logic start to split the string in rows
DECLARE @strNameTable TABLE (name VARCHAR(400))

DECLARE @allnames VARCHAR(400),
@currentindex INT,
@currentName VARCHAR(400),
@currentID INT;

IF RIGHT(RTRIM(@strNameString),1) <> ','
SELECT @strNameString = @strNameString + ','
SELECT @currentindex = patindex('%,%' , @strNameString)
WHILE @currentindex <> 0
BEGIN
SELECT @currentName = left(@strNameString, (@currentindex-1))
INSERT INTO @strNameTable(name) VALUES( CAST (@currentName AS VARCHAR(400)))
SELECT @strNameString = stuff(@strNameString, 1, @currentindex, '')
SELECT @currentindex = patindex('%,%' , @strNameString)
END

-- Logic ends to split the string in row, By this time you get all names in temp table as rows
-- SELECT * FROM @strNameString

--Get the max id and add one to creat new id which will be saved with all names
SELECT @currentID = MAX(isnull(ID,0)) from ActualTable
SET @currentID = @currentID + 1;

-- Insert all names with same id in your actual table
INSERT INTO ActualTable(id,name)
SELECT @currentID, name FROM @strNameTable

END


-- Test this as below
-- EXEC proc_ins_names 'Prashant,Sonewane'
-- SELECT * FROM ActualTable
 
Share this answer
 
Comments
babli3 10-Apr-13 5:48am    
Thank you
Hi Ms. Suman, Here I made a simple example for you.
As per your requirement you want to insert multiple value from same textbox to the different row in database with same Id. Because you are repeating the Id so you have to insert Id from your page level.
Please follow the Steps given below:-
I. Take one textbox and one button on your page :-

ASP.NET
 <asp:textbox id="txtName" runat="server" xmlns:asp="#unknown"></asp:textbox>
<asp:button id="btnSave" runat="server" text="Save" onclick="btnSave_Click" xmlns:asp="#unknown" />


2.Create a Table in Database :-
SQL
create table Test
(
 Id int,
 UserName varchar(500)
)


3.Write a Procedure to Insert the value
SQL
CREATE PROCEDURE sp_SaveValue (
	 @aintId INT
	,@astrUserName VARCHAR(500)
	)
AS
BEGIN

   CREATE TABLE #tempTable ( Id INT ,PID VARCHAR(50))
   INSERT INTO #tempTable (id, PID )
   SELECT Row AS ID ,Value AS SetValue FROM dbo.fn_SplitForOrdering(@astrUserName, ',')
   INSERT INTO Test (Id, UserName) SELECT @aintId,PID FROM #tempTable

END


You can see here I am using a function to split all the values separated by comma.
and here is the function I am using :-
SQL
CREATE FUNCTION dbo.fn_SplitForOrdering (
	@InputText VARCHAR(4000)
	,@Delimiter VARCHAR(10)
	)
RETURNS @Array TABLE (
	Row INT
	,Value VARCHAR(4000)
	)
AS
-----------------------------------------------------------  
-- returns a varchar rowset from a delimited string --  
-----------------------------------------------------------  
BEGIN
	DECLARE @Pos INT
	DECLARE @End INT
	DECLARE @TextLength INT
	DECLARE @DelimLength INT
	DECLARE @Row INT

	SET @TextLength = DataLength(@InputText)

	-- Exit function if no text is passed in  
	IF @TextLength = 0
		RETURN

	SET @Pos = 1
	SET @DelimLength = DataLength(@Delimiter)
	SET @Row = 1

	IF @DelimLength = 0
	BEGIN
		WHILE @Pos <= @TextLength
		BEGIN
			INSERT @Array (
				Row
				,Value
				)
			VALUES (
				@Row
				,SubString(@InputText, @Pos, 1)
				)

			SET @Pos = @Pos + 1
			SET @Row = @Row + 1
		END
	END
	ELSE
	BEGIN
		SET @InputText = @InputText + @Delimiter
		SET @End = CharIndex(@Delimiter, @InputText)

		WHILE @End > 0
		BEGIN
			INSERT @Array (
				Row
				,Value
				)
			VALUES (
				@Row
				,SubString(@InputText, @Pos, @End - @Pos)
				)

			SET @Pos = @End + @DelimLength
			SET @End = CharIndex(@Delimiter, @InputText, @Pos)
			SET @Row = @Row + 1
		END
	END

	RETURN
END


Now at last, on button click write following code :-
C#
string strValue = txtName.Text; //Make sure you enter your value separated by comma.
       //int id = 2; //take your id from anywhere you want.

       int id = GetMaxId(); //see this method is returning a new Id.//[Edit]
       SqlConnection con = new SqlConnection(yourconnectionstring);
       SqlCommand cmd = new SqlCommand("sp_SaveValue", con);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("@aintId", id);
       cmd.Parameters.AddWithValue("@astrUserName", strValue);
       con.Open();
       cmd.ExecuteNonQuery();
       con.Close();


That's It, Hope you got it.
[Edit]
I guess You won't do anything by your self. Any ways. to enter different id in each button click do the following things:-
Write a method that will fetch the maximum Id from your table then increase it by 1 and then insert to your table :-
C#
private int GetMaxId()
  {
      int intId = 0;
      SqlConnection con = new SqlConnection(strCon);
      SqlCommand cmd = new SqlCommand("select Max(Id) from Test", con);
      con.Open();
      SqlDataReader dr = cmd.ExecuteReader();
      if (dr.Read())
      {
          intId = int.Parse(dr[0].ToString());
      }
      return intId + 1;
  }
 
Share this answer
 
v2
Comments
babli3 10-Apr-13 5:51am    
Thanks alot. Its on every button click there will be a different id .

sorry i think i didnt ask the question properly. If example in a text box if i enter mango,bananna,apple then on save button click these values should be entered row by row with id=1 and then next time if a user enters other vaues then they should get saved as id=2 .
Hope now i have explained the question in a clear way. Please can you help me to do this. Thanks
Raje_ 10-Apr-13 6:13am    
I guess You won't do anything by your self, Please use your Mind, think...think...and think..
Ok that's It try Updated Solution.
babli3 10-Apr-13 6:23am    
thanks alot Raje
Raje_ 10-Apr-13 6:29am    
Welcome.(-_-)
babli3 10-Apr-13 6:56am    
Hi Raji

Your example is working fine but still the values are saving in the same row like
id value
2 sk sd ss

I want them to be saved like

id value
2 ss
2 sd
2 sk


Thanks. Please help
HI SUMAN

Get the ID in a session and pass the session value to the database. You can get your result.

Rgds
Jagadesh
 
Share this answer
 
Comments
babli3 10-Apr-13 4:32am    
Hi Jagadesh

Thanks but I am not very experienced . If possible can you please give me a small example. Thanks
Kumar from madras 10-Apr-13 4:47am    
HI SUMAN

For Example your ID Value = SUMAN

To create a session use the below code
Session("ID") = "SUMAN"
Now the Session("ID") Contains the value as SUMAN You can use it anywhere in your application.
TRY THIS
HI SUMAN

FOR EXAMPLE : Your id is SUMAN.

You want to insert suman in all of your rows.


To create a session use the below code.
SESSION("ID") = "SUMAN"
Now SESSION("ID") Contains the value of SUMAN You can use the session in anywhere in your application.
TRY THIS.

Rgds
Jagadesh.
 
Share this answer
 
Comments
babli3 10-Apr-13 4:46am    
ok thanks I will try ...
babli3 10-Apr-13 5:03am    
sorry i think i didnt ask the question properly.

If example in a text box if i enter mango,bananna,apple then on save button click these values should be entered row by row with id=1 and then next time if a user enters other vaues then they should get saved as id=2 .

Hope now i have explained the question in a clear way.


Please can you help me to do this.

Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900