Ignore Comma with Single quote in sql insert statement

Question

0.00/5 (No votes)

See more:

i have value like following

strNm="'MyData','MySinger'"

while inserting or updating it give me error

my query is
insert into Table values('" + strNm + "')

Posted 12-Feb-13 22:12pm

hareshdgr8

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2013-02-12T22:23:00

Don't do it like that!
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. Your problem will magically disappear at the same time!
And always list your column names when inserting records!

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("INSERT INTO myTable (myColumn1) VALUES (@C1)", con))
        {
        com.Parameters.AddWithValue("@C1", strNm);
        com.ExecuteNonQuery();
        }
    }

Using con As New SqlConnection(strConnect)
    con.Open()
    Using com As New SqlCommand("INSERT INTO myTable (myColumn1) VALUES (@C1)", con)
        com.Parameters.AddWithValue("@C1", strNm)
        com.ExecuteNonQuery()
    End Using
End Using

gvprabu · Answer 2 · 2013-02-12T23:01:00

Hi ,

Check the following Script,

SQL

-- If Object Exists then Drop
IF OBJECT_ID('split') IS NOT NULL DROP FUNCTION dbo.split
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
GO
-- Create Split Function
CREATE FUNCTION [dbo].[split](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
RETURN
END
GO
-- Create Temp Table 
CREATE TABLE #Test(Name VARCHAR(100))

DECLARE @DataString VARCHAR(1000)='MyData,MySinger'

INSERT INTO #Test(Name)
SELECT Val FROM dbo.split(@DataString, ',')

-- Actual Output
SELECT Name FROM #Test

Regards,
GVPrabu

*Karthik Harve* · Answer 3 · 2013-02-12T22:26:00

Solution 2

Hi,

try this query
strNm="(SELECT 'MyData' UNION ALL SELECT 'MySinger')"

SQL

insert into Table(column1) + strNm +

you are using single quotes twice. one at value assignment and other at query. remove the single quote at query, it will work.

hope it helps.

Posted 12-Feb-13 22:26pm

Karthik Harve

Updated 12-Feb-13 22:34pm

v2

Comments

hareshdgr8 13-Feb-13 4:27am

but there is only single columns where the value has been inserted

Karthik Harve 13-Feb-13 4:34am

try with updated answer.

hareshdgr8 · Answer 4 · 2013-02-12T22:41:00

Solution 3

Dim abc As String = "'Abc','CEEE',"

sqlselect.CommandText = "insert into table1(Name1) values (@a)"
sqlselect.Parameters.AddWithValue("@a", abc)
sqlselect.ExecuteNonQuery

Posted 12-Feb-13 22:41pm

hareshdgr8

RDBurmon · Answer 5 · 2013-02-12T23:36:00

SQL

DECLARE @strNm AS NVARCHAR(MAX)

SET @strNm='''MyData'',''MySinger'''

DECLARE @SQLString AS NVARCHAR(MAX)
DECLARE @ParmDefinition AS NVARCHAR(MAX)

SET @SQLString =
     N'insert into temptbl(name,name1) values(' + @strNm + ')'


EXECUTE(@SQLString)

Please replace your table name and field name in above query

Ignore Comma with Single quote in sql insert statement

5 solutions

Solution 1

Solution 4

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0