ORA-01008: not all variables bound using Oracle Adaptor 1

[LokiDba]

Hi Everyone,

I'm getting the above error message for the following code:

DataSet lds_Data = new DataSet();

OracleConnection lo_Conn = new OracleConnection("Data
Source=xxxx; User ID=xxxx; Password=xxxx");

string ls_SQL = "";

// Build the SQL Statement

ls_SQL = "SELECT * FROM CAT WHERE TABLE_TYPE
= v_TType";

try
{

OracleDataAdapter lo_Adapt = new OracleDataAdapter(
ls_SQL, lo_Conn );

lo_Adapt.SelectCommand.Parameters.Add( "v_TType",
OracleType.VarChar, 255, "TABLE" );

// Fill the dataset

lo_Adapt.Fill( lds_Data );
}
catch( Exception x )
{
Console.WriteLine( x.Message );
}

Could anyone tell me what I'm doing wrong.

Thanks in advance

Andy (LokiDBA)

 

[cappmgr]

Not sure what your doing wrong. I have never written a Command.Text I always use sprocs and after all this typing I am glad I do. The code below worked for me. I am not sure of what all the parameters mean for myCommand.Parameters.Add.

Object my_DBNull;
my_DBNull = Convert.DBNull;
string inRegionCode = "E";
string myConnString = "User ID=xxxx;Password=xxxx;Data Source=xxxx;"; 
OracleConnection myConnection = new OracleConnection(myConnString);
OracleCommand myCommand = new OracleCommand();
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.Add(new OracleParameter("INREGIONCODE", OracleType.Char, 0, ParameterDirection.Input, true,0,0,"",DataRowVersion.Default, my_DBNull) );         
myCommand.CommandText = "SELECT REGION_CODE, COUNTY_NAME FROM county WHERE REGION_CODE = :INREGIONCODE";
myCommand.Parameters[0].Value = inRegionCode;
DataSet ds = new DataSet();
OracleDataAdapter MyDA = new OracleDataAdapter(myCommand);
try 
{
	myConnection.Open();
	MyDA.Fill(ds);
	return ds;
}......

Here is the link I read.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/manprooracperf.asp

If you can make a Command.Text work with less typing please post it.
Marty

 

[obislavu]

I agree with cappmgr about stored procedures and here is an example.
I found that using sprocs is the solid method to accomplish complex tasks.
There are other ways but are less performant.
Move the Select * from the code into a stored procedure as in this example and let know the results.
public InventoryItem Get()
{
int vReturn=0;
SqlDataAdapter vSqlDataAdapter=new SqlDataAdapter();
if (m_.SQLConnection.State==System.Data.ConnectionState.Closed) m_.SQLConnection.Open();
vSqlDataAdapter.SelectCommand = new SqlCommand("sp_GetInventoryItem",m_.SQLConnection);
vSqlDataAdapter.SelectCommand.CommandType =CommandType.StoredProcedure;
SqlParameter vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
vSqlParameter.Direction = ParameterDirection.ReturnValue;
vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ItemID", m_ExceptionID);
DataSet vDataSet = new DataSet();
vSqlDataAdapter.Fill(vDataSet);
vReturn= (Int32) vSqlDataAdapter.SelectCommand.Parameters["@ReturnValue"].Value;
if (vReturn>0)
{
// iterate here through the vDataSet.Tables[0].Rows;

}

Here is the expected sproc sp_GetInventoryItem expects 1 string parameter as input and it also can call other sprocs which are unknown for your C# program..


CREATE PROCEDURE dbo.sp_GetInventoryItem
(
@ItemID varchar(50)
)
AS
DECLARE @PreFix char(4)
DECLARE @Suffix varchar(50)
DECLARE @ID int
DECLARE @TableName varchar(50)
DECLARE @WhereClause varchar(255)
DECLARE @Cmd varchar(1024)
DECLARE @Return int


Set @PreFix =Left(@ItemID,4)
Set @Suffix =Right(@ItemID,len(@ItemID)-4)
Set @ExceptionID=convert(int,@Suffix)

IF (@PreFix='PROV') Set @TableName='_tEventWork'
IF (@PreFix='LEAS') Set @TableName='_tEventLeasingWork'


Set @WhereClause='WHERE ExceptionID=' + CAST(@ID as varchar(50))
Set @Cmd='SELECT * FROM ' + @TableName + ' ' + @WhereClause
EXEC(@Cmd)
RETURN @@RowCOunt
GO
-obislavu-

 

[obislavu]

I agree with cappmgr about stored procedures and here is an example.
I found that using sprocs is the solid method to accomplish complex tasks.
There are other ways but are less performant.
Move the Select * from the code into a stored procedure as in this example and let know the results.

public InventoryItem Get()
{
	int vReturn=0;
	SqlDataAdapter vSqlDataAdapter=new SqlDataAdapter();
	if (m_.SQLConnection.State==System.Data.ConnectionState.Closed) m_.SQLConnection.Open();
	vSqlDataAdapter.SelectCommand = new SqlCommand("sp_GetInventoryItem",m_.SQLConnection);
	vSqlDataAdapter.SelectCommand.CommandType =CommandType.StoredProcedure;
	SqlParameter vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
	vSqlParameter.Direction = ParameterDirection.ReturnValue;
	vSqlParameter= vSqlDataAdapter.SelectCommand.Parameters.Add("@ItemID", m_ExceptionID);
	DataSet vDataSet = new DataSet();
	vSqlDataAdapter.Fill(vDataSet);
	vReturn= (Int32) vSqlDataAdapter.SelectCommand.Parameters["@ReturnValue"].Value;
	if (vReturn>0) 
	{
		// iterate here through the vDataSet.Tables[0].Rows;
		
	}

Here is the expected sproc sp_GetInventoryItem expects 1 string parameter as input and it also can call other sprocs which are unknown for your C# program..

CREATE PROCEDURE dbo.sp_GetInventoryItem
(
@ItemID varchar(50)
)
AS
DECLARE @PreFix char(4)
DECLARE @Suffix varchar(50)
DECLARE @ID int
DECLARE @TableName varchar(50)
DECLARE @WhereClause varchar(255)
DECLARE @Cmd varchar(1024)
DECLARE @Return int


Set @PreFix =Left(@ItemID,4)
Set @Suffix =Right(@ItemID,len(@ItemID)-4)
Set @ExceptionID=convert(int,@Suffix)

IF (@PreFix='PROV') Set @TableName='_tEventWork'
IF (@PreFix='LEAS') Set @TableName='_tEventLeasingWork'


Set @WhereClause='WHERE ExceptionID=' + CAST(@ID as varchar(50)) 
Set @Cmd='SELECT * FROM ' + @TableName + ' ' + @WhereClause
EXEC(@Cmd)
RETURN @@RowCOunt
GO

-obislavu-

 

[LokiDba]

Thanks cappmgr and everyone else, code worked with a few changes, need to add : at the start of the parameter and it all worked fine.

Thanks again

Andy