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.
