Click here to Skip to main content
16,020,459 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
hi this is stored procedure:

SQL
DELIMITER $$

CREATE DEFINER=`gps`@`%` PROCEDURE `SpAssetLost`(in HistoryID int,in AssetLostID int,in HistoryName varchar(250),in Remarks varchar(250),
in UserID int,in InMode varchar(200))

Begin
    

	if inmode= 'INSERT' then		
		insert into trnassetlost(HistoryID,HistoryName,Remarks,UserID)Values(HistoryID,HistoryName,Remarks,UserID);		
	end if;
    if inmode='UPDATE' then
        update trnassetlost set HistoryID=HistoryID,HistoryName=HistoryName,Remarks=Remarks,UserID=UserID where
        AssetLostID=AssetLostID;
    end if;
    if inmode='DELETE' then
         update trnassetlost set IsActive='FALSE' where AssetLostID=AssetLostID;
    end if;
    if inmode='SELECT' then
        select * from trnassetlost;
    end if;

end




I was wrote select function in my class file using c#:
C#
 public Access objAccess = new Access();

 public DataSet SELECT(BALAssetLost ObjAl)
        {
            param = new MySqlParameter[1];
            param[0] = new MySqlParameter("@inmode", "SELECT");

            DataSet ds = objAccess.GetDataSet(param, "SpAssetLost");
            return ds;
        }

Access.cs:
 public DataSet GetDataSet(MySqlParameter[] param, string procedure)
        {
            try
            {
                establishConnection();
                try
                {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();

                    }
                    con.Open();
                    MySqlCommand cmd = new MySqlCommand(procedure, con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(param);
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


now i got error "Parameter 'HistoryID' not found in the collection".so i want to set null in unwanted variables.how can i declare null while creating stored procedure in mysql workbench.

PLEASE ANYBODY HELP ME.
Please anybody help me.
Posted
Updated 21-Dec-12 20:29pm
v2

1 solution

Use the value DBNull.Value[^] when you want to pass a null through to SQL.
 
Share this answer
 
Comments
MURALIBALA1991 22-Dec-12 7:24am    
I have more than 20 parameter means i set DBNull Vaule for corresponding parameter.output also come also code will large so how can i set default null in stored procedure for parameter .
Jibesh 22-Dec-12 9:42am    
If your SP contains parameters means you must add the parameter to the SqlCmd inorder to execute the SP. it's like calling a function, to call a parmaterized function you must call it with proper argument else it wont allow you to compile.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900