none
Insert data to sql RRS feed

  • Question

  • Hello,
    I'm trying to learn from this video:


    When I try execute my code,I always got mistake "System.Data.SqlClient.SqlException: 'Incorrect syntax near 'value'.'
    "

    The program stops at this line "cmd.ExecuteNonQuery();"


    Maybe somebody can give suggest How solve this problem?

    My code:

    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 WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            public string conString = "Data Source=SARUNAS-PC\\dragunai;Initial Catalog=connectionDB;Integrated Security=True";
            private void Label1_Click(object sender, EventArgs e)
            {
            }
            private void Button1_Click(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection(conString);
                con.Open();
                if (con.State == System.Data.ConnectionState.Open)
                {
                    string q = "INSERT INTO Test(ID,name) value('" + txtID.Text.ToString() + "','" + txtname.Text.ToString() + "')";
                    SqlCommand cmd = new SqlCommand(q, con);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Connection made Successfully");
                }
                con.Close();
            }
        }
    }

     
    • Edited by sarunasz Friday, September 6, 2019 8:56 AM
    Friday, September 6, 2019 8:54 AM

All replies

  • Hi sarunasz,

    Thank you for posting here.

    You could try the following code to solve this exception.

       string q = "INSERT INTO Test(ID,name) values('" + txtID.Text.ToString() + "','" + txtname.Text.ToString() + "')";

    You should use 'values' instead of 'value'.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Ehsan Sajjad Friday, September 6, 2019 9:46 AM
    Friday, September 6, 2019 9:06 AM
    Moderator
  • Thank, Now the code working
    Friday, September 6, 2019 9:20 AM
  • Hi

    Thanks for the feedback.

    >>Thank, Now the code working

    You could mark it as an answer so that it will help other members to find the solution quickly if they face the similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 6, 2019 9:23 AM
    Moderator
  • Hello,

    Although you have a working solution I must indicate there are a good deal of issues with this code and will address each one. I feel this is important as it appears you are just starting out and that now is the time to correct these issues.

    • Each table in a database 99 percent of the time requires a primary key that auto increments rather than attempting to handle this yourself which will backfire on you sooner or later. Note in the example below The new identifier is returned via out var (You can declare out values inline as arguments to the method where they're used.).
    • Anytime you access a table e.g. Test table in your case always prefix with the schema which 99.99 percent of the time is dbo (as in the code below).
    • DO NOT write data operations directly in a form, instead use a class that sole purpose is to interact with your database.
    • DO NOT create a single connection for data operations, instead one connection per data operation although there are time when a connection will be used for multiple operations in a method e.g. inserting a record into a master table then inserting a record into a child table.

      Note in your case you check to see if the connection is open but it could be open and broken.
    • Use parameters for each value going to a INSERT INTO (and UPDATE and WHERE on a SELECT). Why? Let's look at a simple example, you want to insert a value for last name which is O'Brian. Without a parameter the database will kick back with an error as the name has an unescaped apostrophe while with a parameter O'Brian turns into O''Brian and O'Brian is inserted.
    • Give variable names meaningful names. con and cmd are fine but q for a command parameter is not.
    • Look at how I create the connection and command via using statements which close and dispose of those objects for you.
    • Note how I use cmd.ExecuteScalar to get the new identifier which comes from the secondary query (SQL-Server accepts multiple queries and this can be done with read queries to where the data provider has methods to return your data).

      When using ExecuteNonQuery get use to checking the return value.

    • Note how exception handling is done in the code sample below. This is subjective, ask five developers and maybe two will agree on how to handle errors.

    Very easy/simple data class

    public class DataOperations
    {
        private bool _hasException;
        public bool HasException => _hasException;
        private Exception _lastException;
        public Exception LastException => _lastException;
    
        private string ConnectionString = "Data Source=SARUNAS-PC\\dragunai;" + 
                                           "Initial Catalog=connectionDB;" + 
                                           "Integrated Security=True";
    
    
        public bool Add(string pFirstName, out int pIdentifier)
        {
            _hasException = false;
    
            using (var cn = new SqlConnection() {ConnectionString = ConnectionString})
            {
                using (var cmd = new SqlCommand() {Connection = cn})
                {
                    cmd.CommandText = "INSERT INTO dbo.test (FirstName) VALUES (@FirstName);" + 
                                      "SELECT CAST(scope_identity() AS int);";
    
                    cmd.Parameters.AddWithValue("@Name", pFirstName);
    
                    try
                    {
                        cn.Open();
    
                        pIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
    
                        return true;
                    }
                    catch (Exception error)
                    {
                        _hasException = true;
                        _lastException = error;
    
                        pIdentifier = -1;
    
                        return false;
    
                    }
                }
            }
    
        }
    }

    Simple usage

    public partial class Form1 : Form
    {
        private DataOperations _dataOperations = new DataOperations();
        public Form1()
        {
            InitializeComponent();
        }
    
        private void AddNewRecordButton_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(FirstNameTextBox.Text))
            {
    
                MessageBox.Show(_dataOperations.Add(FirstNameTextBox.Text, out var newIdentifier) ? 
                    $"New identifier is {newIdentifier}" : 
                    "Insert failed");
            }
            else
            {
                MessageBox.Show("Requires a first name");
            }
        }
    }

    What else? Rather than write code in a form, write a unit test using the data class to ensure the method Add in this case works.

    Hopefully you will take time to read the above and learn from this information.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, September 6, 2019 10:48 AM
    Moderator
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer. So that it will help other members to find the solution quickly if they face the similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 12, 2019 8:56 AM
    Moderator