Accepting a DataSet in a Web Service and Updating the Database with the Dataset VB.NET

Posted on 2005-03-18
Medium Priority
Last Modified: 2012-05-05
Working with VB.net

I have a Web Service that needs to accept a dataset.
Once that dataset is accepted... it needs to process the dataset....it needs to...

update the existing rows and add any new rows to the database

The dataset has these following fields
recID   integer
rectext    string
bc1       integer
bc2      integer
bc3     integer
recvalue single

Database is sands
Table is rec_type

<System.Web.Services.WebService(Namespace:="http://ipaddress/Milllane2005/Retrieve")> _
Public Class Retrieve
    Inherits System.Web.Services.WebService

    Dim dsn As String = "Data Source = RECYCLE.milllane.local:Initial Catalog=Sands:Intergrated Security=SSPI"
    Dim myConn As SqlClient.SqlConnection = New SqlClient.SqlConnection(dsn)

 <WebMethod(Description:="Updates Clients Features.")> _
    Public Function UpdateFeatures(ByVal myDS As DataSet) As DataSet

        Dim myDA As SqlDataAdapter = New SqlDataAdapter

        myDA.UpdateCommand = New SqlCommand("Update Rec_Types Set rec_Text =  @recText, button_Color_1 = @buttonColor1, button_Color_2 = @buttonColor2, button_Color_3 = @buttonColor3, rec_Value = @recValue Where id = @recID", myConn)
        myDA.Update(myDS, "Rec_Types")

    End Function

This is what I have so far...
I have seen the dataset  and there are updates to 16 rows and the creation of 2 new rows in the table that need to be made.

I think the above will address the updates to the 16 rows ( will I need to put that in a loop) and how would the code look for adding 2 rows to the database.

Not quite sure how to read down through the dataset and take that data and place it in the database.

Also once the updates/additions are made ..I need to send back a message "Done" to the client ...which I could have either as a message box or display in a label on the form.

Any help or insight would be great.
Question by:nomar2
  • 3
  • 3
LVL 14

Expert Comment

ID: 13580852
How is your dataset getting populated with records? Typically you use the Adapter.Fill method to fill a dataset. Once filled, you can insert, remove, update rows in the dataset either through code or by binding it to a Grid control.

What I have done in the past is use the Web Service to fill the dataset as I described above. So make a call to the web service passing it a new dataset object. Have the web service fill it and return it to your client code.

Next, make your edits on the client end.

Finally, pass the dataset back to the web service and call the Adapter.Update method like so:

Call MyAdapter.Update(MyDataset.GetChanges())

You need to execute the GetChanges method in order to have the Update method process all the updates properly.

In order for all of this to work, you need to make sure the Adapter is properly configured with the appropriate Insert, Update & Delete commands.

You can achieve that using the CommandBuilder object (The syntax may be a little off. I am going from memory here. But you should get the jist of what I am saying):

Dim MyAdapter As New SqlDataAdapter("Select * FROM MyTable")

Dim cb as New SqlCommandBuilder(MyAdapter)
MyAdapter.InsertCommand = cb.GetInsertCommand
MyAdapter.UpdateCommand = cb.GetUpdateCommand
MyAdapter.DeleteCommand = cb.GetDeleteCommand

Now when you are ready to call the Adapter.Update method it should work just fine.

Author Comment

ID: 13591565
I wrote my code based on what you said...but now I get an error...maybe a fresh set of eyes will see the problem

This is on the client side...

        Dim t1 As New test.Retrieve 'this referneces my web service
        Dim myDS As DataSet = t1.RetrieveFeatures()
        Dim myTable As DataTable = myDS.Tables("rec_types")
        Dim newRow As DataRow = myTable.NewRow()

        newRow("id") = "17"
        newRow("rec_code") = "17"
        newRow("rec_text") = "coke"
        newRow("button_color_1") = "999"
        newRow("button_color_2") = "888"
        newRow("button_color_3") = "777"
        newRow("rec_value") = "0.05"

        Dim updateDB As DataSet = t1.UpdateFeatures(myDS.GetChanges())
        DataGrid1.DataSource = myDS

on the web service side I have the following 2 methods ...
<WebMethod(Description:="Method to retrieve the user preferences.")> _
    Public Function RetrieveFeatures() As DataSet

        'Dim myComm As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("Select id, rec_code, rec_Text, button_color_1, button_color_2, button_color_3, rec_Value FROM Rec_Types", myConn)
        Dim myComm As SqlDataAdapter = New SqlDataAdapter("Select id, rec_code, rec_Text, button_color_1, button_color_2, button_color_3, rec_Value FROM Rec_Types", myConn)

        Dim ds As DataSet = New DataSet
        myComm.MissingSchemaAction = MissingSchemaAction.AddWithKey

        myComm.Fill(ds, "Rec_Types")
        Return ds

    End Function
    <WebMethod(Description:="Updates Clients Features.")> _
    Public Function UpdateFeatures(ByVal ds As DataSet) As DataSet
        Dim myDA As SqlDataAdapter = New SqlDataAdapter

        myDA.InsertCommand = New SqlCommand("Insert into Rec_Types (id, rec_code, rec_text, button_color_1, button_color_2, button_color_3, rec_value) Values ( @id, @rec_code, @rec_text, @button_color_1, @button_color_2, @button_color_3, @rec_value)", myConn)

        myDA.InsertCommand.Parameters.Add("@id", SqlDbType.Int, 4, "id")
        myDA.InsertCommand.Parameters.Add("@rec_code", SqlDbType.Int, 4, "rec_code")
        myDA.InsertCommand.Parameters.Add("@rec_text", SqlDbType.Text, 64, "rec_text")
        myDA.InsertCommand.Parameters.Add("@button_color_1", SqlDbType.Int, 4, "button_color_1")
        myDA.InsertCommand.Parameters.Add("@button_color_2", SqlDbType.Int, 4, "button_color_2")
        myDA.InsertCommand.Parameters.Add("@button_color_3", SqlDbType.Int, 4, "button_color_3")
        myDA.InsertCommand.Parameters.Add("@rec_value", SqlDbType.Int, 9, "rec_value")

        myDA.UpdateCommand = New SqlCommand("Update Rec_Types Set id = @id, rec_code = @rec_code, rec_text = @rec_text, button_color_1 = @button_color_1, button_color_2 = @button_color_2, button_color_3 = @button_color_3, rec_value = @rec_value Where id = @OldID", myConn)
        'myDA.Update(myDS, "Rec_Types")

        myDA.UpdateCommand.Parameters.Add("@id", SqlDbType.Int, 4, "id")
        myDA.UpdateCommand.Parameters.Add("@rec_code", SqlDbType.Int, 4, "rec_code")
        myDA.UpdateCommand.Parameters.Add("@rec_text", SqlDbType.Text, 64, "rec_text")
        myDA.UpdateCommand.Parameters.Add("@button_color_1", SqlDbType.Int, 4, "button_color_1")
        myDA.UpdateCommand.Parameters.Add("@button_color_2", SqlDbType.Int, 4, "button_color_2")
        myDA.UpdateCommand.Parameters.Add("@button_color_3", SqlDbType.Int, 4, "button_color_3")
        myDA.UpdateCommand.Parameters.Add("@rec_value", SqlDbType.Int, 9, "rec_value")

        Dim myParm As SqlParameter = myDA.UpdateCommand.Parameters.Add("@oldID", SqlDbType.Int, 4, "id")
        myParm.SourceVersion = DataRowVersion.Original

        myDA.DeleteCommand = New SqlCommand("Delete from rec_types where id = @id", myConn)
        myParm = myDA.DeleteCommand.Parameters.Add("@id", SqlDbType.Int, 4, "id")
        myParm.SourceVersion = DataRowVersion.Original

        myDA.Update(ds, "rec_Types")
        Return ds

    End Function

when I run this ...I get the following error

Server was unable to process request. --> Value cannot be null. Parameter name: dataSet

I am ready to pull my hair...what is here that I am missing


Author Comment

ID: 13591624
I should tell you the table rec_types is set-up this way..

maybe in my insert statement I have the wrond datattype

column        type        size   null
id                 int           4
rec_code       int         4        1
rec_text        varchar    64      1
button_color_1  int     4       1
button_color_2  int     4       1
button_color_3  int    4       1
rec_value      numeric    9       1
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

LVL 14

Accepted Solution

ptakja earned 420 total points
ID: 13591751
Looks like you need to check to see what MyDS.GetChanges is returning. If that is returing nothing or an empty dataset then you would see that error.

Also in your Update statement, I am not positive on this but does the "Rec_Text" parameter need to be enclosed in single quotes as:

myDA.InsertCommand = New SqlCommand("Insert into Rec_Types (id, rec_code, rec_text, button_color_1, button_color_2, button_color_3, rec_value) Values ( @id, @rec_code, '@rec_text', @button_color_1, @button_color_2, @button_color_3, @rec_value)", myConn)

Author Comment

ID: 13591927
I put quotes around the '@rec_text' but that never solved anything..I have looked at a couple of example on the web and I think it doesn't need the quotes in the insert statement

How would I code-wise see what MyDS.GetChanges is returning.

Your help has been great

My field ( rec_value) is declared as a numeric...what is the equivalent in SQLDbType....I used an int...is this right
LVL 14

Expert Comment

ID: 13592236
int is fine.

To see what is in your dataset, try running the app in debug mode and set a breakpoint on that line. Then you can browse the variables, including the dataset to see what is happening.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…

593 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