none
Data Adapter query terminating early RRS feed

  • Question

  • I am attempting to implement a button to delete a record (selected from a listbox) from a database. The command works on some tables in the database, but fails on one with a greater number of columns, giving me this outcome:

    Syntax error (missing operator) in query expression '((Record = ?) AND ((? = 1 AND Name IS NULL) OR (Name = ?)) AND ((? = 1 AND Gender IS NULL) OR (Gender = ?)) AND ((? = 1 AND Origin IS NULL) OR (Origin = ?)) AND ((? = 1 AND DOB IS NULL) OR (DOB = ?)) AND ((? = 1 AND Ear Type IS NULL) OR (Ear Type = ?)) AN'

    Presumably, the query is reaching 255 characters in length and running out of space, causing it to terminate there. Is there any way I can increase the permissible length of the query? Having already added the data to the listbox, I am running the following code to perform the deletion:

     

    conn = new
     System.Data.OleDb.OleDbConnection();
    conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=PetDatabase.mdb"
    ;
    string
     dbcommand = "Select * from Rats;"
    ;
    
    RatDataAdapter = new
     OleDbDataAdapter(dbcommand, conn);
    
    System.Data.OleDb.OleDbCommandBuilder cb;
    cb = new
     System.Data.OleDb.OleDbCommandBuilder(RatDataAdapter);
    
    int
     record2 = profileListBox.SelectedIndex;
       
    RatDataSet.Tables["Records"
    ].Rows[record2].Delete();
    
    RatDataAdapter.Update(RatDataSet, "Records"
    );
    

     

    Many thanks in advance for any answers!

    • Edited by NekoBazu Monday, December 20, 2010 12:38 AM Tidying formatting
    • Moved by Aspen VJ Tuesday, December 21, 2010 5:46 AM (From:Windows Forms General)
    Monday, December 20, 2010 12:35 AM

Answers

All replies

  • Hi NekoBazu,

    ·         Base on your description,  I think it is little about winform.

    This forum is dedicated to Windows Forms technology. I'm moving this thread from Base "Windows Forms" to "ADO.NET date" forum, since this issue is related to ADO.NET date. There are more ADO.NET date experts in that forum.

    Thank you for understanding and supporting.

     

    Best Regards,

    Vin Jin


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, December 21, 2010 5:45 AM
  • Hi NekoBazu,

    Welcome to MSDN forums.

    According to your description, I don't think it is the permissible length problem. I don't see you open your connection and close your connection, Please refer here and try the link's code: http://support.microsoft.com/kb/308507

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 27, 2010 2:35 AM
    Moderator
  • @Neko ... does Access support Stored Procedures? If so, I'd recommend using Stored Procs for this table and then use the CommandBuilder.DeriveParameters() method.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, December 29, 2010 4:43 PM