Click here to Skip to main content
12,251,889 members (58,749 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# MySQL
hi this is stored procedure:

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#:
 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 21-Dec-12 20:23pm
Edited 21-Dec-12 20:29pm
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Use the value DBNull.Value[^] when you want to pass a null through to SQL.
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 22 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100