Adding integer parameter to stored procedure in ASP.NET C#

Question

While trying to pass an integer parameter @id to a stored procedure, I get an error da.Fill(ds):

Additional information: Conversion failed when converting the varchar value '@id' to data type int.

I have made sure that integer value is passed and stored procedure contain the correct datatype. What other possibilities are there to rectify this error?

SqlConnection conn = new SqlConnection(cs);
conn.Open(); 

SqlCommand cmd1 = new SqlCommand("asp_GetTrainingDetail", conn);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@id", id);

SqlDataAdapter da = new SqlDataAdapter(cmd1);
DataSet ds = new DataSet();
da.Fill(ds);

why would a parameter that ends in "Id" be a varchar?

Mitch Wheat
– Mitch Wheat

2018-04-16 11:42:31 +00:00
Commented Apr 16, 2018 at 11:42 — Mitch Wheat
– Mitch Wheat, Commented Apr 16, 2018 at 11:42
It seems the column id on your table is a varchar

João Menighin
– João Menighin

2018-04-16 11:43:51 +00:00
Commented Apr 16, 2018 at 11:43 — João Menighin
– João Menighin, Commented Apr 16, 2018 at 11:43
Looks like the c# variable id is a string.

Jamiec
– Jamiec

2018-04-16 11:44:43 +00:00
Commented Apr 16, 2018 at 11:44 — Jamiec
– Jamiec, Commented Apr 16, 2018 at 11:44

Patrick Artner · Accepted Answer · 2018-04-16 12:10:56Z

0

If you know better, do not use AddWithValue() ... it has to "guess" what datatype you have in your DB based on what you put into the command. It is errorprone and causes unneeded conversions to take place.

Also: use using(..) around disposables, especially when using Database-access as it will close your connections even if exceptions arise - not using using might let some connection stay unclosed.

DataSet ds = new DataSet ();
using (var conn = new SqlConnection (cs))
{
    using (var cmd1 = new SqlCommand ("asp_GetTrainingDetail", conn))
    {
        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.Parameters.Add("@id", System.Data.SqlDbType.BigInt).Value = id;

        using (var da = new SqlDataAdapter (cmd1))
        {
            da.Fill (ds);
        }
    }
}

Read the link in do not use AddWithValue() for more background infos.

edited Apr 16, 2018 at 12:10

answered Apr 16, 2018 at 12:05

Patrick Artner

51.9k10 gold badges50 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandeep Suthar · Accepted Answer · 2018-04-16 11:48:32Z

0

Try this...

 SqlConnection conn = new SqlConnection(cs);
        conn.Open(); SqlCommand cmd1 = new 
        SqlCommand("asp_GetTrainingDetail", conn);
        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.Parameters.AddWithValue("@id", Int.Parse(id));
        SqlDataAdapter da = new SqlDataAdapter(cmd1);
        DataSet ds = new DataSet();
        da.Fill(ds);

answered Apr 16, 2018 at 11:48

Sandeep Suthar

571 silver badge12 bronze badges

Collectives™ on Stack Overflow

Adding integer parameter to stored procedure in ASP.NET C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related