Database layer issue

Hello,

I am executing the stored procedures based on the following from by dataaccess layer. The issue is that some how it is not tackling null values. When I execute the stored procedure from the query analyzer it is working fine.

BUT when I execute from the ASP.net page I am getting an error saying input string was not in correct format.

Pls help

 /////////////////////////////////////////////////////////////////////////////////////////////////////////
        public bool InitialiseProcedure(string Procedure)
        /////////////////////////////////////////////////////////////////////////////////////////////////////////
        {
            // set the initial state preparatory to performing a stored procedure operation

            CloseReader();

            Command.Parameters.Clear();
            Command.CommandType = CommandType.StoredProcedure;
            Command.CommandText = Procedure;

            /* try
            {
                SqlCommandBuilder.DeriveParameters(Command);
            }
            catch (Exception Ex)
            {
                return false;
            }*/

            return true;
        }

        /////////////////////////////////////////////////////////////////////////////////////////////////////////
        public void SetParam(string ParamName, object ParamValue, DbType dbType)
        /////////////////////////////////////////////////////////////////////////////////////////////////////////
        {
            // set the value of the specified parameter

            try
            {
                if (Command.Parameters.Contains(ParamName))
                    Database.SetParameterValue(Command, ParamName, ParamValue);
                else
                    Database.AddParameter(Command, ParamName, dbType, ParameterDirection.Input, "", DataRowVersion.Default, ParamValue);
            }
            catch (Exception Ex)
            {
                ReportError(Ex, "CommandText:[" + Command.CommandText + "] ParamName:[" + ParamName + "] ParamValue[" + ParamValue.ToString() + "]");
            }
        }

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////And this is how I am executing the stored procedure

/////////////////////////////////////////////////////////////////////////////////////////////////////////
    public int LogRecipients()
    /////////////////////////////////////////////////////////////////////////////////////////////////////////
    {

        if (!Log || Db == null || Recipients.Count == 0)
            return -1;

        Db.InitialiseProcedure("z360core_insert_mail_message");

        Db.SetParam("@subject", Msg.Subject, System.Data.DbType.String);
        Db.SetParam("@recipient", Recipients[0].ToString(), System.Data.DbType.String);
        Db.SetParam("@sender", Msg.FromAddress, System.Data.DbType.String);
        Db.SetParam("@sender_name", Msg.FromName, System.Data.DbType.String);
        Db.SetParam("@message_id", Msg.MessageId, System.Data.DbType.String);
        Db.SetParam("@content_type", Msg.ContentType, System.Data.DbType.String);
        Db.SetParam("@email_date", System.DateTime.Now, System.Data.DbType.DateTime);
        Db.SetParam("@processed", System.DateTime.Now, System.Data.DbType.DateTime);
        Db.SetParam("@processed_by", App.UserId, System.Data.DbType.Int32);
        Db.SetParam("@ticket_number", TicketNumber, System.Data.DbType.Int32);

        //if (ParentMailMessageId != "")
            Db.SetParam("@mail_message_id", ParentMailMessageId, System.Data.DbType.Int32);

        int MailMessageId = int.Parse(Db.ExecuteScalarProcedure().ToString());

        if (MailMessageId < 0)
            return -1;

return MailMessageId;
    }


sureshrainaAsked:
Who is Participating?
 
davehunt00Commented:
My guess is it is one of the date fields, but you could try putting in some checks for null similar to what you have commented out:

if (ParentMailMessageId != "")

except use something like:

if (xyz is DBNull.Value) then xyz = ""

 Db.SetParam("@xyz", xyz, System.Data.DbType.String);

Of course, I'm not sitting in front of VS so check for syntax errors there...

Dave
0
 
nauman_ahmedCommented:
What line the error is coming on?

Also look at the following code:

Title: Writing Data Access Layer
http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_20993347.html

-Nauman.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.