Solved

Multiple Update Commands on a Table Adapter / Multiple TableAdapters on same dataset

Posted on 2010-09-16
19
1,073 Views
Last Modified: 2013-11-26
Using Visual Basic.net in Visual Studio 205

I have an application that uses a Table Adapter to fill a typed table within a dataset which is then used as the datasource for a datagrid, allowing for user editing and then using the Table Adapter Update command to save the changes.

All works well.  However now I want to be able to also load the data in a summary form (i.e. grouping like rows together using GROUP BY in the Select command).

I can create 2 Select (Fill) commands, but the Update command for saving changes made to summary lines needs to be different to the one I already use.

For example, the current Update Command is in the form

UPDATE Products Set ProductType = @ProductType, Details = @Details WHERE PartId = @Original_PartID

if I am working with Summary data I need something like:

UPDATE Products Set Details = @Details WHERE ProductType = @Original_ProductType

(i.e. change to single row in dataset could update multiple rows in SQL table)

Using a TableAdapter I don't seem to be able to access the Update Command to change it.  If I create a new query in the Table Adapter it doesn't take either a datatable or a datarow as an argument.  Instead it wants all the individual fields passed as parameters (in reality there are actually many columns to update).

I tried creating two separate table adapters but am having problems updating the summary lines.  I am getting a "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" error, which I suspect is down to my Update Command, although in debuging it looks correct, the data seems correct and if I try the update manually in SQL management studio it works.

So, which is the best approach?  Can I do this with a single table adapter?  Also is there anyway I can see the actual SQL Update command sent for any record in the dataset (rather than just the parameterised command as this seems OK)

 
0
Comment
Question by:WestcountryBusiness
  • 11
  • 8
19 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33690621
hi ...
The summary Form Uses the same dataset?
If so and your summary data are displayed in another form you can create a new dataset with the same table...
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33690660
you can name the firtst dataset ProductsDataset and the other ProductsSumDataset...both of them uses the same table Products...
You can do that ...(if i understand correctly your question :)    )
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33690745
Can you please post also your update command?
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33690776
The problem is that when you try to update data with two update commands with the same table adapter your are causing the violation ...
try to use different datasets and use the update separatelly not with UpdateAll...and it  is usefull to use Dataset.HasChanges Method
0
 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 33690957
Here's the actual Update Command:

UPDATE       Specs
SET                Batch_Id = @Batch_Id, Desc_5 = @Desc_5, Bay_No = @Bay_No, Status = @Status, Damage_Code = @Damage_Code, Part_UnId = @Part_UnId,
                         Part_Ref = @Part_Ref, Quality = @Quality, Material_Type = @Material_Type, Desc_1 = @Desc_1, Desc_2 = @Desc_2, Desc_3 = @Desc_3,
                         Desc_4 = @Desc_4, Customer_Ref_1 = @Customer_Ref_1, Customer_Ref_2 = @Customer_Ref_2, Customer_Ref_3 = @Customer_Ref_3,
                         Agency = @Agency, Date_InStock = @Date_InStock, Deliver_to = @Deliver_to, Sell_to = @Sell_to, Product = @Product,
                         Shipping_Principal = @Shipping_Principal, Site_Ref = @Site_Ref, Storage_Area = @Storage_Area, Date_StockChecked = @Date_StockChecked,
                         Value_2 = @Value_2, Value_1 = @Value_1, Invoice_Principal = @Invoice_Principal
WHERE        (Spec_Id IN
                             (SELECT        Spec_Id
                               FROM            Specs AS s1
                               WHERE        (Batch_Id = @Original_Batch_id) AND (Damage_Code = @Original_Damage_Code) AND (Desc_5 = @Original_Desc_5) AND
                                                         (Bay_No = @Original_Bay_No) AND (Status = @Original_Status)))

the 'Specs' table contains the products.  The products are large individually traceable items so every single item in stock is created as an entry in this table, each with its own unique serial number.  However, many of the stock items are effectively the same and have the same.  If so, they will share a Batch_Id.  Stock items in the same batch (with other matching criteria) can be grouped together in certain cases.

So there is only 1 form (Stock Management) - on this form there is an option when loading to show Summary or Detail.  When the form is saved, either the detail data adapter OR the summary data adapter will be used for the update - never both so that's not what's causing the concurrency conflict.

I have tried applying the Update for each row and get the same error.  I tried creating a custom query to perform the update manually - even though this means passing every field as a parameter.  This resulted in a call like this:


Unfortunately as many of these fields can be null I get a type conversion error (can't covert Null to String etc).

Any ideas?
Using ta As New ds_StocksTableAdapters.Specs_SummaryTableAdapter

    ta.Connection = SQLConn

    For Each rw As DataRow In changes.Rows

ta.UpdateSummaryRow(rw.Item("Part_Ref"), rw.Item("Quality"), rw.Item("Material_Type"), _

rw.Item("Desc_1"), rw.Item("Desc_2"), rw.Item("Desc_3"), rw.Item("Desc_4"), rw.Item("Desc_5"), _

rw.Item("Bay_No"), rw.Item("Status"), rw.Item("Damage_Code"), rw.Item("Customer_ref_1"), _

rw.Item("Customer_ref_2"), rw.Item("Customer_ref_3"), rw.Item("Agency"), rw.Item("Date_InStock"), _

rw.Item("Deliver_To"), rw.Item("Sell_To"), rw.Item("Product"), rw.Item("Shipping_Principal"), _

rw.Item("Site_ref"), rw.Item("Storage_Area"), rw.Item("Date_StockChecked"), _

rw.Item("Value_1"), rw.Item("Value_2"), rw.Item("Invoice_Principal"), _

rw.Item("Batch_Id", DataRowVersion.Original), rw.Item("Damage_Code", DataRowVersion.Original), _

rw.Item("Desc_5", DataRowVersion.Original), _

rw.Item("Bay_no", DataRowVersion.Original), rw.Item("Staus", DataRowVersion.Original))

Next

End Using

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33691103
With this Kind of command it is better to work like this(Assuming you are using Sql Server)
Private Sub AppendChanges()





        Dim conn As New SqlConnection(My.Settings.MyConnectionString)

        



        If conn.State = ConnectionState.Open Then conn.Close()

        conn.Open()

        Try

            Dim intRowsAffected As Integer

            Dim cmd As New SqlCommand("UPDATE [MyTable]" & _

                                       " SET Column1=@Column1,Column2=@Column2,Column3=@Column3 WHERE [Column4]=@Column4", conn) 'or Another Select Command as in your case



            Dim valid As Boolean = False

            Dim HasRows As Boolean = False



            



            Dim Param1 As New SqlParameter("@Column1",SomeValue1)

            Dim Param2 As New SqlParameter("@Column1", SomeValue2)

            Dim Param3 As New SqlParameter("@Column1", SomeValue3)

            Dim Param4 As New SqlParameter("@Column1", SomeValue4)

            cmd.Parameters.Add(Param1)

            cmd.Parameters.Add(Param2)

            cmd.Parameters.Add(Param3)

            cmd.Parameters.Add(Param4)

           

           intRowsAffected = cmd.ExecuteNonQuery    'this one makes the update





            conn.Close()





            cmd.Dispose()

            conn.Dispose()



        Catch e As SqlException

            MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error")



        Catch e As Exception

            MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")





        End Try

    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 33691532
Thanks.  I've given that a try (see my version of your code below - I'm only updating 1 fields for now for simplicity of testing)

It still isn't updating any rows though!

In the debugger, I've checked the values of each of the rw.item("") fields and all seems correct.  I manually created the update command from this data and tried it in SQL Management Studio.

Here's the SQL command:

UPDATE Specs SET Desc_5 = 'blue' WHERE (Batch_Id = 14) AND (Damage_Code IS NULL) AND
        (Desc_5 IS NULL) AND (Bay_No IS NULL) AND (Status = 'Inbound   ')

that successfully updated 2 rows (as it should) so I can't see why the coded version won't.  Is it perhaps the way the NULL values are passed?  Do I need to set the Nullable property in each of the parameter declarations?

Thanks again for your help


    Private Sub SaveSummary(ByVal changes As DataTable)



        Using cmd As New SqlCommand("", SQLConn)



            Dim intRowsAffected As Integer

            Try



                cmd.CommandText = "UPDATE Specs SET Desc_5 = @Desc_5 " & _

                               "WHERE (Spec_Id IN (SELECT Spec_Id FROM Specs AS s1 " & _

                                    "WHERE (Batch_Id = @Original_Batch_id) AND (Damage_Code = @Original_Damage_Code) AND " & _

                                    "(Desc_5 = @Original_Desc_5) AND (Bay_No = @Original_Bay_No) AND (Status = @Original_Status)))"



                cmd.Connection.Open()



                For Each rw As DataRow In changes.Rows

                    cmd.Parameters.Add(New SqlParameter("@Desc_5", rw.Item("Desc_5")))

                    cmd.Parameters.Add(New SqlParameter("@Original_Batch_id", rw.Item("Batch_Id", DataRowVersion.Original)))

                    cmd.Parameters.Add(New SqlParameter("@Original_Damage_Code", rw.Item("Damage_Code", DataRowVersion.Original)))

                    cmd.Parameters.Add(New SqlParameter("@Original_Desc_5", rw.Item("Desc_5", DataRowVersion.Original)))

                    cmd.Parameters.Add(New SqlParameter("@Original_Bay_No", rw.Item("Bay_no", DataRowVersion.Original)))

                    cmd.Parameters.Add(New SqlParameter("@Original_Status", rw.Item("Status", DataRowVersion.Original)))



                    intRowsAffected = cmd.ExecuteNonQuery    'this one makes the update



                    cmd.Parameters.Clear()

                Next



            Catch e As SqlException

                MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error")



            Catch e As Exception

                MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")



            Finally

                cmd.Connection.Close()

            End Try



        End Using

    End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33691661
becuase you are using 2 different tables rewrite the statement using also table name before the field like :[Specs].Spec_Id
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33691665
Otherwise if the table is the same ...create a query for the select statement
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 33691706
Thanks but no luck, I'm afraid.  I changed the update query to simplify it.  Its now:

cmd.CommandText = "UPDATE Specs SET Desc_5 = @Desc_5 " & _
                                  "WHERE (Batch_Id = @Original_Batch_id) AND (Damage_Code = @Original_Damage_Code) AND " & _
                                  "(Desc_5 = @Original_Desc_5) AND (Bay_No = @Original_Bay_No) AND (Status = @Original_Status)"

So no chance of thinking there are two tables, but the update still returns zero records affected. I'll try stripping it down even further & then build it back up to see if I can see where it is.

In the mean time, if you have any other suggestions - feel free to chip in!  Thanks.
0
 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 33691872
I think it must be something in the parameter config for DBNULL values.

I just changed the update WHERE clause to only include fields where the value wasn't NULL (i.e. Batch ID & Status) and the update worked fine.

I've got to go out for a while now, but will try & pin this down when I get back later.

Thanks for your help.
0
 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 33693685
OK back on this now.  I thought I'd save some time & create the update command by building an SqlDataAdapter in designer, building the update query and copying the parameter code generated.

So I have a line of code that creates parameters, along these lines:

cmd.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {New System.Data.SqlClient.SqlParameter("@Batch_Id", System.Data.SqlDbType.[Variant], 1024, "Batch_Id"), New System.Data.SqlClient.SqlParameter("@Desc_5", System.Data.SqlDbType.NVarChar, 50, "Desc_5"), New System.Data.SqlClient.SqlParameter("@Bay_No", System.Data.SqlDbType.NVarChar, 32, "Bay_No"), New System.Data.SqlClient.SqlParameter("@Status", System.Data.SqlDbType.NChar, 16, "Status")

etc.

but how do I set the parameters to the actual values?  In the TableAdapter I'd just pass the datatable & the adapter would handle it.

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33693866
Can you try cmd.parametres.addwithvalue ?
0
 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 33694183
Thanks - I tried the addwithvalue.  I'm not sure what the difference is compared to the original add method with the value passed as a param, but worth a go.  Anyway no change.

The problem, in is simplest form, is this:

Why does the update below fail (i.e. find not matching records to update) when one or more of the 'Original_Value' parameters is NULL?

Really appreciate your help because I'm struggling here ...
Using cmd As New SqlCommand("", SQLConn)

  Dim intRowsAffected As Integer

  cmd.CommandText = "UPDATE Specs SET Desc_5 = @Desc_5 " & _

  "WHERE (Batch_Id = @Original_Batch_id)  AND (Damage_Code = @Original_Damage_Code) " & _

  "AND (Desc_5 = @Original_Desc_5) AND (Bay_No = @Original_Bay_No) AND (Status = @Original_Status)"

    cmd.Connection.Open()

        For Each rw As DataRow In changes.Rows

                    cmd.Parameters.AddWithValue("@Desc_5", rw.Item("Desc_5"))

                    cmd.Parameters.AddWithValue("@Original_Batch_id", rw.Item("Batch_Id", DataRowVersion.Original))

                    cmd.Parameters.AddWithValue("@Original_Damage_Code", rw.Item("Damage_Code", DataRowVersion.Original))

                    cmd.Parameters.AddWithValue("@Original_Desc_5", rw.Item("Desc_5", DataRowVersion.Original))

                    cmd.Parameters.AddWithValue("@Original_Bay_No", rw.Item("Bay_no", DataRowVersion.Original))

                    cmd.Parameters.AddWithValue("@Original_Status", rw.Item("Status", DataRowVersion.Original))

                    intRowsAffected = cmd.ExecuteNonQuery

                    cmd.Parameters.Clear()

         Next

  cmd.Connection.Close()

           

End Using

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33694713
Ok let's try something else ...close similar with the above...
try to find out first if the values passed as parameters null or nothing...then if is nothing exit the sub otherwise call the update method

If not IsDbnull(Me.Value) or Me.Value IsNot Nothing Then .....
'update Sub
Else
Exit sub
End If
0
 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 33699318
Morning!

Unfortunately I can't do as you suggest as I still need the update to work when one or more of the values are NULL.  

Of the 5 search values, 3 may be null (Batch_ID and Status will always have a value), so I have created a solution to this problem by coding my Update Command as below.

OK, so it works.  I just can't believe its the 'right' or best way of solving this problem.  I can't help thinking that if it works for an update command in a table adapter, why can't I recreate the same functionality in my own update command.

Given the time its taken to get me here, I may need to leave this as the solution but I just wish I'd found a more elegant solution.  Thanks for all your input though - wasn't even close without it!

For Each rw As DataRow In changes.Rows

                    cmd.CommandText = "UPDATE Specs SET Desc_5 = @Desc_5 " & _

                                  "WHERE (Batch_Id = @Original_Batch_id)  AND (Status = @Original_Status) "

                    If IsDBNull(rw.Item("Damage_Code", DataRowVersion.Original)) Then

                        cmd.CommandText &= "AND (Damage_Code IS NULL) "

                    Else

                        cmd.CommandText &= "AND (Damage_Code = @Original_Damage_Code) "

                    End If

                    If IsDBNull(rw.Item("Desc_5", DataRowVersion.Original)) Then

                        cmd.CommandText &= "AND (Desc_5 IS NULL) "

                    Else

                        cmd.CommandText &= "AND (Desc_5 = @Original_Desc_5) "

                    End If

                    If IsDBNull(rw.Item("Bay_No", DataRowVersion.Original)) Then

                        cmd.CommandText &= "AND (Bay_No IS NULL) "

                    Else

                        cmd.CommandText &= "AND (Bay_No = @Original_Bay_No) "

                    End If

                    cmd.Parameters.AddWithValue("@Desc_5", rw.Item("Desc_5"))

                    cmd.Parameters.AddWithValue("@Original_Batch_id", rw.Item("Batch_Id", DataRowVersion.Original))

                    cmd.Parameters.AddWithValue("@Original_Damage_Code", rw.Item("Damage_Code", DataRowVersion.Original))

                    cmd.Parameters.AddWithValue("@Original_Desc_5", rw.Item("Desc_5", DataRowVersion.Original))

                    If Not IsDBNull(rw.Item("Bay_No")) Then cmd.Parameters.AddWithValue("@Original_Bay_No", rw.Item("Bay_no", DataRowVersion.Original))

                    cmd.Parameters.AddWithValue("@Original_Status", rw.Item("Status", DataRowVersion.Original))

                    intRowsAffected = cmd.ExecuteNonQuery

                    cmd.Parameters.Clear()

                Next

Open in new window

0
 
LVL 18

Accepted Solution

by:
John (Yiannis) Toutountzoglou earned 500 total points
ID: 33699359
Your Code is very logic ...If it is working you may accept it as the solution ....
0
 
LVL 1

Author Closing Comment

by:WestcountryBusiness
ID: 33699374
Thanks for all your help.  I do now have a solution that works.

I would still like to know if there is a better was of passing NULL value parameters to an Update query, but may raise that as a separate question elsewhere.
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33699390
null is also a value ..consider also if the value is just "Nothing"...Glad  helping you...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Object to array 7 28
Using MS Code on my Mac 6 45
VB.net PaintEventArgs Handling. 10 27
.net VBA word safemode 1 24
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now