Problem in Saving

[xpertwinner]

I have a program which deals with submarines. The submarine has various modules, which in turn are composed of various components.

I have a database with a table of modules and a table of components, and a 1:N relationship between them, that is, a module has various components.

When I start my program, I copy the database to some objects in my program, so that the user can handle them in the way he wants. In the end, the user can save the final submarine back into the database.

However, I am having a problem saying cannot insert NULL ID in Modules, but I don't have any null ID, i have tried to debug. Here is the code:


Me.SubDataSet.Clear()
Dim compID As Integer = 1
For i As Integer = 0 To Submarine.Count() - 1
Dim row As SubDataSet.ModuloRow = SubDataSet.Modulo.NewModuloRow()
row.ID_Module = Submarine(i).ID_Module
row.Name = Submarine(i).Name
row.length = Submarine(i).Length
row.Position = Submarine(i).Position
SubDataSet.Modulo.AddModuloRow(row)
For j As Integer = 0 To Submarine(i).Components.Count() - 1
Dim comprow As SubDataSet.ComponentsRow = SubDataSet.Components.NewComponentsRow()
comprow.ID_Component = compID
compID += 1
comprow.ID_Module = Submarine(i).ID_Module
comprow.Name = Submarine(i).Components(j).Name
comprow.Material = Submarine(i).Components(j).Material
comprow.Volume = Submarine(i).Components(j).Volume
comprow.weight = Submarine(i).Components(j).Weight
comprow.buoyancy = Submarine(i).Components(j).Buoyancy
comprow.CGX = Submarine(i).Components(j).CGX
comprow.CGY = Submarine(i).Components(j).CGY
comprow.CGZ = Submarine(i).Components(j).CGZ
comprow.CBX = Submarine(i).Components(j).CBX
comprow.CBY = Submarine(i).Components(j).CBY
comprow.CBZ = Submarine(i).Components(j).CBZ
'SubDataSet.Componente.AddComponenteRow(row, Submarine(i).Components(j).Name, Submarine(i).Components(j).Material, Submarine(i).Components(j).Volume, Submarino(i).Components(j).Weight, Submarine(i).Components(j).Buoyancy, Submarine(i).Components(j).CGX, Submarine(i).Components(j).CGY, Submarine(i).Components(j).CGZ, Submarine(i).Components(j).CBX, Submarine(i).Components(j).CBY, Submarine(i).Components(j).CBZ)
SubDataSet.Components.AddComponentsRow(comprow)
Next

MsgBox("MODULE " & row.ID_Module & "ADDED")
Next
MsgBox("Nro of components" & SubDataSet.Componente.Rows.Count)
Me.Validate()
Me.ModuleBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.SubDataSet)
MsgBox("submarine saved")


The error is just in the UpdateAll line. I tried to translate the code to english, so dont worry if there are any errors in writing. thanks in advance.

 

[jmcilhinney]

Are you setting the ID values yourself or is the database supposed to be generating then for you? If it's the former, how are you creating and setting them? If it's the latter, have you actually configured the database correctly to do that?

 

[xpertwinner]

Previously I had the ID's to auto increment, but then I would have the trouble to find the nextID all the time, so I decided that the ID's would be the primary key, but not have autoincrement. For that, I unchecked the "is identity" checkbox.

In my program, when I add a new module to the "submarine" object, I automatically generate the ID, by finding the highest from the previous ID's and adding one.

The point is, that I am debugging and I see four different ID's ( 1, 2 3 and 4) and I am getting errors like the one previously stated, and now also "Violation of PRIMARY KEY constraint 'PK_Modulo'. Cannot insert duplicate key in object 'dbo.Modules'. The statement has been terminated."

I was wondering if I there was a problem in deleting the old values in the dataset, adding the same values all over again, and then updating.. Do I need to update in each step, or can I just update in the end?

 

[xpertwinner]

I figured out what my real problem is. I guess that I am not actually clearing the database before I insert the new modules, because I tried to insert the submarine with diferent values for the modules, and it worked. However, the previous values weren't erased :S

 

[jmcilhinney]

What you apparently don't realise is that getting the generated IDs from an identity column is no trouble at all. You simply add a SELECT statement to your InsertCommand immediately after the INSERT statement, which the IDE will even do for you by default if you create a typed DataSet. Your SQL code just needs to look something like this:

INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2);
SELECT ID, Column1, Column2 FROM MyTable

When you add a row to a DataTable it will then generate a temporary ID, which can be used in child records. When you insert the parent data the database generates the final ID, the TableAdapter refreshes the DataTable contents and the DataRelation automatically refreshes the related records in the child DataTable. You don't have to do anything.

 

[xpertwinner]

I understand what you are saying. But how do I use SQL statements? I am only using automatically generated table adapters, binding sources, etc. from dragging the dataset to the form.
I was hoping that by updating the dataset I could update the tables in the database. But you are saying I should update the tables directly by SQL, if I understood. So I would need to create an SQL connector? How can I connect to the database in the server explorer? Where do I put the code :

Code:

INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2);
SELECT ID, Column1, Column2 FROM MyTable

 

[MattP]

I am only using automatically generated table adapters, binding sources, etc. from dragging the dataset to the form.

Double click your xsd file in Solution Explorer.
r-click your fill method and select 'Configure'
Click on 'Advanced Options'
Check 'Refresh the data table'

 

[xpertwinner]

it is already checked..

 

[jmcilhinney]

it is already checked..

But that's no help if the ID column isn't an identity. You need to make the ID column an identity and then checking that box will cause SQL code like I mentioned earlier to be generated and you'll automatically get the generated IDs in your DataTable after saving.