?
Solved

vb.net - Updating data automatically - Part 2

Posted on 2007-07-25
12
Medium Priority
?
306 Views
Last Modified: 2013-11-26
I have written code in a VB.NET form to update the data in the database (The form was one that was auto generated via dragging and dropping).

I got this code from a previous question I had asked: http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22711111.html
I have added the code:
Me.StockTableAdapter.Update(Me.EmbDataSet.Stock)
to the event:
StockBindingSource_ListChanged

The problem now, is that it only works for the first update - Any subsequent updates generate the error:
"Column 'Code' does not allow nulls."

The Code column is the primary key, and indeed does not accept nulls. The problem is, that the Code is not a null - it has a value.

Can anyone suggest what I'm doing wrong here? What I can do to make it automatically update records consistantly?

Thanks.
0
Comment
Question by:fweeee
  • 7
  • 5
12 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 19564681
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
0
 

Author Comment

by:fweeee
ID: 19569970
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.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 19570697
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fweeee
ID: 19570798
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.
0
 

Author Comment

by:fweeee
ID: 19572397
Just tried as you suggested, and got 0 for both first and second time.
0
 

Author Comment

by:fweeee
ID: 19572403
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.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 19572853
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
0
 

Author Comment

by:fweeee
ID: 19572964
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.
0
 
LVL 34

Accepted Solution

by:
Sancler earned 1500 total points
ID: 19573252
I think I might have found the source of the problem, although I haven't worked it through yet and it is one which - as I mostly work with OleDb rather than SQL dataobjects, and I usually work with DataAdapters rather than TableAdapters - I would have to experiment to get to the bottom of.

OleDb does not accept "multiple" commands: that is, sending both an Update and a Select command to the database in the same operation.  SQL does.  And your update CommandText does both

            Me._adapter.UpdateCommand.CommandText = "UPDATE [dbo].[Stock] [...] " & "SELECT [...]"

Now, the effect of a SELECT command is that it changes the datatable - it adds a new (or updates an existing) record.  That datatable is the "List" in relation to which the "ListChanged" Event with which we are working fires.  So I reckon there is some sort of recursive call going on.  Quite how that produces the symptoms I am, as I say, not sure at the moment.

But can I suggest that you reconfigure the TableAdapter by unchecking the "Refresh the data table" option?  So far as I can see, at least for the purpose that we are looking at at the moment, it gains you nothing.  All the additional SELECT part of the CommandText is doing is bringing back the single record that has just been altered.  Your app already knows precisely what is in it.  It is not a case, as you explained earlier, where it is necessary to "refresh" to get hold of a Primary Key which has been freshly generated at the database end.  And the .AcceptChanges call is, in effect, putting the datatable and the database "in synch" with respect to rowstates as well as to values.

Roger
0
 

Author Comment

by:fweeee
ID: 19573400
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.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 19573855
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
0
 

Author Comment

by:fweeee
ID: 19573884
Rightio. Well thank you - I guess you solved the issue I was having.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Loops Section Overview
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question