Solved

Dataset update not updating DB.

Posted on 2004-08-22
6
524 Views
Last Modified: 2008-02-01
Hi,
I'm running an example to use Dataset update function to update DB, but it's not working. therre is no exception, and the dataset's datatable has been uupdated, but not the real SQL DB. I checked the priviledge, it's no problem. Any ideas?

part of the code:


                            Try
                                SqlDataAdapter_local.SelectCommand.CommandText = "select * from tbl_adsl_signup"
                                SqlDataAdapter_local.SelectCommand.CommandType = CommandType.Text
                                SqlDataAdapter_local.SelectCommand.Connection = SqlConnection_local
                                SqlConnection_local.Open()
                                SqlDataAdapter_local.Fill(DataSet_local, "tbl_adsl_signup")
                                ' Create command builder. This line automatically generates the update commands for you, so you don't
                                ' have to provide or create your own.
                               Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(SqlDataAdapter_local)
                                ' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
                                ' key & unique key information to be retrieved unless AddWithKey is specified.
                                SqlDataAdapter_local.MissingSchemaAction = MissingSchemaAction.AddWithKey
                                ' SqlDataAdapter_local.UpdateCommand.Connection() = SqlConnection_local
                                Dim myDataRow, localDataRow As DataRow
                                Dim loop_int As Integer = 0
                                 For Each myDataRow In DataSet_remote.Tables("tbl_adsl_signup").Rows
                                    If (loop_int >= local_int(i)) Then
                                        DataSet_local.Tables("tbl_adsl_signup").ImportRow(myDataRow)
                                    End If
                                    loop_int += 1
                                Next
                                'SqlConnection_local.Open()
                                ' Update Database with SqlDataAdapter
                                SqlDataAdapter_local.Update(DataSet_local.Tables("tbl_adsl_signup"))
                                 SqlConnection_local.Close()
                            Catch ex As Exception
                                Console.WriteLine(ex.ToString())

                            End Try



here is the full code.


Imports System.Data.SqlClient
Public Class f_update
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents get_data As System.Web.UI.WebControls.ImageButton
    Protected WithEvents message As System.Web.UI.WebControls.Label

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region
    Dim SqlDataAdapter_remote As SqlDataAdapter
    Dim SqlDataAdapter_local As SqlDataAdapter
    Dim SqlConnection_remote As SqlConnection
    Dim SqlConnection_local As SqlConnection
    Dim DataSet_remote As DataSet
    Dim DataSet_local As DataSet

    Dim local_int(7) As Integer
    Dim remote_int(7) As Integer
    Dim outParameter As SqlClient.SqlParameter
    Dim i As Integer = 0
    Dim spCommand() As String = {"get_adsl_total", "get_apac_total", "get_dial_total", "get_dpac_total", "get_tele_total", "get_dler_total", "get_csid_total"}
    Dim sParameter() As String = {"@adsl_intl", "@apac_intl", "@dial_intl", "@dpac_intl", "@tele_intl", "@dler_intl", "@csid_intl"}
    Dim tbl_name() As String = {"tbl_adsl_signup", "tbl_adsl_pack", "tbl_dialup_signup", "tbl_dialup_pack", "tbl_telephony_signup", "tbl_dealer_signup", "tbl_customer_id"}


    Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Dim adsl_intr, apac_intr, dial_intr, dpac_intr, tele_intr, dler_intr, csid_intr As Integer
        'Dim adsl_intl, apac_intl, dial_intl, dpac_intl, tele_intl, dler_intl, csid_intl As Integer
        SqlConnection_remote = New SqlConnection("server=(local);database=test2; UID=lijunguo;PWD=lijunguo")
        SqlConnection_local = New SqlConnection("server=(local);database=test; UID=lijunguo;PWD=lijunguo")
        SqlDataAdapter_remote = New SqlDataAdapter("select * from customers", SqlConnection_remote)
        SqlDataAdapter_local = New SqlDataAdapter("select * from customers", SqlConnection_remote)

 
        DataSet_remote = New DataSet
        DataSet_local = New DataSet
        'open connection first
        If Not Page.IsPostBack Then
            SqlConnection_remote.Open()
            SqlDataAdapter_remote.SelectCommand.Connection = SqlConnection_remote
            'retrieve updated tbl_customer_id from remote server
            SqlDataAdapter_remote.SelectCommand.CommandText = "select customerid, available, producttype from tbl_customer_id where available='no'"
            SqlDataAdapter_remote.Fill(DataSet_remote, "tbl_customer_id")
            'retrieve tbl_adsl_signup from remote server
            SqlDataAdapter_remote.SelectCommand.CommandText = "select * from tbl_adsl_signup order by [date] "
            SqlDataAdapter_remote.Fill(DataSet_remote, "tbl_adsl_signup")
            'retrieve tbl_adsl_pack from remote server
            SqlDataAdapter_remote.SelectCommand.CommandText = "select * from tbl_adsl_pack order by [date]"
            SqlDataAdapter_remote.Fill(DataSet_remote, "tbl_adsl_pack")
            'retrieve tbl_dialup_signup from remote server
            SqlDataAdapter_remote.SelectCommand.CommandText = "select * from tbl_dialup_signup order by [date]"
            SqlDataAdapter_remote.Fill(DataSet_remote, "tbl_dialup_signup")
            'retrieve tbl_dialup_pack from remote server
            SqlDataAdapter_remote.SelectCommand.CommandText = "select * from tbl_dialup_pack order by [date]"
            SqlDataAdapter_remote.Fill(DataSet_remote, "tbl_dialup_pack")
            'retrieve tbl_dealer_signup from remote server
            SqlDataAdapter_remote.SelectCommand.CommandText = "select * from tbl_dealer_signup order by [date]"
            SqlDataAdapter_remote.Fill(DataSet_remote, "tbl_dealer_signup")
            'retrieve tbl_telephony_signup from remote server
            SqlDataAdapter_remote.SelectCommand.CommandText = "select * from tbl_telephony_signup order by [date]"
            SqlDataAdapter_remote.Fill(DataSet_remote, "tbl_telephony_signup")

            SqlConnection_remote.Close()


            SqlConnection_local.Open()
            'SqlDataAdapter_local.SelectCommand = new
            SqlDataAdapter_local.SelectCommand.Connection = SqlConnection_local

            'create output parameter to store value return by SP.
            Do While i < 7
                outParameter = SqlDataAdapter_local.SelectCommand.CreateParameter()




                outParameter.ParameterName = sParameter(i)
                outParameter.Direction = ParameterDirection.Output
                outParameter.SqlDbType = SqlDbType.BigInt
                SqlDataAdapter_local.SelectCommand.CommandText = spCommand(i)
                SqlDataAdapter_local.SelectCommand.CommandType = CommandType.StoredProcedure
                SqlDataAdapter_local.SelectCommand.Parameters.Clear()
                SqlDataAdapter_local.SelectCommand.Parameters.Add(outParameter)
                'SqlDataAdapter_local.SelectCommand.Parameters.Add(New SqlParameter("@adsl_intl", SqlDbType.BigInt))
                SqlDataAdapter_local.SelectCommand.ExecuteNonQuery()
                'SqlDataAdapter_local.Fill(DataSet_local, "tbl_adsl_signup")

                If (IsDBNull(SqlDataAdapter_local.SelectCommand.Parameters(sParameter(i)).Value)) Then

                    local_int(i) = 0
                Else
                    local_int(i) = SqlDataAdapter_local.SelectCommand.Parameters(sParameter(i)).Value

                End If
                i += 1
            Loop


            SqlConnection_local.Close()
            i = 0
            'reset i to 0
            Do While i < 6 ' not include total users
                remote_int(i) = DataSet_remote.Tables(tbl_name(i)).Rows.Count
                'DataSet_remote.Tables(tbl_name(i)).Rows().
                If remote_int(i) > local_int(i) Then
                    Dim j As Integer 'j as no. of new users
                    Dim insert_cmd As Integer
                    j = remote_int(i) - local_int(i)
                    Select Case i
                        Case 0
                            Label1.Text = "You have " & j.ToString() & " new ADSL customers; "
                            Try
                                SqlDataAdapter_local.SelectCommand.CommandText = "select * from tbl_adsl_signup"
                                SqlDataAdapter_local.SelectCommand.CommandType = CommandType.Text
                                SqlDataAdapter_local.SelectCommand.Connection = SqlConnection_local
                                SqlConnection_local.Open()
                                SqlDataAdapter_local.Fill(DataSet_local, "tbl_adsl_signup")
                                ' Create command builder. This line automatically generates the update commands for you, so you don't
                                ' have to provide or create your own.
                               Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(SqlDataAdapter_local)
                                ' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
                                ' key & unique key information to be retrieved unless AddWithKey is specified.
                                SqlDataAdapter_local.MissingSchemaAction = MissingSchemaAction.AddWithKey
                                ' SqlDataAdapter_local.UpdateCommand.Connection() = SqlConnection_local
                                Dim myDataRow, localDataRow As DataRow
                                Dim loop_int As Integer = 0
                                 For Each myDataRow In DataSet_remote.Tables("tbl_adsl_signup").Rows
                                    If (loop_int >= local_int(i)) Then
                                        DataSet_local.Tables("tbl_adsl_signup").ImportRow(myDataRow)
                                    End If
                                    loop_int += 1
                                Next
                                'SqlConnection_local.Open()
                                ' Update Database with SqlDataAdapter
                                SqlDataAdapter_local.Update(DataSet_local.Tables("tbl_adsl_signup"))
                                 SqlConnection_local.Close()
                            Catch ex As Exception
                                Console.WriteLine(ex.ToString())

                            End Try

                        Case 1
                            Label1.Text = Label1.Text & j.ToString() & " new ADSL TELEPHONY PACKAGE customers; "
                        Case 2
                            Label1.Text = Label1.Text & j.ToString() & " new DIALUP customers; "
                        Case 3
                            Label1.Text = Label1.Text & j.ToString() & " new DIALUP TELEPHONY PACKAGE customers; "
                        Case 4
                            Label1.Text = Label1.Text & j.ToString() & " new TELEPHONY customers; "
                        Case 5
                            Label1.Text = Label1.Text & j.ToString() & " new DEALERS; "
                            'Case 6
                            'Label6.Text = "You have " & j & " new ADSL customers"
                    End Select
                End If
                i += 1

            Loop
        End If
    End Sub


End Class
0
Comment
Question by:lijunguo
  • 3
6 Comments
 
LVL 8

Expert Comment

by:nishikanth
Comment Utility
What are u trying to do....?

 the code will not be called evertime the page is submitted, since the code for update is in the if condition Not iSPOSTBACK. Only the first time when the page is loaded, the code will be executed..


also for some info abt update....

check this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.asp
0
 
LVL 11

Author Comment

by:lijunguo
Comment Utility
Hi nishikanth,

I'm just trying to update the table with dataset, the problem is not the if condition, I know it's only running once, the problem is the update is not working.

I am trying to update the tables from remote DB to a local DB. I could not use the SQL server replication because of lacking enough priviledge. So I need to do it automatically, not use sql to do it manunally.
0
 
LVL 11

Author Comment

by:lijunguo
Comment Utility
I try to compare the number of records on both tables which have the same schema, but on different servers, if the number of records in remote server are greater than those of local server, then insert the new records into the local server. That's what I try to do.
0
 
LVL 10

Accepted Solution

by:
jnhorst earned 150 total points
Comment Utility
When you call the Update method of a data adapter, if there are new records in the data table you pass in the arg, an Insert command is called, but I do not see the Insert command defined anywhere.  Here is what I reccommend based on the info you have provided so far:

Begin by opening the Server Explorer.  In this pane you can add connections to databases.  Add one for the remote machine with your credentials and one for the local machine.

Then add a dataset to your project by dropping down the Project menu and selecting "Add New Item".  An icon for Dataset should appear in one of the first three lines of icons in the dialog.  Name the file something that makes sense (maybe ADSL.xsd).  You will have a yellowish blank designer to work with.  If you have not already created a connection to your local database in the Server Explorer, do this.  You will have a nodes under that connection for things like your stored procedures and tables.  Expand the tables in the database and drag the ones to which you will be adding rows onto the designer.  You will now have the data tables you need.  Since the schemas are the same between your local copy and the remote one, you do need to worry about the fact that you got the schema from the local db.  Save and close this and return to your web page in designer mode.

Now open the toolbar(s) and select the Data tab.  Double click the Dataset tool.  You will get a dialog with a dropdown of the datasets in your project, including the one you just created.  Select that one, uncheck "Create cached strongly typed dataset" and click OK.  You will see an icon in a tray at the bottom of your page designer.  Right click the icon, select properties and rename this dsLocal.  Do exactly the same thing again and rename it dsRemote.

Now return to the Data tab on the toolbars and double click SqlDataAdapter.  You get a dialog asking you to define a connection.  Since you have added one for both remote and local databases, these should be available in the dropdown.  Select the remote one.  You will now be asked if you want to enter SQL commands manually, create stored procedures or use existing stored procedures.  Since you probably do not have permissions to create stored procedures on the remote box, choose to write these manually.  Enter the Select statement for the first table in question.  Since you are not inserting records into the remote database, you do not need an Insert command here.  Work through the data adapter wizard to completion and name it something like "daRemote_TableName" (change TableName accordingly).  Repeat this for all of your remote tables (one data adapter for each).

Then do this again for your local tables.  But now for each local data adapter, define both the select command and the insert command, setting in-line parameters for each of the values you need to insert into the local table.  As you complete the data adapter wizard, the necessary table mappings are created to map the columns in the tables to the parameters the insert command will expect (this is where I think your current method is failing).

When you have done all of this, you will see in the Windows generated code most of the stuff you have done manually.  All you will have to do is call the Fill method of each pair of data adapters (remote and local), compare the data tables in the dsRemote and dsLocal datasets, and add the new rows from the remote tables to the local ones.  Then you call the Update method of the local adapters.  Assuming the data adapter wizards complete successfully (they will inform you if they were not able to generate the table mappings for you), this should work.

Good luck!

John
0
 
LVL 11

Author Comment

by:lijunguo
Comment Utility
Hi jnhorst ,

thank you for your post, I tried your way, it's working. But I have to provide so many parameters, that's what I don't like. It's not generated automatically. Because I use dynamic query.

And I found the problem is the following method, after I changed to addrow() method, and it's working. But I don't know why ImportRow() method failed. From the debug, I could see the recoreds have been added to the Dataset, but not updating to the DB.

DataSet_local.Tables("tbl_adsl_signup").ImportRow(myDataRow)

I used the following line, so it's generating all methods automatically. That's why there is no insert method in my code.

' Create command builder. This line automatically generates the update commands for you, so you don't  have to provide or create your own.


                               Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(SqlDataAdapter_local)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

13 Experts available now in Live!

Get 1:1 Help Now