hello, pls help look through this code its not inserting the record in dataset into the another database.
i want to insert the row in the dataset into another table in another database.
pls help. it urgent i submit this application. when it runs it gives: this OdbcTransaction has completed,it no longer usable.
thx
toyin
here is the code
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 from Command
Dim oDA As New OdbcDataAdapter(oSCmd)
' create auto-generated INSERT command with CommandBuilder
Dim oCB As New OdbcCommandBuilder(oDA)
' get InsertCommand from 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
Sub GetConfigurationValues()
' get values from application configuration file
gConnStr = ConfigurationSettings.AppSettings("TargetOdbcConne ctionString")
gTableNames = ConfigurationSettings.AppSettings("SourceTableList ")
Dim sSourceUrl As String = ConfigurationSettings.AppSettings("SourceWebServic eURL")
Dim sWebSiteIP As String = ConfigurationSettings.AppSettings("SourceDatabaseA ddresses")
' 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.Lo cation & ".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
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="SourceWebServiceURL"
value="http://localhost/SynDatabase/GetNewRecord.asmx"/>
<add key="SourceDatabaseAddresses"
value="PROVIDER={MSDASQL.1};DSN=myodbc;SERVER =localhost;DATABASE =db1;UID =root;PWD = webserver;port=3306" />
<add key="TargetOdbcConnectionString"
value="PROVIDER=MSDASQL.1;DSN=myodbc1;server=local host;database=db2;uid=root;pwd=webserver" />
<add key="SourceTableList"
value="table2" />
<add key="LogFileFullPathAndName"
value="c:\inetpub\wwwroot\synchiis.inc" />
</appSettings>
</configuration>