CODEWITHSUNDEEP
c#sql
PJW
PJW

Reputation: 5397

SQL Syntax Error (INSERT command)

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.

Upvotes: 2

Views: 809

Answers (5)

bhavesh Khatri
bhavesh Khatri

Reputation: 41

Please use following:

insert into <table name> Values (value);

Upvotes: 0

Ravi Gadag
Ravi Gadag

Reputation: 15851

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)

Upvotes: 11

Sarim Shekhani
Sarim Shekhani

Reputation: 128

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);"

Upvotes: -1

benni_mac_b
benni_mac_b

Reputation: 8877

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

Upvotes: 7

tobiasbayer
tobiasbayer

Reputation: 10369

Remove the = after VALUES.

Upvotes: 10

Related Questions