• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 903
  • Last Modified:

Asp.net Oracle stored procedure error

Hi,

I are using ASP.net 2008 and microsoft entreprise Application block 4.0 for Accesing Oracle 10g Express Edition.
while trying to insert a new record in oracle using stored procedure i face the error ..
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PR_INSERT_TEST' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

i  have even tried for a table with one column and a stored procedure for the same , but i still get the error ...

Please Help me solve the error

Regards,
Lilib
' -----------------------------------Form  DAL layer ---------------------------
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Configuration
Imports Microsoft.Practices.EnterpriseLibrary.Data.Oracle
Imports Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation
Imports JM.BMS.DS
Public Class BIN_TYPEDAL
    Inherits BaseDAL
    Private Const JM_BMS_pr_insert_bin_type As String = "PR_INSERT_BIN_TYPE"
     
    Public Function ViewBinType(ByVal dataSet As DSBinType) As DSBinType
        dataSet.EnforceConstraints = False
        Dim db As Database = DatabaseFactory.CreateDatabase("BMS_DB_CONNECTION")
        Dim cmdInsert As DbCommand = PrepareSPParameters(db, dataSet.TBL_BIN_TYPES, JM_BMS_pr_insert_bin_type)
           Dim i As Int32 = db.UpdateDataSet(dataSet, dataSet.TBL_BIN_TYPES.TableName, cmdInsert, Nothing, Nothing, UpdateBehavior.Standard)
 
        Return dataSet
    End Function
End Class
'----------------------------------------- Base DAL code---------------------------------------
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Configuration
Imports Microsoft.Practices.EnterpriseLibrary.Data.Oracle
Imports Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation
Imports System.Web
Imports System.Web.Caching
Imports System.Collections
Imports System.Linq
 
 
 
    Public Class BaseDAL
        'Static variable 
        Private Shared paramCache As Hashtable = Hashtable.Synchronized(New Hashtable())
        ''' <summary> 
        ''' The function creates a command object with parameters based on the passed on stored procedure 
        ''' name. 
        ''' </summary> 
        ''' <param name="db">The database object where the stored procedure resides.</param> 
        ''' <param name="procedureName">Stored procedure name.</param> 
        ''' <returns>DBCommand object filled in with parameter information (except the values).</returns> 
        Protected Overridable Function PrepareSPParameters(ByVal db As Database, ByVal procedureName As String) As DbCommand
            Dim databaseCommand As DbCommand
 
            databaseCommand = db.GetStoredProcCommand(procedureName)
 
            If CheckParametersInCache(db, databaseCommand) Is Nothing Then
                ' Discover parameters required for dbcommand and store them in cache 
                SetCacheParameter(db, databaseCommand)
            Else
                Dim cachedParameters As DbParameter() = CheckParametersInCache(db, databaseCommand)
                For Each Dbpara As DbParameter In cachedParameters
                    ' create clone copy from cache 
                    Dim CloneDbpara As DbParameter = DirectCast(DirectCast(Dbpara, ICloneable).Clone(), DbParameter)
                    db.AddParameter(databaseCommand, CloneDbpara.ToString(), CloneDbpara.DbType, CloneDbpara.Direction, CloneDbpara.SourceColumn, CloneDbpara.SourceVersion, _
                    CloneDbpara.Value)
                Next
            End If
 
            Return databaseCommand
        End Function
 
 
        ''' <summary> 
        ''' The function creates a command object with parameters based on the passed on stored procedure 
        ''' name. The table also maps dataset values to stored procedure parameter values. The function 
        ''' expects stored procedure parameter names to be same as data table column names. The function 
        ''' also assumes a single row in the table and ignore any additional rows. 
        ''' </summary> 
        ''' <param name="db">The database object where the stored procedure resides.</param> 
        ''' <param name="dataStore">The table from which data needs to be mapped to the stored procedure. 
        ''' </param> 
        ''' <param name="procedureName">Stored procedure name.</param> 
        ''' <returns>DBCommand object filled in with parameter information (including the values).</returns> 
        Protected Overridable Function PrepareSPParameters(ByVal db As Database, ByVal dataStore As DataTable, ByVal procedureName As String) As DbCommand
            Dim databaseCommand As DbCommand
 
            If dataStore.Rows.Count <= 0 Then
                Throw New Exception("No rows in datatable")
            End If
 
            databaseCommand = PrepareSPParameters(db, procedureName)
 
            Return databaseCommand
        End Function
 
        ''' <summary> 
        ''' The function creates a command object with parameters based on the passed on stored procedure 
        ''' name. The table also maps dataset columns to stored procedure parameter names. The function 
        ''' expects stored procedure parameter names to be same as data table column names. Use 
        ''' this function for the UpdateDataSet() commands parameters list. 
        ''' </summary> 
        ''' <param name="db">The database object where the stored procedure resides.</param> 
        ''' <param name="dataStore">The table from which data needs to be mapped to the stored procedure. 
        ''' </param> 
        ''' <param name="procedureName">Stored procedure name.</param> 
        ''' <returns>DBCommand object filled in with parameter information (including the values).</returns> 
        Protected Overridable Function PrepareSPParameters(ByVal db As Database, ByVal dataStore As DataTable, ByVal procedureName As String, ByVal rowVersion As DataRowVersion) As DbCommand
            Dim databaseCommand As DbCommand
 
            If dataStore.Rows.Count <= 0 Then
                Throw New Exception("No rows in datatable")
            End If
 
            databaseCommand = PrepareSPParameters(db, procedureName)
 
            For Each para As DbParameter In databaseCommand.Parameters
                Dim colName As String = para.ParameterName.Replace("@", "")
                If dataStore.Columns.Contains(colName) Then
                    para.SourceColumn = colName
                    para.SourceVersion = rowVersion
                End If
            Next
 
            Return databaseCommand
        End Function
 
        ''' <summary> 
        ''' The function retrieves the parameter collection for a stored procedure. This will reduce 
        ''' the need to scatter parameter names all around in data access. 
        ''' </summary> 
        ''' <param name="db">The database object where the stored procedure resides.</param> 
        ''' <param name="databaseCommand">Command object for which parameters are needed.</param> 
        Private Function CheckParametersInCache(ByVal db As Database, ByVal databaseCommand As DbCommand) As DbParameter()
            Dim hashKey As String = databaseCommand.CommandText
            Dim cachedParameters As DbParameter() = DirectCast((HttpContext.Current.Cache(hashKey)), DbParameter())
 
            Return cachedParameters
        End Function
 
        ''' <summary> 
        ''' Add DBparameterCollection to the cache 
        ''' </summary> 
        ''' <param name="db">The database object where the stored procedure resides.</param> 
        ''' <param name="databaseCommand">Command object for which parameters are needed.</param> 
        Public Shared Sub SetCacheParameter(ByVal db As Database, ByVal databaseCommand As DbCommand)
            db.DiscoverParameters(databaseCommand)
            Dim hashKey As String = databaseCommand.CommandText
            Dim DBpara As DbParameter() = New DbParameter(databaseCommand.Parameters.Count - 1) {}
            For i As Integer = 0 To databaseCommand.Parameters.Count - 1
                ' create a clone copy 
                DBpara(i) = DirectCast(DirectCast(databaseCommand.Parameters(i), ICloneable).Clone(), DbParameter)
            Next
            paramCache(hashKey) = DBpara
            HttpContext.Current.Cache.Insert(hashKey, paramCache(hashKey))
        End Sub
    End Class
 
'------------------------------------ Web Config --------------------------
	<connectionStrings>
		<add name="BMS_DB_CONNECTION" connectionString="Data Source=172.16.129.67;User ID=jmp;Password=jmp;Unicode=True" providerName="System.Data.OracleClient"/>
	</connectionStrings>
 
'---------------------------------Stored Procedure--------------------------------
create or replace procedure pr_insert_bin_type 
(BIN_TYPE_NAME IN tbl_bin_types.bin_type_name%type , 
QUANTITY IN tbl_bin_types.quantity%type ,
REMARK IN tbl_bin_types.remark%type)
as
Begin
insert into TBL_BIN_TYPES (ID,bin_type_name,quantity,remark) 
values (s_bin_type_id.nextval,BIN_TYPE_NAME,QUANTITY,REMARK);
commit;
End;
 
'--------------------------------------  Table Structure ---------------------
 
ID                                        NOT NULL VARCHAR2(16)
BIN_TYPE_NAME                VARCHAR2(30)
QUANTITY                           NUMBER(10,2)
REMARK                             VARCHAR2(200)
AUDIT_USERID                   VARCHAR2(16)
CREATION_DATE               VARCHAR2(15)
 
sequence create on ID

Open in new window

error.JPG
0
Lilib9
Asked:
Lilib9
  • 2
1 Solution
 
CodeCruiserCommented:
Something appears to be wrong in your PrepareSPParameters(dbname, procedurename) method.
0
 
Lilib9Author Commented:
Hi  Nasir,

Thanks for going through the code and helping out with a suggestion.
The fact is that i am new to 3 -tier Architecture in ASP.Net (VB) using  microsoft entreprise Application block 4.0.

If possible, can you provide me with  the code to interact with Dataset, Data Access layer(DAL),Business Access Layer(BAL) and Businnes Facade layer.

Any kind of help would be grateful.

Thanks and Regards,
Lilib

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now