none
OleDbDataAdapter.Update() throwing OleDbException RRS feed

  • Question

  • I'm receiving an OleDbException of "Syntax error in INSERT INTO statement."  I'm assuming this is because only the adapter's select command is set.  I'm also using a OleDbCommandBuilder.  It was my understanding that when using the two together that the builder would supply the adapter with all commands provided the select command was set.  I have a datagridview I wish to use to always display a table in a database.  I would also like to be able to add and delete rows from the gridview and have the values reflect to the database.  Here is the code portions relating to the database handling.

    Code Snippet

    // Global Variables

    DataTable dTable;
    OleDbDataAdapter dAdapter;
    OleDbCommandBuilder cBuilder;


    // Forms Load Event

    //create the connection string
    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    System.IO.Path.GetDirectoryName(Application.ExecutablePath) + "\\Reminder.mdb";
    //create the database query
    string query = "SELECT * FROM ReminderTable";
    //create an OleDbDataAdapter to execute the query
    dAdapter = new OleDbDataAdapter(query, connString);
    //create a command builder
    cBuilder = new OleDbCommandBuilder(dAdapter);
    //create a DataTable to hold the query results
    dTable = new DataTable();//fill the DataTable
    dAdapter.Fill(dTable);
    //BindingSource to sync DataTable and DataGridView
    BindingSource bSource = new BindingSource();
    //set the BindingSource DataSource
    bSource.DataSource = dTable;
    //set the DataGridView DataSource
    data.DataSource = bSource;


    // Method For Adding Data to the GridView

    // Make sure the selected values form valid input.
    // If the values are valid
    //   Open connection to the database.
    //   Write the values to the datebase.
    //   Close the connection to the database.

    // Validate input
    DateTime selectedTime = GetSelectedDateTime();
               
    string day = selectedTime.Year % 100 < 10 ? "0" + (selectedTime.Year % 100).ToString() : (selectedTime.Year % 100).ToString();
    day += "/" + (selectedTime.Month < 10 ? "0" + selectedTime.Month.ToString() : selectedTime.Month.ToString());
    day += "/" + (selectedTime.Day < 10 ? "0" + selectedTime.Day.ToString() : selectedTime.Day.ToString());
               
    string time = selectedTime.Hour < 10 ? "0" + selectedTime.Hour.ToString() : selectedTime.Hour > 12?selectedTime.Hour-12<10?"0" + (selectedTime.Hour-12).ToString():(selectedTime.Hour-12).ToString():selectedTime.Hour.ToString();
    time += ":" + (selectedTime.Minute < 10 ? "0" + selectedTime.Minute.ToString() : selectedTime.Minute.ToString());
    time += ":" + (selectedTime.Second < 10 ? "0" + selectedTime.Second.ToString() : selectedTime.Second.ToString());
    time += " " + (selectedTime.Hour > 11 ? "PM" : "AM");
               
    string msg = message.Text;

     //
     // Mon = M, Tues = T, Wed = W, Thurs = R, Fri = F, Sat = S, Sun = U
     // Once = O, Everyday = E, EveryOtherDay = EO, EveryXDay = E{D},
     // EveryOtherXDay = EO{D}  where {D} is the day.  For example,
     // ET is every Tuesday while EOT is every other Tuesday.
     string freq = "";
     List<string> freqParams = new List<string>();
     foreach (Control c in frequency.Controls)
     {
        if (c is RadioButton)
        {
            RadioButton r = (RadioButton)c;
            if (r.Checked)
            {
                freq = r.Tag.ToString();
                if (freq.EndsWith("X"))
                {
                    freq = freq.Remove(freq.Length - 1);
                    foreach (Control c2 in frequency.Controls)
                    {
                        if (c2 is CheckBox)
                        {
                            CheckBox b = (CheckBox)c2;
                            if (b.Checked)
                                freqParams.Add(b.Tag.ToString());
                        }
                    }
                }
                break;
            }
        }
    }

    // Remember to insert the message for every day selected if need be
    do
    {
        string trueFreq = freq;
        if (freqParams.Count != 0)
        {
            trueFreq += freqParams[0];
            freqParams.RemoveAt(0);
        }
        dTable.Rows.Add(new object[] { day,time,msg,trueFreq });
    } while (freqParams.Count > 0);


    // DataGridView's Row Removal Event
    dTable.Rows.RemoveAt(e.RowIndex);

    // DataGridView's Validation
    cBuilder.RefreshSchema();
    dAdapter.Update(dTable);


    Thursday, February 7, 2008 10:49 PM

All replies