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: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 :-
create table Test
(
Id int,
UserName varchar(500)
)
3.Write a Procedure to Insert the value
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 :-
CREATE FUNCTION dbo.fn_SplitForOrdering (
@InputText VARCHAR(4000)
,@Delimiter VARCHAR(10)
)
RETURNS @Array TABLE (
Row INT
,Value VARCHAR(4000)
)
AS
BEGIN
DECLARE @Pos INT
DECLARE @End INT
DECLARE @TextLength INT
DECLARE @DelimLength INT
DECLARE @Row INT
SET @TextLength = DataLength(@InputText)
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 :-
string strValue = txtName.Text;
int id = GetMaxId();
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 :-
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;
}