How can i insert the values into sql database

prathyusha

13y2.6k.NET

hi,

how can i insert the values into sql database

ex:manish,rama,raju etc.......

these fields are inserted into table.may be the fields are 3 or 4 or etc.not particular numbers

please help me.

19 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
Hi,
First you need to change the Table definatition of TableMain as shown below

  CREATE TABLE TableMain
(
Id int identity,
Value varchar(50)
)

Then if you want keep duplicate value then modify the SP as shown below

  ALTER PROCEDURE dbo.[usp_SaveCommaSeparatedValue]
( @Input varchar(1000) )
AS SET NOCOUNT ON

INSERT INTO dbo.[TableMain](Value)
SELECT value FROM [dbo].[udf_SplitCsvToTable](@Input,',')

SET NOCOUNT OFF

Regards,
Iftikar

Remember to click "Mark as Answer" on the post, if it helps you.
0
manjula d13y ago
using stored procedure how to update the data
0
Bineesh Viswanath13y ago
Hi

i don't know how much fields your table has.

A database will became unfruitful if the same group of values being repeated.

to avoid these, u need a condition in the stored procedure.

In variable declaration, can avoid Pk_column in insert because it will auto generated if set this to IDENTITY

here is the stored procedure to insert data into database.

CREATE PROCEDURE tbl_Name
// variable declaration
@name nvarchar(20),

//condition to avoid data duplication
if not exists(select name from tbl_Name where name=@name)
begin
insert into tbl_Name(name)
values(@name)
end

this statement is an easy way to add as we don't have to go table each time of insertion.

when execute being clicked, a window opens for enter the name into the table.

if this statement tested successfully, accept it.
0
Josephs13y ago
hi

Iftikar Hussain

why using using block what is advantages?
0
prathyusha13y ago
thank u very much. it's working perfectly......
0
prathyusha13y ago
alter PROCEDURE dbo.[usp_SaveCommaSeparatedValue]

( @Input varchar(1000) )
AS SET NOCOUNT ON

DELETE FROM dbo.[TableMain] WHERE value=value

INSERT INTO dbo.[TableMain](idx,Value)
SELECT idx,value FROM [dbo].[udf_SplitCsvToTable](@Input,',') SET NOCOUNT OFF

exec dbo.[usp_SaveCommaSeparatedValue] 'aaa,bbb,cccc' output like this
0aaa
1bbb
2cccc

exec dbo.[usp_SaveCommaSeparatedValue] 'aarra,bbb,cccc' first recod is deleted

0aarra
1bbb
2cccc

but i need like this

0aaa
1bbb
2cccc
3aaarra
4bbb
5cccc
when i execute that it will be inserted. when iexecute second time second time records is there only i need full records

please help me
0
Iftikar Hussain13y ago
That delete statement you need to execute before executing the insert statement inside the Stored Procedure

Regards,
Iftikar
0
prathyusha13y ago
hi Vulpes
how can we insert depending on length.
please help me
0
prathyusha13y ago
if i use that command table is empty. i want to fill the table continuously.
please help me
0
Iftikar Hussain13y ago
Hi,
before inserting the data into Table, always execute the delete statement with the primary key condition.
DELETE FROM TableMai WHERE PK_Column=value

Let me know if you need more information

Regards,
Iftikar

Remember to click "Mark as Answer" on the post, if it helps you.
0
prathyusha13y ago
hi,
thank u very much for your help.

exec dbo.[usp_SaveCommaSeparatedValue] 'aaa,bbb,cccc' it is working and inserted successfully.

but when i execute that procedure second time like
exec dbo.[usp_SaveCommaSeparatedValue] 'd2,t3,ty4'

this error will be displayed

Msg 2627, Level 14, State 1, Procedure usp_SaveCommaSeparatedValue, Line 8 Violation of PRIMARY KEY constraint 'PK__TableMai__DC501A787132C993'. Cannot insert duplicate key in object 'dbo.TableMain'. The statement has been terminated.
and by using coding

incmd = new SqlCommand();
incmd.Connection = cn;
//cn.Open();
incmd.CommandType = CommandType.StoredProcedure;
incmd.CommandText = "usp_SaveCommaSeparatedValue";
//cmd.Parameters.AddWithValue("@Sno", "0");
cmd.Parameters.AddWithValue("@Input", txtkey.Text.Trim());
SqlDataAdapter da_in = new SqlDataAdapter(incmd);
DataSet ds_in = new DataSet();
da_in.Fill(ds_in);
Procedure or function 'usp_SaveCommaSeparatedValue' expects parameter '@Input', which was not supplied.this error will be displayed .

please help me.it's very important to me
0
Vulpes13y ago
If you don't want to use a stored procedure, then you can use the String.Split method to split up the textbox contents into parts:

string[] items = textBox1.Text.Split(',');

and then have different INSERT INTO queries depending on the value of items.Length.
0
Iftikar Hussain13y ago
Hi,
First create one User Defined Function in sql as shown below
CREATE FUNCTION [dbo].[udf_SplitCsvToTable](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE
BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END

Then create on new SP and pass comma separated value into it

CREATE PROCEDURE usp_SaveCommaSeparatedValue
(
@Input varchar(1000)
)
AS
SET NOCOUNT ON

INSERT INTO TableMain(Id,Value)
SELECT idx,value FROM udf_SplitCsvToTable(@Input,',')

SET NOCOUNT OFF

exec usp_SaveCommaSeparatedValue 'aaa,bbb,cccc'

Let me know if you need more information

If your SQL version is 2005 or 2000 then I will provide different solution

Regards,
Iftikar

Remember to click "Mark as Answer" on the post, if it helps you.
0
prathyusha13y ago
by using stored procedure or code how can it's possible
0
Iftikar Hussain13y ago
Hi,
There is two way you can do this. First tell me are you trying to insert the data directly from your code or by using Stored Procedure?

Regards,
Iftikar
0
prathyusha13y ago
i have only one textbox.in that ihave word like string s="aaa,bc,ccc"; now i want to insert that string in to another table like
aaa
bc
ccc
and one more thing that string not contains particular number of parts. it may be 1 or 3 or like.
please help me.....
0
Iftikar Hussain13y ago
Hi,
You can do like below
protected void BtnSave_Click(object sender, EventArgs e)
{
var firstName = TxtBxFirstNm.Text;
var lastName = TxtBxLastNm.Text;
var address = TxtBxDesc.Text.Trim();
try
{
using (SqlConnection conn = new SqlConnection(@"Data Source=SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=password"))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
string insertString = @"insert into EmployeesBackUp(FirstName,LastName, Address) values(@FirstName, @LastName, @Address)";
SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.Parameters.AddWithValue("@FirstName", firstName);
cmd.Parameters.AddWithValue("@LastName", lastName);
cmd.Parameters.AddWithValue("@Address", address);
cmd.ExecuteNonQuery();
conn.Close();
Response.Write("Data is Saved");
}
}
catch (Exception ex)
{

Response.Write(ex.Message);
}
}

Regards,
Iftikar

Remember to click "Mark as Answer" on the post, if it helps you.
0
prathyusha13y ago
actually i need to insert one word like aaa,bbb,ccc,wewrwr.after that the word will be inserted into another table. and that word have like aa only.or aaa,ddd then also it will be inseted into that table. that table has only one field keywords
0
Vulpes13y ago
Check this link for how to INSERT into a SQL database:

http://www.w3schools.com/sql/sql_insert.asp

If you're doing this from .NET, then the precise code will depend on which database you're using as there are different 'providers' available. This link applies to SQL Server:

http://msdn.microsoft.com/en-us/library/ms233812(v=vs.100).aspx
0