Problems updating my OLEDB Access Database

[Troy]

Hey all I'm having a slight issue I hope someone can help me with.

This code allows me to update my database just fine, with one exception.

If I try to delete a field and make it blank I get an error stating that it cannot be a zero length field.

How can I delete a field and update my database back to a null field.

here is my code for updating.

Try
                '-- Start the editing in the datarow.
                rowContact.BeginEdit()

                '-- Run through the text boxes on the form, and
                '-- if they match up with a field from the record,
                '-- place the value back in the record.
                If txtLastName1.Text <> "" Then rowContact("LastName1") = txtLastName1.Text
                If txtFirstName1.Text <> "" Then rowContact("FirstName1") = txtFirstName1.Text
                If txtMiddleInitial1.Text <> "" Then rowContact("MiddleInitial1") = txtMiddleInitial1.Text
                If txtLastName2.Text <> "" Then rowContact("LastName2") = txtLastName2.Text
                If txtFirstName2.Text <> "" Then rowContact("FirstName2") = txtFirstName2.Text
                If txtMiddleInitial2.Text <> "" Then rowContact("MiddleInitial2") = txtMiddleInitial2.Text
                If txtCurrentStreet.Text <> "" Then rowContact("CurrentStreet") = txtCurrentStreet.Text
                If txtCurrentCity.Text <> "" Then rowContact("CurrentCity") = txtCurrentCity.Text
                If txtCurrentState.Text <> "" Then rowContact("CurrentState") = txtCurrentState.Text
                If txtCurrentZipCode.Text <> "" Then rowContact("CurrentZipCode") = txtCurrentZipCode.Text
                If txtCurrentCounty.Text <> "" Then rowContact("CurrentCounty") = txtCurrentCounty.Text
                If txtCurrentCounty.Text <> "" Then rowContact("FutureStreet") = txtFutureStreet.Text
                If txtFutureCity.Text <> "" Then rowContact("FutureCity") = txtFutureCity.Text
                If txtFutureState.Text <> "" Then rowContact("FutureState") = txtFutureState.Text
                If txtFutureZipCode.Text <> "" Then rowContact("FutureZipCode") = txtFutureZipCode.Text
                If txtFutureCounty.Text <> "" Then rowContact("FutureCounty") = txtFutureCounty.Text
                If txtHomePhoneAreaCode.Text <> "" Then rowContact("HomePhoneAreaCode") = txtHomePhoneAreaCode.Text
                If txtHomePhonePrefix.Text <> "" Then rowContact("HomePhonePrefix") = txtHomePhonePrefix.Text
                If txtHomePhoneSuffix.Text <> "" Then rowContact("HomePhoneSuffix") = txtHomePhoneSuffix.Text
                If txtFaxAreaCode.Text <> "" Then rowContact("FaxAreaCode") = txtFaxAreaCode.Text
                If txtFaxPrefix.Text <> "" Then rowContact("FaxPrefix") = txtFaxPrefix.Text
                If txtFaxSuffix.Text <> "" Then rowContact("FaxSuffix") = txtFaxSuffix.Text
                If txtCellPhoneAreaCode.Text <> "" Then rowContact("CellPhoneAreaCode") = txtCellPhoneAreaCode.Text
                If txtCellPhonePrefix.Text <> "" Then rowContact("CellPhonePrefix") = txtCellPhonePrefix.Text
                If txtCellPhoneSuffix.Text <> "" Then rowContact("CellPhoneSuffix") = txtCellPhoneSuffix.Text
                If txtOfficePhoneAreaCode.Text <> "" Then rowContact("OfficePhoneAreaCode") = txtOfficePhoneAreaCode.Text
                If txtOfficePhonePrefix.Text <> "" Then rowContact("OfficePhonePrefix") = txtOfficePhonePrefix.Text
                If txtOfficePhoneSuffix.Text <> "" Then rowContact("OfficePhoneSuffix") = txtOfficePhoneSuffix.Text
                If txtExtension.Text <> "" Then rowContact("Extension") = txtExtension.Text
                If txtEMail.Text <> "" Then rowContact("Email") = txtEMail.Text

                '-- Finish the editing of the datarow
                rowContact.EndEdit()

                If dsContact.HasChanges(DataRowState.Modified) Then

                    Dim dsChangedContact As DataSet
                    dsChangedContact = dsContact.GetChanges(DataRowState.Modified)

                    '-- Create an instance of the command builder
                    Dim ocbContact As OleDb.OleDbCommandBuilder
                    ocbContact = New OleDb.OleDbCommandBuilder(daContact)

                    '-- Have the command builder create an Update SQL command
                    daContact.UpdateCommand = ocbContact.GetUpdateCommand
                    '-- Perform the specified SQL command; then close the connection
                    daContact.Update(dsContact, "Contact")
                    dsContact.Tables("Contact").AcceptChanges()

                    '-- Close the connection
                    daContact.UpdateCommand.Connection.Close()

                End If

            Catch eInsertException As Exception
                'Error handling goes here.
                UnhandledExceptionHandler()
                Exit Sub
            Finally

                MsgBox("File for [" & txtLastName1.Text & ", " & txtFirstName1.Text & " " & txtMiddleInitial1.Text & "] Updated!")
                Me.Close()
                'Clear Data Table
                dsContact.Clear()
                dtContact.Clear()

                'RaiseEvent OpenFileDialog1(m_FileNames(i).ToString)
                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " _
                & dbPath & "; Persist Security Info=False"
                cn.ConnectionString = strConnection

                'Open the Database
                cn.Open()

                ' Fill the Contacts DataTable 
                daContact.Fill(dsContact, "Contact")
                dtContact = dsContact.Tables.Item("Contact")

                'Close the Database
                cn.Close()

                'Clear TreeView Control
                frmMain.tvTreeview.Nodes.Clear()
                frmMain.configureTreeview()
                frmMain.tvTreeview.Enabled = True

            End Try

 

[cjard]

Are you working with .NET 1.1?

 

[Troy]

Are you working with .NET 1.1?

Actually I'm working with .Net 2.0 and VB 2005.

I've checked out your links at the bottom and I just can't understand them. I've tried constructing some Parameters and even reconstructing my SQL Statment to allow for them but I keep getting errors.

If I could see a working example It might help alot.

Right now I can Update my Fileds just fine using the Code I've listed but I cannot Delete all the information and revert it back to an empty null state.

 

[cjard]

Actually I'm working with .Net 2.0 and VB 2005.

I've checked out your links at the bottom and I just can't understand them.

Even the "Creating a Simple Data App" ?

It's just about the most hand-holdy explain-everything tutorial I've ever seen, with links off to everything that might even be remotely complex. The only thing I could do to make it easier is to come round your house and do it for you..

Now, if you live in some sun-kissed place where the beaches are clean and the girls are di.. er.. i mean.. tanned and smiling all the time, let me know..

If I could see a working example It might help alot.

Compact, Zip and Post your db as an attachment, and I'll knock up a sample project for you

 

[Troy]

Even the "Creating a Simple Data App" ?

It's just about the most hand-holdy explain-everything tutorial I've ever seen, with links off to everything that might even be remotely complex. The only thing I could do to make it easier is to come round your house and do it for you..

Now, if you live in some sun-kissed place where the beaches are clean and the girls are di.. er.. i mean.. tanned and smiling all the time, let me know..




Compact, Zip and Post your db as an attachment, and I'll knock up a sample project for you

ok Here it is with a bit of sample data in it. It's a standard Access Database with 3 tables.

 

[cjard]

This took me about 3 minutes to write, about 20 mouse clicks.. the longest part was figuring out how VB.NET does its event handler attachment (and compared to C#, its very dumb and tedious indeed)

The crucial part, is found when you right click the dataset designer and view the code

That one line will ensure that any value on any row of contact, when being edited, if it is an empty string, will result in a null being added in place. This, when sent to the db, is fine.. Allow Zero Length string = False is set for some or all of your columns.. so "" cannot be stored, but null can.

 

[Troy]

This took me about 3 minutes to write, about 20 mouse clicks.. the longest part was figuring out how VB.NET does its event handler attachment (and compared to C#, its very dumb and tedious indeed)

The crucial part, is found when you right click the dataset designer and view the code

That one line will ensure that any value on any row of contact, when being edited, if it is an empty string, will result in a null being added in place. This, when sent to the db, is fine.. Allow Zero Length string = False is set for some or all of your columns.. so "" cannot be stored, but null can.

I'd like to thankyou for taking the time to write this and help me understand it all. With that said, unfortunately it's still greek to me as you're using ADO and binding controls. I use OLEDB and create my statements manually using code. This allows me to give my user Database freedom.

What I mean by that is they are able to create a database and backup or save it to a certain location .... Say they only want to record information for a particular year.

Then they can open and load only that database. I have my program fully functional in that reguard, but my edit routine isn't allowing me to save Null values or empty fields to my Database when I update.

Thats why I took interest in your parameter link. As I understand it if I could make it work I could then specify not only the type of information being saved (ex. update.Parameters.Add("?LastName1", OleDbType.VarChar).Value = txtLastName1.Text), but also it would allow empty null fields as well.

Please correct me if I'm wrong.

I was hoping to see an example using OLEDB with constructing the SQL INSERT, UPDATE, DELETE Strings, as well as how I should implement the save routine allowing my Textbox information to be placed in the database.

I'll show some of my code I used to setup my Update routine.

Dim cn As New System.Data.OleDb.OleDbConnection()
Dim update As New OleDbCommand("UPDATE Contact LastName1 = ?LastName1, FirstName1 = ?FirstName1 , MiddleInitial1 = ?MiddleInitial1", cn)
Dim dsContact As New System.Data.DataSet()
Dim dtContact As System.Data.DataTable
Dim rowContact As System.Data.DataRow

If response = MsgBoxResult.Yes Then     ' User chose Yes.

            Try
                '-- Start the editing in the datarow.
                rowContact.BeginEdit()
                'daContact.UpdateCommand = update
                '-- Run through the text boxes on the form, and
                '-- if they match up with a field from the record,
                '-- place the value back in the record.
                update.Parameters.Add("?LastName1", OleDbType.VarChar).Value = txtLastName1.Text
                update.Parameters.Add("?FirstName1", OleDbType.VarChar).Value = txtFirstName1.Text
                update.Parameters.Add("?MiddleInitial1", OleDbType.VarChar).Value = txtMiddleInitial1.Text


                '-- Finish the editing of the datarow
                rowContact.EndEdit()
                cn.Open()

                update.ExecuteNonQuery()

                cn.Close()

The problem here is I'm getting an sql error in UPDATE. I guess I'm just not grasping how to write an update routine using the Parameters.

By the way, I allow them to select the database by opening a Dialog window and when they find it I create this string.

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & .FileName & "; Persist Security Info=False"
cn.ConnectionString = strConnection

 

[cjard]

I'd like to thankyou for taking the time to write this and help me understand it all. With that said, unfortunately it's still greek to me as you're using ADO and binding controls. I use OLEDB and create my statements manually using code. This allows me to give my user Database freedom.

Complexity might bring ultimate fredom, but it also brings ultimate hard work. Additionally, nothing you do in your code is any different to what I do in mine. If your code really did something amazingly different that binding couldnt be used for, then I would not have used binding.. However, it doesnt. Youre basically making your life a whole lot harder 100% needlessly. Additionally, youre limiting yourself to a very quirky, and not-very-good-OO way of programming by using generic classes rather than typed ones. All this said, the concept I'm covering here is not beyond your grasp; you can still add an event handler to the datarow youre using, that will set the value to null when an empty string is passed in.

What I mean by that is they are able to create a database and backup or save it to a certain location .... Say they only want to record information for a particular year.

Unlike men, data access is not a case of "if you want half a pound of sausage, you have to buy the whole pig"
You can use databinding throughout your entire app, and have just one procedure that makes an exclusive connection to the db manually, and backs it up..

Then they can open and load only that database. I have my program fully functional in that reguard, but my edit routine isn't allowing me to save Null values or empty fields to my Database when I update.

It will allow you to save null values.. My program does, and it uses your database without modification; take a binary cmparison - i dont have Access so i couldnt edit your databse. Databinding is not something mystic that is correcting this problem; youre merely trying to send a zero length string into a field that has been instructed not to allow them. Youd get the same problem if you ran the following SQL in some query tool:

INSERT INTO Contact(LastName1) VALUES ("")

Thats why I took interest in your parameter link. As I understand it if I could make it work I could then specify not only the type of information being saved (ex. update.Parameters.Add("?LastName1", OleDbType.VarChar).Value = txtLastName1.Text), but also it would allow empty null fields as well.

Hmm, yes.. but parameters are always good.. you cant Name them in access though! That link isnt a "howto use parameters in various DBs" its a "why you should always use parameters"
The IDE creates good, working, parameterized code for you in SECONDS. Take a look in the MHDataSet.Designer.vb if you dont believe.. Its all the same code youre trying to write, with the exception that:
It's comprehensive
It's well encapsulated
It's well thought out
It was created in seconds, with mouse clicks
It works

I was hoping to see an example using OLEDB with constructing the SQL INSERT, UPDATE, DELETE Strings,

I know, I know.. You have a strange fascination with loving to see button handler code jammed full of connection strings, SQL text, parameters additions and massive blocks of code that does nothing more than shunt text out of textboxes, into parameters.. Sorry my code disappointed in this regard, but we dont do that ourselves except in rare circumstances. Littering our form code with that is BAD OO DESIGN. If you want to see that being done, take a look in the InitCommand function in MHDS.Designer.vb

as well as how I should implement the save routine allowing my Textbox information to be placed in the database.

Yep, that's in there.. SaveToolStripButton_Click or something. Those 3 lines of code?
Validate, EndEdit, Update

That's it!

I think you must be sitting there biting your nails that this is some kind of Voodoo. It isnt! It's all just boring code like you or I would write, but its been written by the IDE and flagged the the [DebuggerNonUserCode] attribute so that you CANT step into it with F8, and hence you CANT fiddle with it and break it (because it works; it doesnt need fiddling with. any fiddles will be lost next time the code is generated by the IDE)

If youre interested, Go to:
Tools>> Options>> Show All Settings (tick)>> Debugging (expand)>> General>> Just My Code (untick)

Now the debugger will ignore the DebuggerNonUserCodeFlags and show you all that the Update() comamnd does. Single step it and watch! Maybe this will change your mind about this DataBinding black magic - it sure turned pachjo and dazlerd around, maybe others..

I'll show some of my code I used to setup my Update routine.

Bear in mind that I dont normally look at blocks of code like that (you know.. medieval stuff) any more

The problem here is I'm getting an sql error in UPDATE. I guess I'm just not grasping how to write an update routine using the Parameters.

Open MHDataSet.Designer.vb and see the one the IDE wrote for me. You will realise two things:

1) You didnt form your UPDATE sql correctly
2) The IDE is perfectly capable of writing a good, working, correctly parameterized, well encapsulated statement, and initialising it properly so that it is only set up once, cached and become available for high performance pooled use. In seconds. With a few mouse clicks

In short, it does THE SAME JOB better than a human. Use it!

By the way, I allow them to select the database by opening a Dialog window and when they find it I create [the connection string]

That too, is not an insurmountable problem with Datasets, but it IS a running problem. Walk first.

 

[Troy]

ok I didn't wish to rewrite almost my entire code to solve my issue. I did however solve my problem and I'll show my dinosaur code that made it work.

                '-- Run through the text boxes on the form, and
                '-- if they match up with a field from the record,
                '-- place the value back in the record.
                If txtLastName1.Text <> "" Then rowContact("LastName1") = txtLastName1.Text Else rowContact("LastName1") = System.DBNull.Value
                If txtFirstName1.Text <> "" Then rowContact("FirstName1") = txtFirstName1.Text Else rowContact("FirstName1") = System.DBNull.Value
                If txtMiddleInitial1.Text <> "" Then rowContact("MiddleInitial1") = txtMiddleInitial1.Text Else rowContact("MiddleInitial1") = System.DBNull.Value
                If txtLastName2.Text <> "" Then rowContact("LastName2") = txtLastName2.Text Else rowContact("LastName2") = System.DBNull.Value
                If txtFirstName2.Text <> "" Then rowContact("FirstName2") = txtFirstName2.Text Else rowContact("FirstName2") = System.DBNull.Value
                If txtMiddleInitial2.Text <> "" Then rowContact("MiddleInitial2") = txtMiddleInitial2.Text Else rowContact("MiddleInitial2") = System.DBNull.Value
                If txtCurrentStreet.Text <> "" Then rowContact("CurrentStreet") = txtCurrentStreet.Text Else rowContact("CurrentStreet") = System.DBNull.Value
                If txtCurrentCity.Text <> "" Then rowContact("CurrentCity") = txtCurrentCity.Text Else rowContact("CurrentCity") = System.DBNull.Value
                If txtCurrentState.Text <> "" Then rowContact("CurrentState") = txtCurrentState.Text Else rowContact("CurrentState") = System.DBNull.Value
                If txtCurrentZipCode.Text <> "" Then rowContact("CurrentZipCode") = txtCurrentZipCode.Text Else rowContact("CurrentZipCode") = System.DBNull.Value
                If txtCurrentCounty.Text <> "" Then rowContact("CurrentCounty") = txtCurrentCounty.Text Else rowContact("CurrentCounty") = System.DBNull.Value
                If txtFutureStreet.Text <> "" Then rowContact("FutureStreet") = txtFutureStreet.Text Else rowContact("FutureStreet") = System.DBNull.Value
                If txtFutureCity.Text <> "" Then rowContact("FutureCity") = txtFutureCity.Text Else rowContact("FutureCity") = System.DBNull.Value
                If txtFutureState.Text <> "" Then rowContact("FutureState") = txtFutureState.Text Else rowContact("FutureState") = System.DBNull.Value
                If txtFutureZipCode.Text <> "" Then rowContact("FutureZipCode") = txtFutureZipCode.Text Else rowContact("FutureZipCode") = System.DBNull.Value
                If txtFutureCounty.Text <> "" Then rowContact("FutureCounty") = txtFutureCounty.Text Else rowContact("FutureCounty") = System.DBNull.Value
                If txtHomePhoneAreaCode.Text <> "" Then rowContact("HomePhoneAreaCode") = txtHomePhoneAreaCode.Text Else rowContact("HomePhoneAreaCode") = System.DBNull.Value
                If txtHomePhonePrefix.Text <> "" Then rowContact("HomePhonePrefix") = txtHomePhonePrefix.Text Else rowContact("HomePhonePrefix") = System.DBNull.Value
                If txtHomePhoneSuffix.Text <> "" Then rowContact("HomePhoneSuffix") = txtHomePhoneSuffix.Text Else rowContact("HomePhoneSuffix") = System.DBNull.Value
                If txtFaxAreaCode.Text <> "" Then rowContact("FaxAreaCode") = txtFaxAreaCode.Text Else rowContact("FaxAreaCode") = System.DBNull.Value
                If txtFaxPrefix.Text <> "" Then rowContact("FaxPrefix") = txtFaxPrefix.Text Else rowContact("FaxPrefix") = System.DBNull.Value
                If txtFaxSuffix.Text <> "" Then rowContact("FaxSuffix") = txtFaxSuffix.Text Else rowContact("FaxSuffix") = System.DBNull.Value
                If txtCellPhoneAreaCode.Text <> "" Then rowContact("CellPhoneAreaCode") = txtCellPhoneAreaCode.Text Else rowContact("CellPhoneAreaCode") = System.DBNull.Value
                If txtCellPhonePrefix.Text <> "" Then rowContact("CellPhonePrefix") = txtCellPhonePrefix.Text Else rowContact("CellPhonePrefix") = System.DBNull.Value
                If txtCellPhoneSuffix.Text <> "" Then rowContact("CellPhoneSuffix") = txtCellPhoneSuffix.Text Else rowContact("CellPhoneSuffix") = System.DBNull.Value
                If txtOfficePhoneAreaCode.Text <> "" Then rowContact("OfficePhoneAreaCode") = txtOfficePhoneAreaCode.Text Else rowContact("OfficePhoneAreaCode") = System.DBNull.Value
                If txtOfficePhonePrefix.Text <> "" Then rowContact("OfficePhonePrefix") = txtOfficePhonePrefix.Text Else rowContact("OfficePhonePrefix") = System.DBNull.Value
                If txtOfficePhoneSuffix.Text <> "" Then rowContact("OfficePhoneSuffix") = txtOfficePhoneSuffix.Text Else rowContact("OfficePhoneSuffix") = System.DBNull.Value
                If txtExtension.Text <> "" Then rowContact("Extension") = txtExtension.Text Else rowContact("Extension") = System.DBNull.Value
                If txtEmail.Text <> "" Then rowContact("Email") = txtEmail.Text Else rowContact("Email") = System.DBNull.Value

The Secret was in the Else rowContact("Fieldname") = System.DBNull.Value part. If the Textbox is empty it simply writes a null back to the database.


I may be old school but sometimes old school rocks.


By the way, thanks for trying to help me Cjard!

I think what you're showing me is what VB generates basically when you create a database project using ADO and binding from a fixed database.

My project allows for DB selection and I have no fixed location for my database so I had a few more difficulties to overcome.
I did however learn how to bind controls to my project using the selected database. Once I filled a dataset it was easy to bind it.

They entirely fixed my Adding/Updating in my project now I'm working on deleting. I've encoundtered yet another small wall.

My DB is setup with 3 tables

Contacts
|
Packages
|
Services

For each contact you can have multiple Packages, for each package you can have multiple Services. My add/update works fine. but when I try to delete If I just delete the Contact Entry of course I still have all of the Package and service entries still in the DB. I need to remove all of the Service entries and Package entries before I remove the Contact entry. This I'm aware of. I think I can create a loop structure to accomplish this but I'm having problem tracking it in my head. Both Contact and Packages have a CONTACTID field Both Packages and Services have a PACKAGEID field. If someone could help me create the loop to search the Dataset for all Services with PACKAGEID 's matching Package entries with the same PACKAGEID's then allowing me to do the rowService.Delete() etc. I would appreciate that very much.

or if you know another way to delete all of the Information relating to my Contacts entry could you please elaborate.

Delete Code I'm working on is as follows:

                      For Each rowPackage In dtPackage.Rows
                            For Each rowService In dtService.Rows
                                If ((Not IsDBNull(rowPackage("PackageID"))) And rowPackage("ContactID") = rowContact("ContactID")) Then
                                    If (Not IsDBNull(rowService("ServiceID"))) And rowService("PackageID") = rowPackage("PackageID") Then
                                        rowService.Delete()
                                    End If
                                End If
                            next
                            rowPackage.Delete()


                        Next
                        '-- Delete the record from the datarow object
                        rowContact.Delete()

                        '-- Instantiate the command builder
                        ocbContact = New OleDb.OleDbCommandBuilder(daContact)
                        ocbPackage = New OleDb.OleDbCommandBuilder(daPackage)
                        ocbService = New OleDb.OleDbCommandBuilder(daService)

                        '-- Have the command builder create a Delete SQL command
                        daContact.DeleteCommand = ocbContact.GetDeleteCommand
                        daPackage.DeleteCommand = ocbPackage.GetDeleteCommand
                        daService.DeleteCommand = ocbService.GetDeleteCommand

                        '-- Perform the specified SQL command; then close the connection
                        daContact.Update(dsContact, "Contact")
                        daPackage.Update(dsPackage, "Package")
                        daService.Update(dsService, "Service")
                        'If Not (IsDBNull(rowPackage)) Then daPackage.Update(dsPackage, "Package")

                        dsContact.Tables("Contact").AcceptChanges()
                        dsPackage.Tables("Package").AcceptChanges()
                        dsService.Tables("Service").AcceptChanges()

                        'If Not (IsDBNull(rowPackage)) Then dsPackage.Tables("Package").AcceptChanges()

                        '-- Close the connection
                        daContact.DeleteCommand.Connection.Close()
                        daPackage.DeleteCommand.Connection.Close()
                        daService.DeleteCommand.Connection.Close()

                        'If Not (IsDBNull(rowPackage)) Then daPackage.DeleteCommand.Connection.Close()


                        'Clear TreeView Control
                        tvTreeview.Nodes.Clear()
                        configureTreeview()

                        Me.MdiChildren(indx).Close()
                        Exit Sub



                    End If
                Next


            Catch excData As Exception
                MessageBox.Show("Error Occurred: " & excData.Message)
            End Try

Thankyou.

Thanks again Cjard.

 

[cjard]

ok I didn't wish to rewrite almost my entire code to solve my issue.

A rewrite wouldnt be necessary. Its possible to phase this in gradually; ask pachjo - he successfully converted an old school project to new school, one form at a time and never encountered any problems

The Secret was in the Else rowContact("Fieldname") = System.DBNull.Value part. If the Textbox is empty it simply writes a null back to the database.

Indeed.. The crazy part is, it probably took me les stime to generate my entire project, than it did for you to paste all those nullvalue codes in

I may be old school but sometimes old school rocks.

In IT, the only sense that old school rocks is as in those things the hills are made of - keep up with the times, for the sake of the health of your career

By the way, thanks for trying to help me Cjard!

Youre welcome

I think what you're showing me is what VB generates basically when you create a database project using ADO and binding from a fixed database.

Maybe you missed the point I was making at the end of my last post. The database IS NOT fixed. It can be changed very easily, but that is way beyong your understanding right now, because you havent even started on the proper methods yet. Walk first, then run.

My project allows for DB selection and I have no fixed location for my database so I had a few more difficulties to overcome.

Youre making a huge mountain out of the molehill of changing a few bits of text in a connection string.. Stop worrying about it.

I did however learn how to bind controls to my project using the selected database. Once I filled a dataset it was easy to bind it.

Er.. well done! I bound all your controls (all 17 or so of em) with a single mouse drag. It surely cant get any easier than that?

They entirely fixed my Adding/Updating in my project now I'm working on deleting. I've encoundtered yet another small wall.

That's the crazy thing.. Look at how long youre spending on the menial aspects of getting just this one form to do basic database push and pull! If you did this new style, you'd be doing more interesting things and forgetting about the data access.. It's a huge wall that youre climbing, when you can just nip through the door at the bottom.. Write a PM to pachjo and ask him what his thoughts are of old school vs new school data access

My DB is setup with 3 tables

Contacts
|
Packages
|
Services

You know what I'm going to say, dont you? Here is the new school code for deleting a contact, in a dataset that has typed datatables and cascading datarelations:

mhmDataset.Contacts.FindByContactID("1234").Delete()
servicesTableAdapter.Update(mhmDataSet.Services)
packagesTableAdapter.Update(mhmDataSet.Packages)
contactsTableAdapter.Update(mhmDataSet.Contacts)

4 lines! Amazing?

See what I mean about "get away from the menial, hours banging your head on silly, low level database push and pull code and get onto something interesting"

This is why we abstract code. This is why we make different classes for different jobs. It stops our form button handlers being so full of code that has nothing to do with a GUI, that the class file ends up well over 100kb of code.

New school is embarrassingly easy for the little stuff.

As to your problem right now.. I cant even begin to describe the performance problem. youre basically going to search every row of services N number of times, where N is the row count of packages

Suppose you have just 10 related rows in each table..
10 contacts
100 packages
1000 services

your foreach loop is going to look through all package rows, then all service rows.. In short, its gonna perform 100 x 1000 = one hundred thousand IFs just to determine which rows to delete. THat's just not efficient!

I dont have the time, energy or inclination to launch into a big explanation how to do it old school (because it would take me 100 times longer to explain, that it would to do it new school), but I'll leave you with a tip: Check out adding some DataRelations to your set.. and look up GetChildRows() and GetParentRow()

Good luck.


ps; there's a section in the DW2 link on displaying and working with related data