Hey all I currently am having trouble utilizing the oledbcommand method named parameter because I do not know how to use it properly :(

Here is my class:

namespace CalendarApplicationLibrary
{
    public class MeetingEntryDA
    {
        private const string CONNECTION_STRING =
            @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Entry\entry.accdb;Persist Security Info=False;";

        public void WriteDataToFile(MeetingEntry currentEntry)
        {
            OleDbConnection dbConnection = new OleDbConnection(CONNECTION_STRING);
            
            string commandString = 
            "INSERT INTO MeetingEntries (Subject, Location, Start Date, End Date, Enable Alarm, Repeat Alarm, Reminder, Repetition Type)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            OleDbCommand commandStatement = new OleDbCommand(commandString, dbConnection);
            
            commandStatement.Parameters.Add("@Subject", OleDbType.VarWChar, 30).Value = currentEntry.Subject;
            commandStatement.Parameters.Add("@Location", OleDbType.VarWChar, 50).Value = currentEntry.Location;
            commandStatement.Parameters.Add("@Start Date", OleDbType.Date, 40).Value = currentEntry.StartDateTime.Date;
            commandStatement.Parameters.Add("@End Date", OleDbType.Date, 40).Value = currentEntry.EndDateTime.Date;
            commandStatement.Parameters.Add("@Enable Alarm", OleDbType.Boolean, 1).Value = currentEntry.IsAlarmEnabled;
            commandStatement.Parameters.Add("@Repeat Alarm", OleDbType.Boolean, 1).Value = currentEntry.IsAlarmRepeated;
            commandStatement.Parameters.Add("@Reminder", OleDbType.Integer, 2).Value = currentEntry.Reminder;
            commandStatement.Parameters.Add("@Repetition Type", OleDbType.VarWChar, 10).Value = currentEntry.Repetition;
            
            dbConnection.Open();
            commandStatement.ExecuteNonQuery();
        }
    }
}

What I am having trouble is that I am always getting a insert into error when the time comes that I have to run the insert statement. I am most certain that the culprit for this is those pesky datetimepicker values. I guess I am casting incorrectly that's why it won't insert properly in my database.

My properties for each column in my ms access database are as follows:

Subject - type text
Location - type text
Start Date - type Date/Time, short date format
End Date - type Date/Time, short date format
Enable Alarm - type Yes/No, Format true/false
Repeat Alarm - type Yes/No, Format true/false
Reminder - type number
Repetition Type - type text

Please show me how to properly format my values so that I may correctly cast them into my insert statement. Thank you!

EDIT:
Oh and my repetition type thingy there is of type enum, will I have to convert it to string when I place it on my statement? I am assuming that it is automatically cast as a string though. That maybe another point where I am incorrectly casting. MY IsEnabledAlarm and IsAlarmRepeated are from checkbox controls so I am certain that they return boolean values. I am just not so sure with my repetiton type, start date and end date values.

Edited 6 Years Ago by ticktock: n/a

Can you please show us the exception?

or try currentEntry.EndDateTime.Date.ToShortDateString();

Edited 6 Years Ago by Ramy Mahrous: n/a

I have already tried that snippet previously and it yielded to incorrect results, how should I show the exception? will a screen shot do?

Oh wait here you go:

System.Data.OleDb.OleDbException was unhandled
  Message="Syntax error in INSERT INTO statement."
  Source="Microsoft Office Access Database Engine"
  ErrorCode=-2147217900
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at CalendarApplicationLibrary.MeetingEntryDA.WriteDataToFile(MeetingEntry currentEntry) in G:\Unsorted Mess\File Dumpster\temp\Activity 3\CalendarApplication\ClassLibrary1\MeetingEntryDA.cs:line 33
       at CalendarApplication.NewMeetingEntryForm.btnSave_Click(Object sender, EventArgs e) in G:\Unsorted Mess\File Dumpster\temp\Activity 3\CalendarApplication\CalendarApplication\NewMeetingEntryForm.cs:line 65
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.RunDialog(Form form)
       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       at System.Windows.Forms.Form.ShowDialog()
       at CalendarApplication.MainForm.btnAddEntry_Click(Object sender, EventArgs e) in G:\Unsorted Mess\File Dumpster\temp\Activity 3\CalendarApplication\CalendarApplication\DisplayDataForm.cs:line 22
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at CalendarApplication.Program.Main() in G:\Unsorted Mess\File Dumpster\temp\Activity 3\CalendarApplication\CalendarApplication\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

Edited 6 Years Ago by ticktock: n/a

Please before commandStatement.ExecuteNonQuery(); get the commandStatement.CommandText and execute the query into access. see if it runs or not.

Edited 3 Years Ago by mike_2000_17: Fixed formatting

Where do I specifically store or use this? It is no method is it not? I tried calling it but it just says it is a property

EDIT: here is what I did:

.....
dbConnection.Open();
            commandStatement.CommandText = commandString;
            commandStatement.ExecuteNonQuery();

still the same exception handler

Edited 6 Years Ago by ticktock: n/a

Man! please read what I posted please commandStatement.CommandText in debug mode will show you the command gonna be executed! take it copy and paste in access and run it.

Man! please read what I posted please commandStatement.CommandText in debug mode will show you the command gonna be executed! take it copy and paste in access and run it.

I'm very sorry. I misunderstood what you said. Anyways, I tried running in debug mode and had my commandtext to yield only to this:

INSERT INTO MeetingEntries (Subject, Location, Start Date, End Date, Enable Alarm, Repeat Alarm, Reminder, Repetition Type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)

Please tell me if I still got the wrong info or what.

EDIT:
I am guessing that my commandtext is not being updated and the text above is still being used as my insert statement that's why I am getting the error. But I wonder how come this happens. I have run it again on debug mode and still the same results.

Edited 6 Years Ago by ticktock: n/a

It means the query run on Access database "INSERT INTO MeetingEntries (Subject, Location, Start Date, End Date, Enable Alarm, Repeat Alarm, Reminder, Repetition Type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" which is wrong!

As I thought so, maybe this is because I am using the parameter method in a wrong way? How do I go about this?

I tried hardcoding the statements but it still garnered errors:

Here is what I input:

INSERT INTO MeetingEntries (Subject, Location, Start Date, End Date, Enable Alarm, Repeat Alarm, Reminder, Repetition Type) VALUES ('Subject', 'Location', #3/21/2010#, #3/30/2010#, True, True, 5, 'Weekly')

I am getting a little hopeless with these oledbstatements now.

Maybe it is something about my database? or maybe both of my insert statement and database are at fault.

Input shall be greatly appreciated.

Edited 6 Years Ago by ticktock: n/a

Fixed the f'n thing finally! haha.

I was missing brackets on my column names, I am guessing that one of my column names is a reserved word for ms access that's why I am getting insert into errors. Man, microsoft ought to be more specific with them insert into errors. Them errors mean a lot of different things and it is hell as hard to find out what you got wrong.

Here is what I did:

string commandString =
            "INSERT INTO MeetingEntries" + 
            "([Subject], [Location], [Start Date], [End Date], [Enable Alarm], [Repeat Alarm], [Reminder], [Repetition Type])" + 
            "VALUES (?,?,?,?,?,?,?,?)";

Edited 6 Years Ago by ticktock: n/a

Because of spaces man...

public void WriteDataToFile(MeetingEntry currentEntry)
        {
            OleDbConnection dbConnection = new OleDbConnection(CONNECTION_STRING);
            
            string commandString =
            "INSERT INTO MeetingEntries (Subject, Location, [Start Date], [End Date], [Enable Alarm], [Repeat Alarm], Reminder, [Repetition Type])" + " VALUES (@Subject, @Location, @StartDate, @EndDate, @EnableAlarm, @RepeatAlarm, @Reminder, @RepetitionType)";
            //"INSERT INTO MEETINGENTRIES (SUBJECT, LOCATION, START DATE, END DATE, ENABLE ALARM, REPEAT ALARM, REMINDER, REPETITION TYPE)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            OleDbCommand commandStatement = new OleDbCommand(commandString, dbConnection);
           

            commandStatement.Parameters.Add("Subject", OleDbType.VarWChar, 30).Value = currentEntry.Subject;
            commandStatement.Parameters.Add("Location", OleDbType.VarWChar, 50).Value = currentEntry.Location;
            commandStatement.Parameters.Add("StartDate", OleDbType.Date, 40).Value = currentEntry.StartDateTime.Date;
            commandStatement.Parameters.Add("EndDate", OleDbType.Date, 40).Value = currentEntry.EndDateTime.Date;
            commandStatement.Parameters.Add("EnableAlarm", OleDbType.Boolean, 1).Value = currentEntry.IsAlarmEnabled;
            commandStatement.Parameters.Add("RepeatAlarm", OleDbType.Boolean, 1).Value = currentEntry.IsAlarmRepeated;
            commandStatement.Parameters.Add("Reminder", OleDbType.Integer, 2).Value = currentEntry.Reminder;
            commandStatement.Parameters.Add("RepetitionType", OleDbType.VarWChar, 10).Value = currentEntry.Repetition.ToString();
            
            
            dbConnection.Open();
            commandStatement.CommandText = commandString;
            commandStatement.ExecuteNonQuery();
        }

Because of spaces man...

public void WriteDataToFile(MeetingEntry currentEntry)
        {
            OleDbConnection dbConnection = new OleDbConnection(CONNECTION_STRING);
            
            string commandString =
            "INSERT INTO MeetingEntries (Subject, Location, [Start Date], [End Date], [Enable Alarm], [Repeat Alarm], Reminder, [Repetition Type])" + " VALUES (@Subject, @Location, @StartDate, @EndDate, @EnableAlarm, @RepeatAlarm, @Reminder, @RepetitionType)";
            //"INSERT INTO MEETINGENTRIES (SUBJECT, LOCATION, START DATE, END DATE, ENABLE ALARM, REPEAT ALARM, REMINDER, REPETITION TYPE)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            OleDbCommand commandStatement = new OleDbCommand(commandString, dbConnection);
           

            commandStatement.Parameters.Add("Subject", OleDbType.VarWChar, 30).Value = currentEntry.Subject;
            commandStatement.Parameters.Add("Location", OleDbType.VarWChar, 50).Value = currentEntry.Location;
            commandStatement.Parameters.Add("StartDate", OleDbType.Date, 40).Value = currentEntry.StartDateTime.Date;
            commandStatement.Parameters.Add("EndDate", OleDbType.Date, 40).Value = currentEntry.EndDateTime.Date;
            commandStatement.Parameters.Add("EnableAlarm", OleDbType.Boolean, 1).Value = currentEntry.IsAlarmEnabled;
            commandStatement.Parameters.Add("RepeatAlarm", OleDbType.Boolean, 1).Value = currentEntry.IsAlarmRepeated;
            commandStatement.Parameters.Add("Reminder", OleDbType.Integer, 2).Value = currentEntry.Reminder;
            commandStatement.Parameters.Add("RepetitionType", OleDbType.VarWChar, 10).Value = currentEntry.Repetition.ToString();
            
            
            dbConnection.Open();
            commandStatement.CommandText = commandString;
            commandStatement.ExecuteNonQuery();
        }

I see so it was not about having reserved words but of spaces. Thank you very much for pointing that out. I am repeating myself when I say this but seriously microsoft should consider being more specific with their insert into statement errors haha.

Marking this as solved (just waited for your input). Thank you!

This question has already been answered. Start a new discussion instead.