none
System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.' RRS feed

  • Question

  • I am trying to create a database program to keep track of guests staying in residents apartments and I get the System.Data.OleDb.OleDbException: 'Syntax error in INSERT INTO statement.' error. Here is the part of the code it is stuck at

     cmd.CommandText = "INSERT INTO Guest Registration (Name, Resident, Relationship, , Phone Number, , Apt Number, Car Info, Max Number of Days) " &
                "VALUES(" & Me.txtName.Text & ",' " & Me.txtResident.Text & "','" &
                Me.cboRelationship.Text & "','" & Me.txtPhone.Text & "','" &
                Me.txtAptNumber.Text & "','" & Me.txtCarInfo.Text & "','" & Me.txtnumberofdays.Text & "')"
            cmd.ExecuteNonQuery()

    May I get some advice?

    Thursday, December 20, 2018 7:47 PM

All replies

  • Remove unneeded commas, put “[ ]” to avoid the problem of spaces, and check the usage of apostrophes:

    cmd.CommandText = "INSERT INTO [Guest Registration] ([Name], [Resident], [Relationship], [Phone Number], [Apt Number], [Car Info], [Max Number of Days]) VALUES('" & Me.txtName.Text & ",'" & Me.txtResident.Text & "','" & Me.cboRelationship.Text & "','" & Me.txtPhone.Text & "','" & Me.txtAptNumber.Text & "','" & Me.txtCarInfo.Text & "','" & Me.txtnumberofdays.Text & "')"

     

    And use Parameterised Queries next time. Meanwhile, do not type apostrophes into all of these textboxes and comboboxes.

    Thursday, December 20, 2018 8:45 PM
  • You really should place your data operations in a class and then use parameters as per below and note MS-Access parameters are ordinal so names of parameters are only meaningful to a developer, not the actual data provider.

    using System;
    using System.Data.OleDb;
    using System.IO;
    
    namespace BackEndLibrary
    {
        public class AccessOperations
        {
            private readonly OleDbConnectionStringBuilder _builder = new OleDbConnectionStringBuilder
                { Provider = "Microsoft.ACE.OLEDB.12.0" };
    
            public AccessMethods()
            {
                _builder.DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb");
            }
            /// <summary>
            /// Missing exception handling
            /// </summary>
            /// <param name="pName"></param>
            /// <param name="pContact"></param>
            /// <param name="pContactTitle"></param>
            /// <param name="pIdentfier"></param>
            public void AddNewRow(string pName, string pContact, string pContactTitle, ref int pIdentfier)
            {
                using (var cn = new OleDbConnection(_builder.ConnectionString))
                {
                    using (var cmd = new OleDbCommand { Connection = cn })
                    {
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,ContactTitle)  " + 
                                          "VALUES (@CompanyName, @ContactName,@ContactTitle)";
    
                        cmd.Parameters.AddWithValue("@CompanyName", pName);
                        cmd.Parameters.AddWithValue("@ContactName", pContact);
                        cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle);
                        cn.Open();
    
                        // insert
                        cmd.ExecuteNonQuery();
                        // get new id
                        cmd.CommandText = "Select @@Identity";
                        pIdentfier = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, December 20, 2018 10:42 PM
    Moderator
  • Ok. That error went away now there is a new one. There is also System.Data.OleDb.OleDbException: 'Syntax error in string in query when I enter data and click submit. Within the same code. 
    Thursday, December 20, 2018 11:29 PM
  • Show your current code that gives the error, and some details about entered data.


    Friday, December 21, 2018 5:38 AM
  • Hi Jake,

    Is there any update, it will be beneficial to resolve the issue if you share reproducible code.

    Best regards,

    Zhanglong


    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.

    Monday, December 24, 2018 9:30 AM
    Moderator