Stored Procedure... .net Please help

[dbenoit64]

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 = "Transaction succeeded"
Page.RegisterStartupScript("myOnloadScript", "")
Catch ex As Exception
transaction.Rollback()
lblDebugId.Text = "

Transaction failed, exception: " & vbCrLf & ex.Message & "

detailed error: " & ex.ToString &

"+++++++++" & strSql
End Try
objDataAdapter.Dispose()
objConnection.Close()
objConnection.Dispose()
End Sub
_________________________________________________________________________

[DaPi]

Space in the wrong place here I think:
= : newFacName" & _
"WHERE

should be ?
= :newFacName " & _
"WHERE

(Any reason why you're not using a bind variable for myID, or will that come later?)

Your delete looks wrong too, you don't need anything between DELETE and FROM. I'd guess you don't need ";" at the end either (not sure of that, no speeka da lingo).

[dbenoit64]

Thanks, yeah that was defininately 1 error.
I have a value for myId it comes from another page. in this case its 146.
After fixing that error i still get the same problem happening:

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 = : newFacName WHERE D_FACILITY.CAP_FACILITY_ID=207

[DaPi]

You've done it again with a space:

FACILITY_NAME = : newFacName

The MUSTN'T be a space after any of the colons :

[bhavesh_09]

hey i also face same problems before some months but after that i start to using spwrapper ..its very useful. try this its free and effective..