hi, pls can someone help me out. its very urgent i get the solution today. i
have a webservice that i use to retrieve new record from a database, also
there is a client application (window form) that calls this service which
returns the record in a dataset. now i need to insert the record in this
dataset into another database table on a different server.how do i do these.i
've tried so many things and also implement a suggestion on a thread about
inserting from one db into another but its not working. am using MySql5.1
community edition,IIS 5.o and Visual studio.Net2003 to develop and test. pls
someone help me. i need a simple code to do this.

Your questiona bit confused me, but based on my understanding, you have
retrieved data into dataset from X database and now want to save the same in
Y database.

This is possible if you have right dataAdaptors for each database(X & Y)
eg.
dataAdaptor1 = new ("Select col1 , col2  from TableXDatabase1")
dataAdaptor2 = new ("Select col1 , col2  from TableYDatabase2")

dataAdaptor1.Fill(ds)
// Make Necessary Changes and
dataAdaptor2.Update(ds)

Regards
JIGNESH

Show quoteHide quote

hello,
thx for ur reply but it still can solve my problem.i  have a record already
in a dataset (ds) i want to push the record in this ds into another  table
(y) in database(y) without making any changes to it .
thx

Show quoteHide quote

Toyin,

Once you have the record in a datatable within a dataset:

Open a connection to database y
Use a command object to execute an SQL Insert statement
Insert a new row into table y in database y, using the data from the record
in the datatable within the dataset

Kerry Moorman


Show quoteHide quote

hello Kerry,
thx for your response. but i need the code for the inser statement. is this
correct
dim comm AS New OdbcCommand("Insert Into table2 Select * from" + oDS.Tables(0)
with the above code i still get an error that operator + is not defined for
string and system.data.dataTable.

pls send the insert statement to me.

thx
Toyin

Show quoteHide quote

Toyin,

Here is an example of inserting a new row into a table where the data for
the new row comes from a row in a datatable in a dataset:

    Private Sub InsertRow(ByVal ds As DataSet)

        Dim cn As New SqlConnection("Data Source=(local)\SQLExpress;Initial
Catalog=CollegeRecords;User ID=sa;Password=xxxxxxx")

        Dim cmd As New SqlCommand

        cmd.CommandText = "Insert Into Courses (CourseID, Title, Period,
Room) Values (@CourseID, @Title, @Period, @Room)"

        cmd.Parameters.AddWithValue("@CourseID",
ds.Tables(0).Rows(0)("CourseID"))
        cmd.Parameters.AddWithValue("@Title", ds.Tables(0).Rows(0)("Title"))
        cmd.Parameters.AddWithValue("@Period", ds.Tables(0).Rows(0)("Period"))
        cmd.Parameters.AddWithValue("@Room", ds.Tables(0).Rows(0)("Room"))

        cn.Open()

        cmd.Connection = cn

        cmd.ExecuteNonQuery()

        cn.Close()

        MsgBox("Inserted")

    End Sub

Kerry Moorman


Show quoteHide quote

hello Kerry,
thx for ur response. i tried the example you sent the only response was
"true" and when i check the database table to see if it has insert the new
row it, i found that no row has been inserted. also my IDE does not have
AddWithValue. here is the sample i use to test.

<WebMethod()> Public Function TransferData() As DataSet

        Dim sConn As String = "PROVIDER={MSDASQL.1};DSN=myodbc1;SERVER
=localhost;DATABASE =db1;UID =root;PWD = webserver;port=3306"
        Dim sComm As String = "select * from table2 where ID = (select
MAX(ID) AS ID from table2)"


        Dim conn As New OdbcConnection(sConn)

        'Dim comm As New OdbcCommand(sComm, conn)

        Dim ds As New DataSet
        Dim da As New OdbcDataAdapter

        conn.Open()
        'comm.ExecuteNonQuery()

        da.SelectCommand = New OdbcCommand(sComm, conn)
        da.Fill(ds)
        conn.Close()

        Dim conn1 As New
OdbcConnection("PROVIDER={MSDASQL.1};DSN=myodbc1;SERVER =localhost;DATABASE
=db2;UID =root;PWD = webserver;port=3306")

        Dim cmd As New OdbcCommand
        cmd.CommandText = "INSERT INTO table1 (ID,Fname,Lname,Sex,DOB)
VALUES (@ID,@Fname,@Lname,@Sex,@DOB)"
        cmd.Parameters.Add("@ID", ds.Tables(0).Rows(0)("ID"))
        cmd.Parameters.Add("@Fname", ds.Tables(0).Rows(0)("Fname"))
        cmd.Parameters.Add("@Lname", ds.Tables(0).Rows(0)("Lname"))
        cmd.Parameters.Add("@Sex", ds.Tables(0).Rows(0)("Sex"))
        cmd.Parameters.Add("@DOB", ds.Tables(0).Rows(0)("DOB"))
        conn1.Open()
        cmd.Connection = conn1
        conn1.Close()

    End Function

if i use conn.ChangeDataBAse(...) it gives me error that the buffer is
overload and the web application am trying to access is not available.

maybe i should send a copy of my code where i implement the client app (a
window form)


Dim gConnStr As String = String.Empty
    Dim gSourceUrls As String = String.Empty
    Dim gTableNames As String = String.Empty
    Dim gSourceURLArray() As String
    Dim gSiteIPArray() As String
    Dim gTableArray() As String
    Dim gbCancel As Boolean = False
    Dim gbAutoStart As Boolean = False
    Dim gLogFile As StreamWriter

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) _
               Handles MyBase.Load
        ' get list of command line parameters
        Dim sParams As String = Microsoft.VisualBasic.Command()
        Me.Show()   ' show the form
        If sParams.IndexOf("/a") >= 0 _
          Or sParams.IndexOf("-a") >= 0 _
          Or sParams.IndexOf("\a") >= 0 Then
            ' set global variable indicating process
            ' will run automatically
            gbAutoStart = True
            btnRunNow_Click(Nothing, Nothing)
        End If
    End Sub


    Private Sub btnRunNow_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnRunNow.Click
        btnRunNow.Enabled = False
        btnExit.Enabled = False
        btnCancel.Enabled = True
        Try
            gbCancel = False
            StatusBox.Text = ""
            OpenLogFile()
            GetConfigurationValues()
            Application.DoEvents()
            If gbCancel = True Then
                Throw New Exception("Process canceled by user.")
            End If
            ' main routine to fetch rows and update database
            ProcessAllSynchOperations()
            WriteStatus("Completed at " & DateTime.Now.ToString("yyyy-MM-dd
hh:mm:ss"))
            CloseLogFile()
            btnCancel.Enabled = False
            btnExit.Enabled = True
            btnRunNow.Enabled = True
        Catch errMain As Exception
            WriteStatus(">> " & errMain.Message)
            CloseLogFile()
            btnCancel.Enabled = False
            btnExit.Enabled = True
            btnRunNow.Enabled = True
        End Try
        If gbAutoStart Then End
    End Sub
    Sub ProcessAllSynchOperations()
        Dim sSourceURL, sWebSiteIP As String
        Dim dLastUpdate As DateTime
        Dim iLoop As Integer
        ' iterate through all the URLs in the config file
        For iLoop = 0 To gSourceURLArray.Length - 1
            ' get values for this source site
            sSourceURL = gSourceURLArray(iLoop).Trim()
            WriteStatus("Processing " & sSourceURL)
            sWebSiteIP = gSiteIPArray(iLoop).Trim()
            WriteStatus("Row key value is " & sWebSiteIP)
            dLastUpdate = GetLastDateLoaded(sWebSiteIP)
            Application.DoEvents()
            ' call routine to fetch the DataSet from the remote Web Service
            Dim oDS As DataSet = FetchTables(sSourceURL, sWebSiteIP,
gTableNames, dLastUpdate)
            ' see if there were any errors with the Web Service
            If oDS Is Nothing Then
                Throw New Exception("No DataSet returned from Web Service")
            End If
            If oDS.Tables(0).TableName = "Errors" Then
                Throw New Exception("Error reported by Web Service: " &
oDS.Tables(0).Rows(0)(0))
            End If
            Dim oConn As OdbcConnection
            Dim oTrans As OdbcTransaction

            Try
                ' create Connection, open it and start a transaction
                oConn = New OdbcConnection(gConnStr)
                oConn.Open()
                oTrans = oConn.BeginTransaction()
                ' iterate through all the tables in the list
                Dim sTableName As String
                For Each sTableName In gTableArray
                    Application.DoEvents()
                    If gbCancel = True Then
                        Throw New Exception("Process canceled by user.")
                    End If
                    ' check if table is in DataSet - might not be if there
were no new rows
                    If oDS.Tables(sTableName) Is Nothing Then
                        WriteStatus(">> WARNING: no rows received for table
'" & sTableName & "'")
                    Else
                        ' see how many rows are in this table
                        Dim iRows As Integer =
oDS.Tables(sTableName).Rows.Count
                        WriteStatus("Received " & iRows.ToString() & " rows.")
                        If iRows > 0 Then
                            WriteStatus("Updating database...")
                            Application.DoEvents()
                            Try
                                ' create new Command for SelectCommand
within current transaction
                                Dim oSCmd As New OdbcCommand("SELECT * FROM
" & sTableName, oConn, oTrans)

                                ' create DataAdapter using that command
                                Dim oDA As New OdbcDataAdapter(oSCmd)

                                ' create auto-generated INSERT command with
CommandBuilder
                                Dim oCB As New OdbcCommandBuilder(oDA)

                                ' get the InsertCommand Command instance
from the commandbuilder
                                Dim oICmd As OdbcCommand =
oCB.GetInsertCommand()

                                ' attach the current transaction to the
InsertCommand
                                oICmd.Transaction = oTrans

                                ' specify this as the InsertCommand of the
DataAdapter
                                oDA.InsertCommand = oICmd


                                ' update the database table
                                Dim iCount As Integer = oDA.Update(oDS,
sTableName)
                                WriteStatus("Added " & iCount.ToString() & "
rows to table '" & sTableName & "'")
                                Application.DoEvents()
                            Catch e As Exception
                                oTrans.Rollback()
                                Throw New Exception("Error updating target
table " & sTableName & " - " & e.Message)
                            End Try
                        End If
                    End If
                Next
                ' all OK so commit all of the updates
                oTrans.Commit()
                WriteStatus("Transaction committed" & vbCrLf)
            Catch e As Exception
                ' error encountered so roll back all the updates
                oTrans.Rollback()

                Throw New Exception("Transaction failed to complete - " &
e.Message)
            Finally
                oConn.Close()
            End Try
            ' update last synchronization date file for this site
            UpdateLastDateLoaded(sWebSiteIP)
        Next
    End Sub

    Function FetchTables(ByVal sSourceURL As String, ByVal sWebSiteIP As
String, _
                         ByVal sTableNames As String, ByVal dLastUpdate As
DateTime) As DataSet
        ' generate new instance of proxy and specify source URL
        Dim oWS As New localhost2.GetNewRecord

        oWS.Url = sSourceURL
        WriteStatus("Accessing Web Service...")
        Application.DoEvents()
        If gbCancel = True Then
            Throw New Exception("Process canceled by user.")
        End If
        ' call Web Service method to get DataSet
        Return oWS.GetNewRecord(sWebSiteIP, sTableNames, dLastUpdate)
    End Function


    Function GetLastDateLoaded(ByVal sWebSiteIP As String) As Date
        ' read file for this site to get last date it was synchronized
        Dim dLast As Date
        Dim sData As String
        Dim sFile As String = sWebSiteIP.Replace(".", "_") & ".txt"
        Dim oStream As StreamReader
        If File.Exists(sFile) Then
            Try
                oStream = File.OpenText(sFile)
                sData = oStream.ReadLine()
            Catch e As Exception
                Throw New Exception("Cannot open last update file: " & sFile
& " - " & e.Message)
            Finally
                Try
                    oStream.Close()
                Catch
                End Try
            End Try
            dLast = DateTime.Parse(sData)
            If DateDiff(DateInterval.Weekday, dLast, DateTime.Now) > 6 Then
                Throw New Exception("Cannot update from more that a week ago")
            End If
            WriteStatus("Last update was " & dLast.ToString("dd MMMM yyyy
\a\t hh:mm:ss"))
            Return dLast
        Else
            UpdateLastDateLoaded(sWebSiteIP)
            Throw New Exception("Cannot find last update file, saved new
file: " & sFile)
        End If
    End Function


    Sub UpdateLastDateLoaded(ByVal sWebSiteIP As String)
        ' save file for this site to persist last date it was synchronized
        Dim oStream As StreamWriter
        Dim sFile As String = sWebSiteIP.Replace(".", "_") & ".txt"
        Try
            oStream = File.CreateText(sFile)
            oStream.WriteLine(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"))
            oStream.Flush()
            oStream.Close()
        Catch e As Exception
            Throw New Exception("Cannot create last update file: " & sFile &
e.Message)
        Finally
            Try
                oStream.Close()
            Catch
            End Try
        End Try
    End Sub


    Sub GetConfigurationValues()
        ' get values from application configuration file
        gConnStr =
ConfigurationSettings.AppSettings("TargetOdbcConnectionString")
        gTableNames = ConfigurationSettings.AppSettings("SourceTableList")
        Dim sSourceUrl As String =
ConfigurationSettings.AppSettings("SourceWebServiceURL")
        Dim sWebSiteIP As String =
ConfigurationSettings.AppSettings("SourceDatabaseAddresses")
        ' check that the required values exist
        If sSourceUrl = String.Empty Or gConnStr = String.Empty _
        Or sWebSiteIP = String.Empty Or gTableNames = String.Empty Then
            Throw New Exception("Error loading configuration settings from "
& System.Reflection.Assembly.GetExecutingAssembly.Location & ".config")
        Else
            ' display values on Form
            SourceWS.Text = "Source Web Service URLs: " & sSourceUrl
            WebSiteIP.Text = "Source Web Site IP Addresses: " & sWebSiteIP
            TargetConn.Text = "Target Database Connection String: " & gConnStr
            gSourceURLArray = sSourceUrl.Split(",")
            gSiteIPArray = sWebSiteIP.Split(",")
            If gSourceURLArray.Length <> gSiteIPArray.Length Then
                Throw New Exception("There are a different number of Web
Service URLs and IP addresses in the configuration file")
            End If
            gTableArray = gTableNames.Split(",")
            Application.DoEvents()
        End If
    End Sub


    Sub WriteStatus(ByVal sMessage As String)
        StatusBox.Text &= sMessage & vbCrLf
        Try
            gLogFile.WriteLine(sMessage)
        Catch
        End Try
    End Sub


    Sub OpenLogFile()
        Dim sFile As String =
ConfigurationSettings.AppSettings("LogFileFullPathAndName")
        Try
            gLogFile.Close()
        Catch
        End Try
        Try
            If File.Exists(sFile) Then
                gLogFile = File.AppendText(sFile)
            Else
                gLogFile = File.CreateText(sFile)
            End If
            gLogFile.WriteLine(vbCrLf &
"-----------------------------------------")
            gLogFile.WriteLine(DateTime.Now.ToString("yyyy-MM-dd \a\t\
hh:mm:ss"))
        Catch e As Exception
            Throw New Exception("Cannot open log file: " & sFile & e.Message)
            Try
                gLogFile.Close()
            Catch
            End Try
        End Try
    End Sub


    Sub CloseLogFile()
        Try
            gLogFile.Flush()
        Catch
        End Try
        Try
            gLogFile.Close()
        Catch
        End Try
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnExit.Click
        End
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCancel.Click
        gbCancel = True
    End Sub


End Class

when i run this, it recieve the rows from the web service i display display
"1 row received' and "Updating Database" after this msg it show This
transaction has been completed,its no longer usable. i guess the insert
failed before the rollback thats why its giving that error. pls help me look
at it and test to see what am doing wrong am using visaual studio .net2003
and IIS5.0
thanks for all your help
i really appreciate them. thx once again.





Show quoteHide quote

Toyin,

I don't see where you are using cmd.ExecuteNonQuery to actually execute the
Insert statement.

Also, I think AddWithValue is new with .Net 2.0.

Kerry Moorman


Show quoteHide quote

hello Kerry,

thx again for your reply. when i input
cmd.ExecuteNonQuery() and run the application i got an error. error 500
internal server error. page cannot be displayed

pls help

thx

Show quoteHide quote

Copy database schema from Access to SQL Server
vs 20028 when??
Memory Leak Caused by Using DataTable.Select
Timeout expired on Insert
"@p5 : Byte array truncation to a length of 8000."