Error:Failed to convert parameter value from a SqlParameter to a Int32.

Topics: Data Access Application Block
May 19, 2009 at 9:54 AM

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hi,

Please let me know why i am getting this error and how i can i solve this error.I am giving the code sample here..when the statement

dr = db.ExecuteReader("get_all_teams_brand_loc_based", ph.ParameterArray); is executed i am getting this error.

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

 

#region

</font></font><font size="2" color="#0000ff">

 

</font>

GetTeams<font size="2">

 

</font>

 

public IDataReader GetTeams(int Brand, int Location)

{

<font size="2">

 

</font>

 

Database db = null;<font size="2">

 

</font>

 

IDataReader dr = null;<font size="2">

db =

</font>

DatabaseFactory.CreateDatabase("Dash Instance");

 

<font size="2">

 

</font>

 

try

{

<font size="2">

 

</font>

 

SqlParameterHelper ph = new SqlParameterHelper();<font size="2">

ph.AddIntParameter(

</font>

"@brand", Brand);<font size="2">

ph.AddIntParameter(

</font>

"@location", Location); <font size="2">

dr = db.ExecuteReader(

</font>

"get_all_teams_brand_loc_based", ph.ParameterArray);

}

<font size="2">

 

</font>

 

catch (Exception  Ex)

{

 

}

<font size="2">

 

</font>

 

return  dr;

 

<font size="2">

}

</font>

#endregion

 

 

----------------------------------------------------------------------------------------------------------------------------

 

 

 

 

 

 

 

 

 

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

public

</font></font>
<font size="2" color="#0000ff">

 

</font>

class SqlParameterHelper : ArrayList

{

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

#region

</font></font>
<font size="2" color="#0000ff">

 

</font>

Properties<font size="2">

 

</font>

 

public SqlParameter[] ParameterArray

{

<font size="2">

 

</font>

 

get

{<font size="2">

 

</font>

 

SqlParameter[] arParameters = new SqlParameter[this.Count];<font size="2">

 

</font>

 

int i = 0;<font size="2">

 

</font>

 

foreach (SqlParameter sp in this)

{

arParameters[i] = sp;

i++;

}

<font size="2">

 

</font>

 

return

arParameters;

}

<font size="2">

}

</font>

 

// end property ParameterArray

#endregion

 

 

 

 

 

 

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

#region

</font></font>
<font size="2" color="#0000ff">

 

</font>

AddIntParameter<font size="2">

 

</font>

 

public void AddIntParameter(string psParmName, int  piValue)

{

<font size="2">

 

</font>

 

SqlParameter loParm = new SqlParameter();

loParm.ParameterName = psParmName;

<font size="2">

loParm.SqlDbType =

</font>

SqlDbType.Int;<font size="2">

loParm.Direction =

</font>

ParameterDirection.Input;

loParm.Value = piValue;

Add(loParm);

<font size="2">

}

</font>

 

// end method AddIntParameter

#endregion

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

}

Thanks,

Ravi

</font></font><font size="2" color="#0000ff">

 

</font>

 

May 19, 2009 at 11:13 AM

Hi,

I think it is asking for an object array. In your implementation you are passing an array of SqlParameter which will be assigned to the parameter value that it will discover. Since your sproc accepts int and you are passing SqlParameter there will be a data type mismatch then you'll get that exception. I modified your code:

        public IDataReader GetTeams(int Brand, int Location)

        {

            Database db = null;

            IDataReader dr = null;

            db = DatabaseFactory.CreateDatabase("xxx");

 

            try

            {

                SqlParameterHelper ph = new SqlParameterHelper();

                ph.AddIntParameter(Brand);

                ph.AddIntParameter(Location);

                dr = db.ExecuteReader("get_all_teams_brand_loc_based", ph.ParameterArray);

            }

            catch (Exception Ex)

            {

 

            }

            return dr;

        }

 

    public class SqlParameterHelper : ArrayList

    {

        public object[] ParameterArray

        {

            get

            {

                object[] arParameters = new object[this.Count];

                int i = 0;

                foreach (object sp in this)

                {

                    arParameters[i] = sp;

                    i++;

                }

                return arParameters;

            }

        }

 

 

        public void AddIntParameter(int piValue)

        {

            Add(piValue);

        }

    }

 

Valiant Dudan
Global Technology & Solutions
Avanade, Inc.

entlib.support@avanade.com

May 19, 2009 at 2:14 PM

Thanks for the prompt response..

I tried this and it's working..........But my concern is i want to set the data type,parameter name,size etc of the parameter .In the previous version i have used  different methods and the  sqlparameter instance to do this.

ex: AddIntParameter;AddStringParameter,

AddBooleanParameter,AddDateParameter etc..

 

 

 

 

 

 

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

#region

</font></font><font size="2" color="#0000ff">

 

</font>

 

AddStringParameter

 

public void AddStringParameter(string psParmName, string psValue, int piLength)

{<font size="2">

 

</font>

 

SqlParameter loParm = new SqlParameter();

loParm.ParameterName = psParmName;

<font size="2">

loParm.SqlDbType =

</font>

SqlDbType.NVarChar;<font size="2">

loParm.Direction =

</font>

ParameterDirection.Input;

loParm.Size = piLength;

loParm.Value = psValue;

Add(loParm);

<font size="2">

}

</font>

 

// end method AddStringParameter

#endregion

Here as it is expecting object..How can i do this?

I think it is a good practice to set the datatype,length etc before passing it DAAB.

Please let me know.

Thanks,

Ravi

May 19, 2009 at 3:49 PM

Hi,

May I ask, do you have any specific requirement in your application to use or create those methods in order to add parameters? Looking at it, you can attain the same goals using the AddInParamater,AddOutParameter and AddParameter methods within the Database abstract class. Like this:

            Database db = DatabaseFactory.CreateDatabase("MyConn");

            DbCommand cmd = db.GetStoredProcCommand("get_all_teams_brand_loc_based");

            int brand = 1;

            int location = 2;

            db.AddInParameter(cmd, "@brand", System.Data.DbType.Int32, brand);

            db.AddInParameter(cmd, "@location", System.Data.DbType.Int32, location);

            IDataReader reader = db.ExecuteReader(cmd);

 

 

 

Valiant Dudan
Global Technology & Solutions
Avanade, Inc.

entlib.support@avanade.com

May 19, 2009 at 9:24 PM

Dudan,

I can do this way.I tried it.This is worked.There is no specific requirement But here the issue is if i have 10 parameters i have to specify the data type 10 times(i.e.,) but in the former case we were using one method only

ph.AddIntParameter("@location", Location);

If this will not work i can go with the solution i have suggested.

Thanks,

Ravi

May 20, 2009 at 4:46 AM

The method db.ExecuteReader("spname", ph.ParameterArray) does not work because it is actually expecting an array of the actual values, not array of SqlParameters.  Thus, if you're stored procedure has an integer and nvarchar parameters, you would call it like:

db.ExecuteReader("spname", 5, "someString');

or as Valiant have mentioned, by passing an array of objects.

You can modify your AddIntParameter like this:

<font size="2" color="#0000ff">

public

</font>

static void AddIntParameter(DbCommand cmd, string paramName, int paramValue, int size)

{

<font size="2">

 

</font>

 

DbParameter param = cmd.CreateParameter();

param.Value = paramValue;

param.ParameterName = paramName;

param.Size = size;

cmd.Parameters.Add(param);

}

This way, you no longer need to have your ParameterHelper class inherit from ArrayList.  You can use it with any DbCommand and need not create new instance of ParameterHelper class for every command you want to execute.  (Notice that I change the method to static.) 

                        Database db = DatabaseFactory.CreateDatabase("MyConn");

            DbCommand cmd = db.GetStoredProcCommand("get_all_teams_brand_loc_based");

            int brand = 1;

            int size= 2;

            ParameterHelper.Add(cmd, "@brand", brand, size);

            IDataReader reader = db.ExecuteReader(cmd);

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.

entlib.support@avanade.com