System.Data.SQLite

So I just started working for this company, and my boss the optimist said, BTW, I need you to create an application that does XYZ by January 1st.  That would be fine and all except my toolkit is totally new (I am a web LAMP/PHP guy) and maybe I have bit off more than I can chew.  I know anyone who could actually do what I am about to ask is probably a pretty busy person, so I don't want to be rediculous, but your help would be appreciated.  What I feel like I am lacking when I am trying to learn this stuff is a simple and practical real world best practices example application.  It would be really great if it worked with about 3 tables that have relationships, but even just one table would be very helpful.  I have boiled down my objective into this very simple application. I dont want to do the data entry in the grid, but instead, use a detail form for that.

So far I have gotten the basics correct (ie, grid editing works, my dataset works), but I have no idea what I am doing wrong.  Thanks in advance for any gracious soul who finds it in their heart to help out.

CREATE TABLE [TaxYear] (
    [TaxYearID] integer PRIMARY KEY NOT NULL,
    [CreateDateTime] datetime,
    [UpdatedDateTime] datetime,
    [OrganizationID] int,
    [TaxYear] varchar(4),
    [OrganizationName] varchar(128),
    [ContactNameFirst] varchar(50),
    [ContactNameLast] varchar(50),
    [ContactEmail] varchar(50),
    [ContactPhone] varchar(50),
    [ContactFax] varchar(50),
    [AddressStreet1] varchar(50),
    [AddressStreet2] varchar(50),
    [AddressCity] varchar(50),
    [AddressState] varchar(50),
    [AddressPostalCode] varchar(50),
    [Complete] integer DEFAULT 0,
    CONSTRAINT [FK_TaxYear_0] FOREIGN KEY ([OrganizationID]) REFERENCES [Organization] ([OrganizationID])
);

Relevent Grid Form code:

        // Code for add button (the details for OK button is set to DialogResult.OK and is the accept button on the form

        private void addButton_Click(object sender, EventArgs e)
        {
            object item = this.organizationDataSet.TaxYear.DefaultView.AddNew();
            DetailsForm dlg = new DetailsForm(item);
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.Validate();
                this.taxYearBindingSource.EndEdit();
                this.tableAdapterManager.UpdateAll(this.organizationDataSet);
                this.taxYearBindingSource.ResetCurrentItem();
            }
        }
 

Details form init method:

        public DetailsForm(object item)
        {
            DataRowView dr = (DataRowView)item;
            if ((item is DataRowView)
                && (((DataRowView)item).Row is OrganizationDataSet.TaxYearRow))
            {
                InitializeComponent();

                // Acquire employee list data item
                this.taxYearBindingSource.DataSource = dr;
            }
            else throw new ArgumentException("Incorrect type");
        }
 

One thing that I am also struggling with is the autoincrement feature. It seems like Visual Studio 2008 is handling the auto increment for me and not the database.  Though this seems to work in some instances, sometimes I get exceptions for the primary key being NULL.  The strongly typed dataset probably is the culprit there.

To take care of the autoincrement problem, change your primary key to a INTEGER PRIMARY KEY AUTOINCREMENT.

See http://www.sqlite.org/autoinc.html for more information.

Thanks for your response.  I read that by setting the primary key to integer primary key that it would auto-increment, but for the dataset stuff, it needs to the explicit version which I thought existed but did not understand it was necessary.

 After much hair pulling and monkeying around I finally figured out what was wrong with my List/Details application. Not sure if this is a bug or just a bad idea, but if you set a column name to the same name as the table name, you wont get an error but some things just wont work.  The most misleading thing about this is that binding does work in certain circumstances for example the grid, but it does not work when binding to a details view.  Not sure if this only impacts certain controls, but it sure impacted my stress level.  It would be nice if there was some sort of warning when you name a column the same as the table name.  Maybe that is bad form and I should have known better, and though I did not like naming it the same name, it made the most sense for what I was doing.  Anyway, I hope that this helps someone other than myself.