Solved

Database layer issue

Posted on 2006-07-06
2
232 Views
Last Modified: 2008-02-01
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;
    }


0
Comment
Question by:sureshraina
2 Comments
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 17054591
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
 
LVL 4

Accepted Solution

by:
davehunt00 earned 500 total points
ID: 17055336
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now