Error when inserting a value containing an apostro

Question

I'am very new with text datatype in SQL Server 2008

I created table:

CREATE TABLE sample(id int identity,name varchar(20),Yell_Your_self text) 

but I am facing problem with inserting

insert into sample values('ganesh','welcome to india')
insert into sample values('ganesh','welcome to india's largest temple')

First statement is working fine but how to execute second statement?

Answer 1

The issue is that you have a single quote in the string, and a single quote is a string delimiter in SQL.

Try:

INSERT INTO  sample VALUES ('ganesh','welcome to india''s largest temple')

Answer 2

Likely throwing an error on the apostrophe in the india's

insert into sample values('ganesh','welcome to india''s largest temple')

Answer 3

As you are already aware that the trouble SQL is facing is to escape an apostrophy in query, which is fixed i guess. To answer your second question about how to pass value from front end like C#, parametrized query is one of the good approach. As an alternative you can go with this one also which involves string manipulations to prepare a query:

public int get(string val1,string val2) 
{ 
    string temp = string.Concat(val1,"," + val2); // concatenate all your params
    temp = temp.replace("'","''").replace(",","','");  // replace any single qoute with escaped single quote
    string s="insert into sample values('" + temp +  "')"; // append altered string in query
    SqlCommand cmd = new SqlCommand(s,con); 
    con.Open(); 
    int i = cmd.ExecuteNonQuery(); 
    con.Close(); 
    return i; 
}

Hope it helps!

Answer 4

try this with passing dynamic value

insert into sample values(@name, N''' + @Yell_Your_self text + ''' )

Answer 5

Try this one -

IF OBJECT_ID(N'dbo.sample') IS NOT NULL
   DROP TABLE dbo.[sample]

CREATE TABLE dbo.[sample]
(
      id INT IDENTITY(1,1) PRIMARY KEY
    , name VARCHAR(20)
    , Yell_Your_self VARCHAR(2000)
)

INSERT INTO dbo.[sample] 
VALUES
    ('ganesh', 'welcome to india'), 
    ('ganesh', 'welcome to india''s largest temple')

Update3:

public int get(string val1,string val2) 
{ 
    using(SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();

        int i = 0;

        using (SqlCommand cmd = new SqlCommand("INSERT INTO dbo.sample (name, Yell_Your_self) VALUES(@val1, @val2)", con))
        {
            cmd.Parameters.AddWithValue("@val1", val1);
            cmd.Parameters.AddWithValue("@val2", val2);
            i = cmd.ExecuteNonQuery();
        }

        con.Close();
        return i;
    }
}