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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

762 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