CODEWITHSUNDEEP
c#sql-serversql-insert
Buğra
Buğra

Reputation: 23

Ignoring Quotes in Insert String C#, SQL Server

I can't insert NULL value into a Date column in my database because I need to use a string variable.

Insert statement:

string insertQuery = "INSERT INTO PersonelListesi (id, ssk_tarih) VALUES(1,'" + sskTarihi + "')";

Working variable example:

string sskTarihi = "20201212";

Incorrect variable examples (which I have tried):

sskTarihi = "NULL";

System.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'

sskTarihi = "'NULL'";

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'NULL'

sskTarihi = "\"'NULL'\"";

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'NULL'

sskTarihi = "\"NULL\"";

System.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'

sskTarihi = "\'NULL\'";

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'NULL'

sskTarihi = "\"\'NULL\'\"";

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'NULL'

I hope you got what i mean and what i need. I appreciate the help. Thanks for your answers in advance. :)

Upvotes: 1

Views: 345

Answers (1)

Karen Payne
Karen Payne

Reputation: 5117

As others recommended use parameters as shown below where the method resides in a class.

public static void InsertRow(int id, string ssk_tarih)
{
    using (var cn = new SqlConnection {ConnectionString = "TODO"})
    {
        using (var cmd = new SqlCommand {Connection = cn})
        {

            cmd.CommandText = "INSERT INTO PersonelListesi (id, ssk_tarih) VALUES(@id,@ssk_tarih)";


            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

            if (string.IsNullOrWhiteSpace(ssk_tarih))
            {
                cmd.Parameters.Add("@ssk_tarih", SqlDbType.DateTime).Value = DBNull.Value;
            }
            else
            {
                cmd.Parameters.Add("@ssk_tarih", SqlDbType.DateTime).Value = ssk_tarih;
            }

            cn.Open();

            cmd.ExecuteNonQuery();

        }
    }

}

Upvotes: 1

Related Questions