none
Wrongful acces database insertion RRS feed

  • Question

  • I'm having trouble with inserting information in a database, i'm working on a project where i need to be able to store the location of trainwagons in a database. Each column stands for a different track but when i insert a train in column the next one i insert in a different column will be in the next row and this can't happen, they need to be able to be in the same column. I will also post my insert code that i wrote in c#. So my question is, does anybody know how i can fix this ?.

    Kind regards Arno.

    ps. I wasn't able to put in the picture because my account isn't verified. I hope it's clear enough.

    static void Insert(string data,string spoor) // Insert wagons in the database { OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Arno\Documents\Code\Visual Studio\Database_GIP20161.accdb; Persist Security Info=False;"; connection.Open(); string query = String.Format(@"INSERT INTO Posities({0}) VALUES ('{1}');", spoor, data); OleDbCommand command = new OleDbCommand(); command.Connection = connection; command.CommandText = query; command.ExecuteNonQuery(); connection.Close(); }

    • Edited by ArnoDeG Wednesday, January 16, 2019 6:11 PM Forgot a picture
    Wednesday, January 16, 2019 6:09 PM

All replies

  • Try this code, see comments within

    using System.Data.OleDb;
    namespace TransactionsViewer // change namespace name
    {
        public class Operations
        {
    	    public void Insert(string data, string spoor) // Insert wagons in the database
    	    {
                /*
                 * Place a breakpoint on the next line of code, examine incoming values
                 */
    
    			var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data " + 
    			                       @"Source=C:\Users\Arno\Documents\Code\Visual Studio\Database_GIP20161.accdb; " + 
    			                       "Persist Security Info=False;";
    
    		    var insertStatement = $"INSERT INTO Posities ({spoor}) VALUES @Value";
    
                using (var cn = new OleDbConnection() {ConnectionString = connectionString })
    		    {
    			    using (var cmd = new OleDbCommand() {Connection = cn, CommandText = insertStatement })
    			    {
                        cn.Open();
    				    cmd.Parameters.AddWithValue("@Value", data);
    				    cmd.ExecuteNonQuery();
    			    }
    		    }
    	    }
        }
    }

    EDIT: Wow, syntax is way off after posting


    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


    Wednesday, January 16, 2019 7:20 PM
    Moderator
  • Hi Karen thanks for replying so fast, when I use your code I get an syntax error in the INSERT INTO statement (see text below). I checked the data with a breakpoint like you said and with the data is nothing wrong.
    System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement.
       bij System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       bij System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       bij System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       bij System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       bij System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       bij System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       bij Server_GIP.Program.Insert(String data, String spoor) in C:\Users\bobba\Documents\Code GIP\Visual Studio GIP\Visual studio c#\Server_GIP\Server_GIP\Program.cs:regel 114
       bij Server_GIP.Program.Main(String[] args) in C:\Users\bobba\Documents\Code GIP\Visual Studio GIP\Visual studio c#\Server_GIP\Server_GIP\Program.cs:regel 64


    ARG

    Thursday, January 17, 2019 7:03 PM
  • Try this

    INSERT INTO Posities ({spoor}) VALUES (@Value)
    And if that fails, replace the field name as is dynamic to hard coded to a real field name for testing only. It has two be one or the other.


    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, January 17, 2019 7:49 PM
    Moderator
  • Now the INSERT command works again but he still inserts the data the same way as before. But thanks for your fast replies and help !

    ARG

    Thursday, January 17, 2019 8:01 PM
  • Put a breakpoint on the following line to ensure it's not called more than once.

     cmd.ExecuteNonQuery();


    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, January 17, 2019 8:40 PM
    Moderator
  • Hi ArnoDeG,

    Is there any update? Is it still exist, please feel free let us know.

    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, February 4, 2019 7:48 AM
    Moderator