asked on

Dataset update not updating DB.

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

nishikanth

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

lijunguo

ASKER

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.

lijunguo

ASKER

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.

ASKER CERTIFIED SOLUTION

jnhorst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lijunguo

ASKER

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)