vb.net - Updating data automatically - Part 2

I'm not sure that this quite explains the precise error message you are getting, but one thing you need to do is add

Me.EmbDataSet.Stock.AcceptChanges

immediately after

Me.StockTableAdapter.Update(Me.EmbDataSet.Stock)

It can depend on settings of the TableAdapter, I think, but under normal circumstances if you don't do that, the .RowState flag of the last record that has been inserted into the the database remains at .Added so, on the next call, the tableadapter tries to add it again.

Try that first, and see whether you are still getting an error and, if so, whether it is the same.

If it doesn't sort it, can you please say where the Primary Key is coming from?

Roger

Thanks Sancler, but that didnt work. The StockBindingSource_ListChanged event now reads:
        Me.StockTableAdapter.Update(Me.EmbDataSet.Stock)
        Me.EmbDataSet.Stock.AcceptChanges()

But upon the second time it is run, I get the error:
"Column 'Code' does not allow nulls."

This is happening after modifying two existing records (I modify the first record, all is happy, I modify the second,and get the error).

The primary key is the Code field. It is of type nvarchar. There is no code anywhere to populate it automatically - the user needs to populate it manually.

In theory, this is not making sense to me at the moment.  When an _existing_ record is modified it should already have a value (both in the database and in the datatable) in the Code column and the SQL will use that (primary key) value to identify the database record that is to be updated.  The CommandText will be in the general form

   UPDATE <table> SET <thisField> = <thisValue> WHERE <PrimaryKeyField> = <PrimaryKeyValue>

But a TableAdapter's .Update method executes Insert and Delete commands as well as the Update command if there are any rows for which those are appropriate.  The description sounds more like an INSERT command being run and the database rejecting a _new_ record which breaches its non-null constraint: or even possibly the ListChanged Event code trying to commit a _new_ record to the datatable and that rejecting it because of a constraint violation.  Does either of those sound possible to you?  Is there any way that, even inadvertently, a _new_ record could be being generated between the first change being saved and the save of the second being attempted?

However unlikely this seems, it might - even if only to close the door in this line of enquiry - be worth checking.  If you stick this code

         Dim dv As New DataView(Me.EmbDataSet.Stock)
         dv.RowStateFilter = DataViewRowState.Added
         Debug.WriteLine(dv.Count)

immediately before

        Me.StockTableAdapter.Update(Me.EmbDataSet.Stock)

it should produce output of 0 on both the first and the second attempts at saving.

If that's OK, we'll need to dig deeper and in other areas.  But I'd be typing for ages if I tried to go over all the possibilities here and now.  So I'd prefer to take it stage by stage.

But, I'd better warn you, I'm in the UK and just off to bed so the next stage, if there is one, won't be for a few hours.

Roger

OK, thanks - I'll try that.

i'm in Australia, and at work at the moment, so I wont be able to try it until I get home, so the next stage at my end wont be happening for a few hours anyway.

Just tried as you suggested, and got 0 for both first and second time.

I should point out - my main goal here is to have bound forms that automatically update thte database, rather then having to click on the "Save" button it gives you. Fixing up this specific issue I'm having will be irrelevant if you can suggest a better way of doing it.

No, I don't think there is a _better_ way of doing it although if we have to we could try to see if triggers _other than_ ListChanged avoid the problem.  But I've just tested using that, and had no problem.

>>
The problem is, that the Code is not a null - it has a value.
<<

How do you know that?  If it is only because you can see it displayed in one of the controls on your form, that leaves the possibility that - for some reason not yet fathomed - that (bit of) the display is not properly reflecting the content of the datatable.  To check that out what is actually in the datatable, substitute this code

        Dim dt As DataTable = Me.EmbDataSet.Stock
        Dim dv As New DataView(dt)
        Dim dv1 As New DataView(dt)
        dv.RowStateFilter = DataViewRowState.ModifiedCurrent
        dv1.RowStateFilter = DataViewRowState.ModifiedOriginal
        For i As Integer = 0 To dv.Count - 1
            For j As Integer = 0 To dt.Columns.Count - 1
                Debug.WriteLine(dt.Columns(j).ColumnName & " = " & dv.Item(i).Item(j) & " was " & dv1.Item(i).Item(j))
            Next
        Next

for that I gave you before.  The first line just creates a "helper variable", to save keep having to type the full table reference out.  It also means that I can test out the code on my system then directly copy and paste it onto here with only that reference having to be changed.  That reduces the chance of typos creeping in.

The other thing it might be worth doing at this stage is inspecting the Update command that the TableAdapter is using.  If you make sure that Show All Files is clicked in Solution Explorer and then double-click on your dataset's Designer.vb  file it will open in the code window.  If you then click in the lefthand dropdown on the tableadapter, and in the righthand dropdown on InitAdapter, you will see the code for the various commands, both CommandText and Parameters.  If you do that, you might yourself be able to spot some anachronism.  If not, can you copy the code - both commandtext and parameters - for the Update command and paste it here.

Roger

Ok,

Well, I put that code in (replacing the code you gave me before, but leaving my old code:
        Me.StockTableAdapter.Update(Me.EmbDataSet.Stock)
        Me.EmbDataSet.Stock.AcceptChanges()
at the end.

The strange thing is, it didnt seem to do anything at first. I:
- ran the app (a container form opens up).
- ran the stock list form within the main container form
- changed a value - nothing in the debugger
- changed another value (in another record), Nothing in the output, but the app crashed with the same error.

I tried something different the second time around. I:
- ran the app (a container form opens up).
- ran the stock list form within the main container form
- changed a value - nothing in the debugger
- Closed down the stock list form (while leaving the app running).
- Re-opened the stock list form.
- changed a value. App doesnt crash, I get output.
- changed another value - app crashes, I get no output.

Copy and paste of the output I got:
Code = stock7 was stock7
Description = Stock item 7 was Stock item 7
Brand_Code =  was
Default_Supplier = SUPP3 was SUPP3
Last_Purch_Cost = 4.0000 was 4.0000
Reorder_Point = 3 was 2
Second_Supplier =  was

I should also point out, the error I'm getting is different now - it is erroring on the line:
                Debug.WriteLine(dt.Columns(j).ColumnName & " = " & dv.Item(i).Item(j) & " was " & dv1.Item(i).Item(j))
with the error:
"Object reference not set to an instance of an object."


I foundthe stuff for the update command, but I really dont know how it should look, so I couldnt find anything that stood out. Copy and paste included:
----------------COPY START-----------------
            Me._adapter.UpdateCommand = New System.Data.SqlClient.SqlCommand
            Me._adapter.UpdateCommand.Connection = Me.Connection
            Me._adapter.UpdateCommand.CommandText = "UPDATE [dbo].[Stock] SET [Code] = @Code, [Description] = @Description, [Brand_Cod"& _
                "e] = @Brand_Code, [Default_Supplier] = @Default_Supplier, [Last_Purch_Cost] = @L"& _
                "ast_Purch_Cost, [Reorder_Point] = @Reorder_Point, [Second_Supplier] = @Second_Su"& _
                "pplier WHERE (([Code] = @Original_Code) AND ([Description] = @Original_Descripti"& _
                "on) AND ((@IsNull_Brand_Code = 1 AND [Brand_Code] IS NULL) OR ([Brand_Code] = @O"& _
                "riginal_Brand_Code)) AND ((@IsNull_Default_Supplier = 1 AND [Default_Supplier] I"& _
                "S NULL) OR ([Default_Supplier] = @Original_Default_Supplier)) AND ((@IsNull_Last"& _
                "_Purch_Cost = 1 AND [Last_Purch_Cost] IS NULL) OR ([Last_Purch_Cost] = @Original"& _
                "_Last_Purch_Cost)) AND ((@IsNull_Reorder_Point = 1 AND [Reorder_Point] IS NULL) "& _
                "OR ([Reorder_Point] = @Original_Reorder_Point)) AND ((@IsNull_Second_Supplier = "& _
                "1 AND [Second_Supplier] IS NULL) OR ([Second_Supplier] = @Original_Second_Suppli"& _
                "er)));"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"SELECT Code, Description, Brand_Code, Default_Supplier, Last_Purch_Cost,"& _
                " Reorder_Point, Second_Supplier FROM Stock WHERE (Code = @Code)"
            Me._adapter.UpdateCommand.CommandType = System.Data.CommandType.Text
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Code", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Code", System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Description", System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Brand_Code", System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Brand_Code", System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Default_Supplier", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Default_Supplier", System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Last_Purch_Cost", System.Data.SqlDbType.Money, 0, System.Data.ParameterDirection.Input, 0, 0, "Last_Purch_Cost", System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Reorder_Point", System.Data.SqlDbType.[Decimal], 0, System.Data.ParameterDirection.Input, 18, 0, "Reorder_Point", System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Second_Supplier", System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Second_Supplier", System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Code", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Code", System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Description", System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Description", System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IsNull_Brand_Code", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Brand_Code", System.Data.DataRowVersion.Original, true, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Brand_Code", System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Brand_Code", System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IsNull_Default_Supplier", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Default_Supplier", System.Data.DataRowVersion.Original, true, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Default_Supplier", System.Data.SqlDbType.NVarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Default_Supplier", System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IsNull_Last_Purch_Cost", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Last_Purch_Cost", System.Data.DataRowVersion.Original, true, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Last_Purch_Cost", System.Data.SqlDbType.Money, 0, System.Data.ParameterDirection.Input, 0, 0, "Last_Purch_Cost", System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IsNull_Reorder_Point", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Reorder_Point", System.Data.DataRowVersion.Original, true, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Reorder_Point", System.Data.SqlDbType.[Decimal], 0, System.Data.ParameterDirection.Input, 18, 0, "Reorder_Point", System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IsNull_Second_Supplier", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Second_Supplier", System.Data.DataRowVersion.Original, true, Nothing, "", "", ""))
            Me._adapter.UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Second_Supplier", System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 0, 0, "Second_Supplier", System.Data.DataRowVersion.Original, false, Nothing, "", "", ""))
-----------------COPY END------------------

Also, just to prove that I wasnt going crazy, I created another list form, for another table (in the same database). I added similar code to automatically update the database, and I get the exact same error.

OK - I have reconfigured the dataadaptor (StockTableAdapter). And it seems to have taken care of the problem - I can now change a few records in a row without getting an error.

However, I am now getting a new error. It seems at random I get a error titled "DataGridView Default Error Dialog".
"System.InvalidOperationException: DataTAble internal index is corrupted: '5'."
After that there is a very detailed log of what happened.

I was changing a normal field in the table - not an indexed field.

That's a different issue ;-)

When you've got time (and you'll need lots) have a look at this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277680&SiteID=1

I haven't read it all myself as it appears to be an SQL issue, and I mostly work with OleDb and Access.  Near the bottom of page 5 in the thread it says there is a fix for what MS acknowledges is a bug, but reading on (a bit) from that it seems that getting hold of that fix might not be as straightforward as it should be.  Good luck.

Roger

Rightio. Well thank you - I guess you solved the issue I was having.