Problem Passing a Double amount to Oracle via SP

[MrPeanut]

Hello Tek-Tippers!

I am having a bit of a problem. I have a table on our server that handles some customer data. The data field invamt will store the dollar amount for our customer data.
The field is defined as NUMBER(15,2).

This is the code I am using to pass in the value to the stored procedure.

Public Function InsertCustData(ByVal InvAmt As Double)
Try
   'Set up our database and dbcommand
   Dim dbInsert As Database = DatabaseFactory.CreateDatabase()
   Dim dbcInsert As OracleClient.OracleCommand = dbInsert.GetStoredProcCommand("sendCustdata")

   'Pass in the parameter
   dbInsert.AddInParameter(dbcInsert, "invamt", OracleType.Number, InvAmt)

   'Execute the StoredProcedure
   dbInsert.ExecuteNonQuery(dbcInsert)

Catch ex As Exception
   Throw New ApplicationException(ex.Message)
End Try

End Function

The error I receive is the following:

Cannot bind type System.Double as Blob.

Why would it do this? I mapped the datatype as recommended by the MSDN site. Any ideas on how to get around this or if I'm doing something wrong? Thank you!

-Diran

 

[MrPeanut]

Oh one thing to note:

This is the code for my Stored Procedure on the Oracle side:

CREATE OR RELPACE PROCEDURE sendCustData (invamt in number)
IS
BEGIN
   INSERT INTO customerData
               (invamt)
        VALUES (invamt);
END;

 

[chrissie1]

Public Function InsertCustData(ByVal InvAmt As Double)


I think you will need decimal here. Since double is a floating point and number isn't. But I could be wrong.

Christiaan Baes
Belgium

"My old site" - Me

 

[MrPeanut]

I went ahead and changed the code to the following:

Public Function InsertCustData(ByVal InvAmt As Decimal)
Try
   'Set up our database and dbcommand
   Dim dbInsert As Database = DatabaseFactory.CreateDatabase()
   Dim dbcInsert As OracleClient.OracleCommand = dbInsert.GetStoredProcCommand("sendCustdata")

   'Pass in the parameter
   dbInsert.AddInParameter(dbcInsert, "invamt", OracleType.Number, InvAmt)

   'Execute the StoredProcedure
   dbInsert.ExecuteNonQuery(dbcInsert)

Catch ex As Exception
   Throw New ApplicationException(ex.Message)
End Try

End Function

Now it's saying as the error:

Cannot bind type System.Decimal as Blob.

This is really weird to get this error message.

 

[chrissie1]

are you sure invamt is a number field?

does the sp work in toad?

Christiaan Baes
Belgium

"My old site" - Me

 

[MrPeanut]

In toad I ran the following command:

exec sendCustData(123.54);

It loads in the data successfully. It's very confusing to me why this isn't working.

 

[ThatRickGuy]

I'm not familiar with the DatabaseFactory, but you may want to ensure you have BindByName enabled.

    Public Overloads Overrides Function ExecuteSP(ByVal Schema As String, ByVal TableName As String, ByVal CommandText As String, ByRef OracleParameters() As OracleParameter) As DataTable
      'GetConnection handles the connection string
      Dim conn As OracleConnection = GetConnection()

      Dim cmd As New Oracle.DataAccess.Client.OracleCommand
      Dim dtResults As New DataTable(TableName, Schema)

      cmd.Connection = conn
      cmd.BindByName = True
      cmd.CommandType = CommandType.StoredProcedure
      cmd.CommandText = CommandText

      For Each Parm As OracleParameter In OracleParameters
        cmd.Parameters.Add(Parm)
      Next

      Dim da As New OracleDataAdapter(cmd)
      Try
        da.Fill(dtResults)
      Catch exc As Oracle.DataAccess.Client.OracleException
        '
      End Try

      cmd.Dispose()

      Return dtResults
    End Function

Hope it helps.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.