-1

I create a table named Table with members (first name, last name, address). The program is throwing the error "incorrect syntax near the keyword 'table'". The application is to insert the data into the table. The code is for the new button exception handler.

    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace Week4
{
    public partial class Form1 : Form
    {
        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\cvyc8\Documents\Testing.mdf;Integrated Security=True;Connect Timeout=30");
        public Form1()
        {
            InitializeComponent();
        }

        private void txtSearch_TextChanged(object sender, EventArgs e)
        {

        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "insert into [Member] values ('" + txtFirstName.Text + "', '" + txtLastName.Text + "', '" + txtAddress.Text + "')";
            cmd.ExecuteNonQuery();
            con.Close();

            MessageBox.Show("Member added successfully");
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {

        }

        private void btnCancel_Click(object sender, EventArgs e)
        {

        }

        private void btnSave_Click(object sender, EventArgs e)
        {

        }
    }
}
CVYC CVYC
  • 75
  • 1
  • 8
  • `[Table]` and Sql Injection and you have extra `)` – Fabio Nov 20 '19 at 03:15
  • I see you updated your table name from 'table' to '[member]'. If you are still getting the exact same error message which says keyword 'table' then you are not running your new code. – TomC Nov 20 '19 at 03:36
  • @TomC, new error, Column name or number of supplied values does not match table definition.' – CVYC CVYC Nov 20 '19 at 03:47
  • Try setting a breakpoint and stepping through. The message is explicit and says 'table', so you are NOT running the commandtext you think you are. – TomC Nov 20 '19 at 03:49
  • [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) – marc_s Nov 20 '19 at 04:54

3 Answers3

1

You need to use parameters to avoid sql injection 

string sql = "insert into Member(col1, col2, col3) values(@val1, @val2, @val3)";

using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\cvyc8\Documents\Testing.mdf;Integrated Security=True;Connect Timeout=30"))
{
  connection.Open();
  using (SqlCommand cmd= new SqlCommand(sql, connection))
  {
     md.Parameters.Add("@val1", SqlDbType.Varchar, 50).value = txtFirstName.Text;  
     cmd.Parameters.Add("@val2", SqlDbType.Varchar, 50).value =  txtLastName.Text;
     cmd.Parameters.Add("@val3", SqlDbType.Varchar, 50).value = txtAddress.Text;
     cmd.ExecuteNonQuery();
  }
     MessageBox.Show("Member added successfully");
}
Saif
  • 2,611
  • 3
  • 17
  • 37
0

table is a reserved keyword in SQL including MSSQL.

Below code could work, but I strongly recommend not to use reserved keyword for a table name. (related answer: https://stackoverflow.com/a/695626/361100 )

cmd.CommandText = ("insert into [table] values ('"+txtFirstName.Text+"', '"+txtLastName.Text+"', '"+txtAddress.Text+"'))");
Youngjae
  • 24,352
  • 18
  • 113
  • 198
  • I created new table called Member. Still getting error: updated code cmd.CommandText = "insert into [Member] values ('" + txtFirstName.Text + "', '" + txtLastName.Text + "', '" + txtAddress.Text + "')"; – CVYC CVYC Nov 20 '19 at 03:28
  • Does it say "incorrect syntax near the keyword 'table'"? If so you are not running the new code. – TomC Nov 20 '19 at 03:35
0

The error is general because it is not able to understand the '[Member]' in your code. It seems suspicious in [Member]. Your table name is Table, but you cannot use it since it is a reserved keyword. Try writing this way.

private void btnNew_Click(object sender, EventArgs e)
    {
        con.Open();
        SqlCommand cmd = con.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into Member("FirstName","LastName","Address") values ('" + txtFirstName.Text + "', '" + txtLastName.Text + "', '" + txtAddress.Text + "')";
        cmd.ExecuteNonQuery();
        con.Close();

        MessageBox.Show("Member added successfully");
    }

Note: The ("FirstName","LastName","Address") are your table fields. Make sure your Table matches the cases (upper and lower) with that of your database table.

Hope this helps.

Rohan Rao
  • 2,505
  • 3
  • 19
  • 39
  • There has been some confusion her @Rohan_Rao because the OP has edited the code from 'table' to 'member' after it was pointed out that 'table' is a reserved word. (By which your solution would fail too). – TomC Nov 20 '19 at 04:13
  • @TomC Got it. Thanks for pointing out. I have changed the table name to Member. – Rohan Rao Nov 20 '19 at 04:30