none
Adding to Access DB Though a C# ODBC Connection RRS feed

  • Question

  • I am currently learning how to manage an Access Database through C# visual, but some Errors appear, of the 139 entries that I try to add, 19 entries (Same ones every time) are constantly blocked. the entries are stored in their own class (called Item) instance, I have a method that translates them into rows by auto creating the SQL Insert command, storing the command in a list and then executing the list of commands within a loop later

    DB Cols - ID, KnownName, NameEng, NameJap, Studio, Status, FinStatus, Cover, Type, StartDate, WatchedEpisodes, SkippedWeeks, TotalEpisodes, Rating, Tags, Hype

               

     public static void AddEntry(Item r)
    {
    R.SetID(); //Creates a Random ID based on the available instance variables in the Item

    string SQLCMD = "INSERT INTO AniChart VALUES(";
    SQLCMD += r.SQLColStr();// Binds each instance variable in single quotes ( '  ' ) and orders them according to row
    SQLCMD += ")";

    AddCommands.Add(SQLCMD);
    }

    public static void Save()
    {
    foreach (string a in Lists.UpdateList.Keys) UpdateEntry(a, Lists.UpdateList[a]);
    foreach (string a in Lists.DeleteList) DeleteEntry(a);
    foreach (Item a in Lists.AddList) AddEntry(a);
    AniListCon.Open();//the connection

    foreach (string SQLCMD in DelCommands)
    {
       if (AniListCon.State != ConnectionState.Open) AniListCon.Open();
       OdbcCommand tem = new OdbcCommand(SQLCMD, AniListCon);
       tem.ExecuteReader();
    }
    foreach (string SQLCMD in AddCommands)
    {
       if (AniListCon.State != ConnectionState.Open) AniListCon.Open();
       OdbcCommand tem = new OdbcCommand(SQLCMD, AniListCon);
       tem.ExecuteReader();
    }
    AniListCon.Close();
    }

    When I add the Rejected entry, the SQL Command looks like this -  INSERT INTO AniChart VALUES('NKunZiGoNJunZiGoSoNEh igsAaaG.3An12','Quan Zhi Gao','The King's Avatar','Quan Zhi Gao Shou','G.CMay Animation & Film','Saturday','SW','System.Byte[]','Anime','04-08-2017','2','0','12','100','Action, Game, Adventure, Comedy','True')

    When I add a random entry (and it accepts it), the SQL Command looks like this - INSERT INTO AniChart VALUES('NKepNJNENS0An0','temp','','','','TBA','TBA','System.Byte[]','Anime','  -  -','0','0','0','0','','False')

    When i Update a test entry (and it accepts it), the SQL Command look like this - INSERT INTO AniChart VALUES('NK et-KonNNJaaeeNmNEnls aeSt0An4','A Test - Known Name','English Name','Japanese Name','Studio','Queued','Queued','System.Byte[]','Anime','01-02-2017','4','0','4','0','Tag, Test','False')

    All of the commands are basely the same, just different data filled in the quotes, What am I doing wrong?



    Wednesday, May 10, 2017 8:39 PM

All replies

  • Hi Flashkid10,

    Thank you for posting here.

    According to your question is more related to ADO, I will move it to ADO.NET Managed Providers forum for suitable support.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    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, May 11, 2017 3:10 AM
  • Hi Flashkid10,

    Are AddCommands a List collection, if so, please modify your code like below, which could reduce database operation.

    var sqlString = String.Join("; ", AddCommands.ToArray());
    
    if (AniListCon.State != ConnectionState.Open) AniListCon.Open();
    
    OdbcCommand tem = new OdbcCommand(sqlString, AniListCon);
    tem.ExecuteReader();

    Best regards,

    Cole Wu


    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, May 11, 2017 7:12 AM
    Moderator
  • Blocked? What are the exceptions (errors)?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 11, 2017 12:35 PM
  • An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll

    Additional information: ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''NKkb' rpNJkb' rpTeNEkb' rpTeGo3An13','Akiba's Trip''.

    "INSERT INTO AniChart VALUES('NKkb' rpNJkb' rpTeNEkb' rpTeGo3An13','Akiba's Trip','Akiba's Trip The Animation','Akiba's Trip The Animation','Gonzo','Dropped','','System.Byte[]','Anime','False','12','0','13','50','Action, Adventure, Ecchi, Supernatural','01-04-2017')"

    Thursday, May 11, 2017 2:55 PM
  • I just figured out the error, the Insert command uses single quote to separate the data, and some of the data has single quotes in them...

    To fix this, i just  replace the out going sing quotes with @#$% and the  in comeing @#$% with single quotes

    Thursday, May 11, 2017 2:58 PM
  • I just figured out the error, the Insert command uses single quote to separate the data, and some of the data has single quotes in them...

    To fix this, i just  replace the out going sing quotes with @#$% and the  in comeing @#$% with single quotes

    Typically you would be using Command Parameters to avoid these types of syntax errors:

    https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparametercollection.addwithvalue(v=vs.110).aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 11, 2017 3:45 PM