SQL Syntax Error (INSERT command)

Question

I have a form with a text box and button, such that when the user clicks the button, the specified name in the text box is added to a table in my sql database. The code for the button is as follows:

private void btnAddDiaryItem_Click(object sender, EventArgs e)
{
   try
   {
       string strNewDiaryItem = txtAddDiaryItem.Text;
       if (strNewDiaryItem.Length == 0)
       {
           MessageBox.Show("You have not specified the name of a new Diary Item");
           return;
       }
       string sqlText = "INSERT INTO tblDiaryTypes (DiaryType) VALUES = ('" + strNewDiaryItem + "');";
       cSqlQuery cS = new cSqlQuery(sqlText, "non query");
       PopulateInitialDiaryItems();
       MessageBox.Show("New Diary Item added succesfully");
   }
   catch (Exception ex)
   {
       MessageBox.Show("Unhandled Error: " + ex.Message);
   }
}

The class cSqlQuery is a simple class that executes various T-SQL actions for me and its code is as follows:

class cSqlQuery
{
    public string cSqlStat;
    public DataTable cQueryResults;
    public int cScalarResult;

    public cSqlQuery()
    {
        this.cSqlStat = "empty";
    }

    public cSqlQuery(string paramSqlStat, string paramMode)
    {
        this.cSqlStat = paramSqlStat;

        string strConnection = BuildConnectionString();
        SqlConnection linkToDB = new SqlConnection(strConnection);

        if (paramMode == "non query")
        {
            linkToDB.Open();
            SqlCommand sqlCom = new SqlCommand(paramSqlStat, linkToDB);
            sqlCom.ExecuteNonQuery();
            linkToDB.Close();
        }

        if (paramMode == "table")
        {
            using (linkToDB)
            using (var adapter = new SqlDataAdapter(cSqlStat, linkToDB))
            {
                DataTable table = new DataTable();
                adapter.Fill(table);
                this.cQueryResults = table;
            }
        }

        if (paramMode == "scalar")
        {
            linkToDB.Open();
            SqlCommand sqlCom = new SqlCommand(paramSqlStat, linkToDB);
            this.cScalarResult = (Int32)sqlCom.ExecuteScalar();
            linkToDB.Close();
        }
    }

    public cSqlQuery(SqlCommand paramSqlCom, string paramMode)
    {
        string strConnection = BuildConnectionString();
        SqlConnection linkToDB = new SqlConnection(strConnection);
        paramSqlCom.Connection = linkToDB;

        if (paramMode == "table")
        {
            using (linkToDB)
            using (var adapter = new SqlDataAdapter(paramSqlCom))
            {
                DataTable table = new DataTable();
                adapter.Fill(table);
                this.cQueryResults = table;
            }
        }

        if (paramMode == "scalar")
        {
            linkToDB.Open();
            paramSqlCom.Connection = linkToDB;
            this.cScalarResult = (Int32)paramSqlCom.ExecuteScalar();
            linkToDB.Close();
        }
    }

    public string BuildConnectionString()
    {
        cConnectionString cCS = new cConnectionString();
        return cCS.strConnect;
    }        
}

The class works well throughout my application so I don't think the error is in the class, but then I can't be sure.

When I click the button I get the following error message:

Incorrect syntax near =

Which is really annoying me, because when I run the exact same command in SQL Management Studio it works fine.

I'm sure I'm missing something rather simple, but after reading my code through many times, I'm struggling to see where I have gone wrong.

Look into parameterized queries. Never go to the database with unsanitized user input. — Anthony Pegram
– Anthony Pegram, Commented Jan 30, 2012 at 13:07
try entering this in the form: MyDiaryItem'); DROP TABLE tblDiaryTypes. Then read some of the links in this google search: google.com/search?q=sql+injection — jeroenh
– jeroenh, Commented Jan 30, 2012 at 13:08

Ravi Gadag · Accepted Answer · 2012-01-31 01:30:43Z

11

you have to remove = after values.

string sqlText = "INSERT INTO tblDiaryTypes (DiaryType) VALUES ('" + strNewDiaryItem + "');"

and try to use Parameterized queries to avoid Sql injection. use your code like this. Sql Parameters

 string sqlText = "INSERT INTO tblDiaryTypes (DiaryType) VALUES (@DairyItem);"
    YourCOmmandObj.Parameters.AddwithValue("@DairyItem",strNewDiaryIItem)

edited Jan 31, 2012 at 1:30

answered Jan 30, 2012 at 13:05

Ravi Gadag

15.9k5 gold badges60 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tobiasbayer · Accepted Answer · 2012-01-30 13:02:22Z

10

Remove the = after VALUES.

answered Jan 30, 2012 at 13:02

tobiasbayer

10.4k4 gold badges48 silver badges66 bronze badges

Comments

benni_mac_b · Accepted Answer · 2012-01-30 13:04:21Z

7

You do not need the =

A valid insert would look like

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

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

answered Jan 30, 2012 at 13:04

benni_mac_b

8,9075 gold badges42 silver badges60 bronze badges

1 Comment

PJW Over a year ago

damn it - I said it would be something simple :s Thank you everyone.

Amol M Kulkarni · Accepted Answer · 2013-02-04 10:20:04Z

0

Please use following:

insert into <table name> Values (value);

edited Feb 4, 2013 at 10:20

Amol M Kulkarni

21.7k34 gold badges125 silver badges165 bronze badges

answered Feb 4, 2013 at 9:59

bhavesh Khatri

413 bronze badges

Comments

Sarim Shekhani · Accepted Answer · 2012-01-30 13:10:11Z

-1

Remove "=", and also i would recommend you to use string.format() instead of string concatenation.

sqlText = string.format(INSERT INTO tblDiaryTypes (DiaryType) VALUES ('{0}'), strNewDiaryItem);"

answered Jan 30, 2012 at 13:10

Sarim Shekhani

1281 silver badge5 bronze badges

1 Comment

jeroenh Over a year ago

String.Format is not going to save you here. Never directly use user input in a SQL query. Use parameterized queries instead.

Collectives™ on Stack Overflow

SQL Syntax Error (INSERT command)

5 Answers 5

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related