PROBLEM:
____________________________________________________________
Im trying to create an update on an oracle database and Im having some real trouble with it. If anyone has experience creating transactions and
stored procedures from .net or asp and can help out I can use the assistance. My code is returning the following error and I dont know where its
going wrong....
_____________________________________________________________
ERROR:
_________________________________________________________
Transaction failed, exception: Input string was not in a correct format.
detailed error: System.FormatException: Input string was not in a correct format. at System.Data.Common.DbDataAdapter.Update(DataRow[]
dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataTable dataTable) at
enforce.GRT_mod.pushChangesFromDatasetToDatabase() in c:\inetpub\wwwroot\enforce\root\main\GRT_mod.aspx.vb:line
338+++++++++UPDATE D_FACILITY SET y = :newLatitude, x= :newLongitude, FACILITY_NAME = : newFacNameWHERE
D_FACILITY.CAP_FACILITY_ID=146
___________________________________________________________
CODE:
________________________________________________________________
Private Sub pushChangesFromDatasetToDatabase()
' use dataadapter to push changes
objDataAdapter = New System.Data.OracleClient.OracleDataAdapter
connString = "cant say"
objConnection = New System.Data.OracleClient.OracleConnection(connString)
objConnection.Open()
transaction = objConnection.BeginTransaction
Dim param As System.Data.OracleClient.OracleParameter
If myID = "0" Then
' ' insert code
' strSql = "INSERT INTO D_FACILITY (y, x, FACILITY_NAME) " & _
'"VALUES (:Latitude, :Longitude, :FACILITY_NAME)"
' Dim insertCmd As New System.Data.OracleClient.OracleCommand(strSql, objConnection)
' insertCmd.CommandType = CommandType.StoredProcedure
' param = insertCmd.Parameters.Add("@y", System.Data.OracleClient.OracleType.Float)
' param.Direction = ParameterDirection.Input
' param.SourceColumn = "Latitude"
' param.SourceVersion = DataRowVersion.Current
' param = insertCmd.Parameters.Add("@x", System.Data.OracleClient.OracleType.Float)
' param.Direction = ParameterDirection.Input
' param.SourceColumn = "Longitude"
' param.SourceVersion = DataRowVersion.Current
' param = insertCmd.Parameters.Add("@FACILITY_NAME", System.Data.OracleClient.OracleType.NVarChar, 255)
' param.Direction = ParameterDirection.Input
' param.SourceColumn = "Description"
' param.SourceVersion = DataRowVersion.Current
' objDataAdapter.InsertCommand = insertCmd
' objDataAdapter.InsertCommand.Transaction = transaction
Else
' edit code
strSql = "UPDATE D_FACILITY SET y = :newLatitude, x= :newLongitude, FACILITY_NAME = : newFacName" & _
"WHERE D_FACILITY.CAP_FACILITY_ID=" & myID
Dim updateCmd As New System.Data.OracleClient.OracleCommand(strSql, objConnection)
updateCmd.CommandType = CommandType.StoredProcedure
param = updateCmd.Parameters.Add("newLat", System.Data.OracleClient.OracleType.Float)
param.Direction = ParameterDirection.Input
param.SourceColumn = "y"
param.SourceVersion = DataRowVersion.Current
param = updateCmd.Parameters.Add("newLong", System.Data.OracleClient.OracleType.Float)
param.Direction = ParameterDirection.Input
param.SourceColumn = "x"
param.SourceVersion = DataRowVersion.Current
param = updateCmd.Parameters.Add("newFacName", System.Data.OracleClient.OracleType.Float)
param.Direction = ParameterDirection.Input
param.SourceColumn = "FACILITY_NAME"
param.SourceVersion = DataRowVersion.Original
objDataAdapter.UpdateCommand = updateCmd
objDataAdapter.UpdateCommand.Transaction = transaction
'' delete code
'strSql = "DELETE D_FACILITY.* FROM D_FACILITY WHERE D_FACILITY.CAP_FACILITY_ID=" & myID & ";"
'Dim deleteCmd As New System.Data.SqlClient.SqlCommand(strSql, objConnection)
'objDataAdapter.DeleteCommand = deleteCmd
'objDataAdapter.DeleteCommand.Transaction = transaction
End If
debugDataGridID.DataSource = myDataSet.Tables("FACtable")
debugDataGridID.DataBind()
' now push data from dataset to database using dataadapter
Try
Dim updTable As DataTable = myDataSet.Tables("FACtable")
objDataAdapter.Update(updTable)
transaction.Commit()
lblDebugId.Text = "<b>Transaction succeeded</b>"
Page.RegisterStartupScript("myOnloadScript", "<script language='javascript'>runOnFinalLoadIfTransactionOk();</script>")
Catch ex As Exception
transaction.Rollback()
lblDebugId.Text = "<p><b>Transaction failed, exception:</b> " & vbCrLf & ex.Message & "<p><b>detailed error:</b> " & ex.ToString &
"+++++++++" & strSql
End Try
objDataAdapter.Dispose()
objConnection.Close()
objConnection.Dispose()
End Sub
_________________________________________________________________________