STORED PROCEDURE in .NET please help...
 

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
_________________________________________________________________________