Error: Conversion failed when converting the nvarchar value to data type int

Question

1.00/5 (3 votes)

See more:

hello friends, i m getting, values from itemtemplate in listView, and, using those values, to insert in database,
i m getting the error:
Error: Conversion failed when converting the nvarchar value to data type int
please help me to recover this error.
My code is:

C#

protected void btn_add_Click(object sender, EventArgs e)
    {
        Control c = sender as Control;
        Label lbl = ((Label)c.Parent.FindControl("subitem_nameLabel"));
        Label lbl2 = ((Label)c.Parent.FindControl("item_descriptLabel"));
        Label lbl3 = ((Label)c.Parent.FindControl("item_rateLabel"));
        /*foreach (ListViewItem item in ListView1.Items)
        {
            Label lb = (Label)item.FindControl("subitem_nameLabel");
            Label4.Text = lb.Text;
        }*/
    }


    protected void ListView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
        Control c = sender as Control;
        Label lbl =((Label)c.Parent.FindControl("subitem_nameLabel"));
        Label lbl2=((Label)c.Parent.FindControl("item_descriptLabel"));
        Label lbl3 = ((Label)c.Parent.FindControl("item_rateLabel"));
        Session["subitem_name"]= lbl.Text;
        Session["item_descript"] = lbl2.Text;
        Session["item_rate"] = lbl3.Text;

        try
        {
            cnn.Open();
            SqlCommand cm = new SqlCommand("str_order", cnn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.Add("@subitem_name", Session["subitem_name"]);
            cm.Parameters.Add("@item_descript", Session["item_descript"]);
            cm.Parameters.Add("@item_rate", Session["item_rate"].ToString());
            cm.ExecuteNonQuery();
            Response.Write("<script>('Items Added')</script>");
            cnn.Close();
        }
        catch (Exception ex)
        {
            //Response.Write("<script>('There is an Error while adding these Items')</script>");
            Response.Write(ex.Message);

        }


    }

My Source code is:

ASP.NET

<asp:ListView ID="ListView1" runat="server" DataKeyNames="itemname" 
                DataSourceID="SqlDataSource1" 
                >
                   
                   
                   <EmptyDataTemplate>
                       No data was returned.
                   </EmptyDataTemplate>
                   
                   <ItemSeparatorTemplate>
                       <br />
                   </ItemSeparatorTemplate>
                   <ItemTemplate>
                       <li style="background-color: #DCDCDC; color: #000000;">Subitem Name:
                           <asp:Label ID="subitem_nameLabel" runat="server" 
                               Text='<%# Eval("subitem_name") %>' />
                           <br />
                           Item Description:
                           <asp:Label ID="item_descriptLabel" runat="server" 
                               Text='<%# Eval("item_descript") %>' />
                           <br />
                           Item Rate:
                           <asp:Label ID="item_rateLabel" runat="server" Text='<%# Eval("item_rate") %>' />
                           <br />
                           <asp:Button ID="btn_add" runat="server" Text="Add" CommandName="Select" OnClick="ListView1_SelectedIndexChanged" />
                       </li>
                   </ItemTemplate>
                   <LayoutTemplate>
                       <ul ID="itemPlaceholderContainer"  runat="server" 
                           style="font-family: Verdana, Arial, Helvetica, sans-serif;">
                           <li  runat="server" id="itemPlaceholder" />
                           
                           <br />
                           </li>
                       </ul>
                       <div style="text-align: center;background-color: #CCCCCC;font-family: Verdana, Arial, Helvetica, sans-serif;color: #000000;">
                       </div>
                   </LayoutTemplate>
                   
            </asp:ListView>

i have changed my code:

C#

protected void ListView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
        Control c = sender as Control;
        Label lbl =((Label)c.Parent.FindControl("subitem_nameLabel"));
        Label lbl2=((Label)c.Parent.FindControl("item_descriptLabel"));
        Label lbl3 = ((Label)c.Parent.FindControl("item_rateLabel"));
        Session["subitem_name"] = lbl.Text;
        Session["item_descript"] = lbl2.Text;
        decimal item_rate = Convert.ToDecimal(lbl3.Text);
        Session["item_rate"] = item_rate;

        try
        {
            cnn.Open();
            SqlCommand cm = new SqlCommand("str_order", cnn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@subitem_name", Session["subitem_name"]);
            cm.Parameters.AddWithValue("@item_descript", Session["item_descript"]);
            cm.Parameters.AddWithValue("@item_rate", Session["item_rate"].ToString());
            cm.ExecuteNonQuery();
            Response.Write("<script>('Items Added')</script>");
            cnn.Close();
        }
        catch (Exception ex)
        {
            //Response.Write("<script>('There is an Error while adding these Items')</script>");
            Response.Write(ex.Message);

        }


    }

And i am still getting error:
Conversion failed when converting the nvarchar value 'asdd' to data type int.

Posted 13-Mar-13 7:42am

Ankit_Sharma1987

Updated 13-Mar-13 9:02am

v4

Add a Solution

Comments

Member 9581488 13-Mar-13 13:47pm

Session["item_rate"] convert this to int if your @item_rate datatype is int.
Double check your datafield's datatype in database as well as in C# when you pass it to database.

Ankit_Sharma1987 13-Mar-13 13:50pm

Thanks for reply, but sir i am using Stored proceedure:
USE [JIN]
GO
/****** Object: StoredProcedure [dbo].[str_order] Script Date: 03/13/2013 22:17:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[str_order]
@subitem_name nvarchar(50),
@item_descript nvarchar(100),
@item_rate decimal(18,2)
as
begin
declare @entrydate date
set @entrydate=GETDATE()
declare @time time
set @time= CURRENT_TIMESTAMP
declare @order_id nvarchar(50)
set @order_id=@subitem_name+DAY(getdate())
insert into Jin_order(subitem_name,item_descript,item_rate,entrydate,time,order_id)values(@subitem_name,@item_descript,@item_rate,@entrydate,@time,@order_id)
end
so you can check this!!

Member 9581488 13-Mar-13 13:52pm

what is the value of Session["item_rate"]?

Ankit_Sharma1987 13-Mar-13 13:57pm

sir it is showing null, it is showing null, but when i was displaying, it on label then, it is,taking value....

Member 9581488 13-Mar-13 14:04pm

Read the explanation in answer by SA.
If you cant troubleshoot the error, Try passing some static values to it.

Richard C Bishop 13-Mar-13 13:49pm

You will need to specify which line this exception is taking place.

Ankit_Sharma1987 13-Mar-13 13:55pm

yes sir,after applying breakpoint ,when i debugged the, solution, then,on
cm.ExecuteNonQuery();
it is directly going to catch, and throwing error
Error: Conversion failed when converting the nvarchar value to data type int

Richard C Bishop 13-Mar-13 14:03pm

Ok, I see what you did with the bolding of the error message and that line of code in your thread. However, not a clear indication. Anyway, I see that you are getting your Session values from a label with text. Try creating a decimal variable and convert the lbl3.text to a deciaml and assign it to that variable. I will post an example as a solution below.

madhav_jain 13-Mar-13 13:55pm

What is the data type of item_rate in the table in data base? if datatype is Int and you try to insert string into table. an you got this error. check your DB table and insert proper data into it.

Ankit_Sharma1987 13-Mar-13 13:59pm

Thanks, 4 reply sir
you can check my DB details
CREATE TABLE [dbo].[Jin_order](
[id] [int] IDENTITY(1,1) NOT NULL,
[order_id] [nvarchar](50) NULL,
[subitem_name] [nvarchar](50) NULL,
[item_descript] [nvarchar](100) NULL,
[item_rate] [decimal](18, 2) NULL,
[entrydate] [date] NULL,
[time] [time](7) NULL,
[activestatus] [int] NULL
)
item_rate is decimal(18,2)

4 solutions

Solution 2

Try this:

Label lbl3 = ((Label)c.Parent.FindControl("item_rateLabel"));
         Session["subitem_name"]= lbl.Text;
         Session["item_descript"] = lbl2.Text;
         decimal item_rate = Convert.ToDecimal(lbl3.text); 
         Session["item_rate"] = item_rate;

Posted 13-Mar-13 8:06am

Richard C Bishop

v2

Comments

Member 9581488 13-Mar-13 14:08pm

decicaml item_rate = Convert.ToDecimal(lbl3.text);
should be
decimal item_rate = Convert.ToDecimal(lbl3.text);

Richard C Bishop 13-Mar-13 14:09pm

Yes, I corrected it.

Solution 1

The SQL data type NVARCHAR is the variable-length Unicode string which is mapped by ADO.NET directly to the .NET class System.String. But of course not to a numeric type. You can further try to interpret the string as int using int.TryParse or int.Parse (this one can throw exception if the string is not parsed as integer).

But this case should make you thinking about your database schema. It's likely that you are trying to use strings representing data instead of data itself. This is really, really bad thing. If the respective columns contain strings representing integer values, you should review your database schema and use one of SQL integer types instead of string.

—SA

Posted 13-Mar-13 7:59am

Sergey Alexandrovich Kryukov

Solution 3

yes Problem with "[item_rate] [decimal](18, 2) NULL" in database

You can use Convert.ToDecimal(string) method. in your code

c

XML

nn.Open();
             SqlCommand cm = new SqlCommand("str_order", cnn);
             cm.CommandType = CommandType.StoredProcedure;
             cm.Parameters.Add("@subitem_name", Session["subitem_name"]);
             cm.Parameters.Add("@item_descript", Session["item_descript"]);
             cm.Parameters.Add("@item_rate", Convert.ToDecimal(Session["item_rate"].ToString()));
             cm.ExecuteNonQuery();
             Response.Write("<script>('Items Added')</script>");
             cnn.Close();

Posted 13-Mar-13 8:28am

madhav_jain

Comments

Ankit_Sharma1987 13-Mar-13 14:32pm

thanks, sir, i have done , change in code, and here it is...
protected void ListView1_SelectedIndexChanged(object sender, EventArgs e)
{
cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
Control c = sender as Control;
Label lbl =((Label)c.Parent.FindControl("subitem_nameLabel"));
Label lbl2=((Label)c.Parent.FindControl("item_descriptLabel"));
Label lbl3 = ((Label)c.Parent.FindControl("item_rateLabel"));
string subitem = Convert.ToString(lbl.Text);
Session["subitem_name"]= subitem;
string item_descript = Convert.ToString(lbl2.Text);
Session["item_descript"] = item_descript;
decimal item_rate = Convert.ToDecimal(lbl3.Text);
Session["item_rate"] = item_rate;

try
{
cnn.Open();
SqlCommand cm = new SqlCommand("str_order", cnn);
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add("@subitem_name", Session["subitem_name"]);
cm.Parameters.Add("@item_descript", Session["item_descript"]);
cm.Parameters.Add("@item_rate", Session["item_rate"].ToString());
cm.ExecuteNonQuery();
Response.Write("<script>('Items Added')</script>");
cnn.Close();
}
catch (Exception ex)
{
//Response.Write("<script>('There is an Error while adding these Items')</script>");
Response.Write(ex.Message);

}
Then also i am faceing the error:
Conversion failed when converting the nvarchar value 'Kalla Jheennga' to data type int.

[no name] 13-Mar-13 14:37pm

First, do not use the Convert.AnyFunctions. Second, Converting a string to a string and sticking that in a string variable is dumb. Third, use AddWithValue and let the framework handle the conversion for you.

Ankit_Sharma1987 13-Mar-13 14:42pm

Ok Sir now, is it correct?

protected void ListView1_SelectedIndexChanged(object sender, EventArgs e)
{
cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jin"].ConnectionString;
Control c = sender as Control;
Label lbl =((Label)c.Parent.FindControl("subitem_nameLabel"));
Label lbl2=((Label)c.Parent.FindControl("item_descriptLabel"));
Label lbl3 = ((Label)c.Parent.FindControl("item_rateLabel"));
Session["subitem_name"] = lbl.Text;
Session["item_descript"] = lbl2.Text;
decimal item_rate = Convert.ToDecimal(lbl3.Text);
Session["item_rate"] = item_rate;

try
{
cnn.Open();
SqlCommand cm = new SqlCommand("str_order", cnn);
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@subitem_name", Session["subitem_name"]);
cm.Parameters.AddWithValue("@item_descript", Session["item_descript"]);
cm.Parameters.AddWithValue("@item_rate", Session["item_rate"].ToString());
cm.ExecuteNonQuery();
Response.Write("<script>('Items Added')</script>");
cnn.Close();
}
catch (Exception ex)
{
//Response.Write("<script>('There is an Error while adding these Items')</script>");
Response.Write(ex.Message);

}

Richard C Bishop 13-Mar-13 15:05pm

You will want to remove the .ToString() from this line:

cm.Parameters.AddWithValue("@item_rate", Session["item_rate"].ToString());

Ankit_Sharma1987 13-Mar-13 15:13pm

Sir i have revoved..., now item_rate is in decimal, but it is showing same error...
Conversion failed when converting the nvarchar value 'asdd' to data type int.

sir this 'asdd' value, is coming from listview item template, "subitem_name"
this means error is coming from :
Session["subitem_name"] = lbl.Text;

Member 9581488 13-Mar-13 15:14pm

try converting it to string.

Ankit_Sharma1987 13-Mar-13 15:19pm

Like this?
cm.Parameters.AddWithValue("@subitem_name", Session["subitem_name"].ToString());
cm.Parameters.AddWithValue("@item_descript", Session["item_descript"].ToString());

But same error....
Conversion failed when converting the nvarchar value 'asdd' to data type int.

Member 9581488 13-Mar-13 15:26pm

try passing some static value and see if your stored procedure is correctly working or not!!

cm.Parameters.AddWithValue("@subitem_name", "test"); and so on....

Member 9581488 13-Mar-13 15:32pm

insert into Jin_order(subitem_name,item_descript,item_rate,entrydate,time,order_id)values(@subitem_name,@item_descript,@item_rate,@entrydate,@time,@order_id)

In stored procedure what is the exact value its taking for @order_id

Ankit_Sharma1987 13-Mar-13 15:39pm

@order_id sir it is autogenerated field
See procedure:
ALTER procedure [dbo].[str_order]
@subitem_name nvarchar(50),
@item_descript nvarchar(100),
@item_rate decimal(18,2)
as
begin
declare @entrydate date
set @entrydate=GETDATE()
declare @time time
set @time= CURRENT_TIMESTAMP
declare @order_id nvarchar(50)
set @order_id=@subitem_name+DAY(getdate())
insert into Jin_order(subitem_name,item_descript,item_rate,entrydate,time,order_id)values(@subitem_name,@item_descript,@item_rate,@entrydate,@time,@order_id)

Member 9581488 13-Mar-13 15:41pm

I tried your stored procedure. when you comment out the lines:-
--declare @entrydate date
--set @entrydate=GETDATE()
--declare @time time
--set @time= CURRENT_TIMESTAMP
--declare @order_id nvarchar(50)
--set @order_id=DAY(getdate())

Its working fine....so there is error when you set the @order_id Field.

Ankit_Sharma1987 13-Mar-13 15:44pm

no!! but the procedure is running successfully!!

Richard C Bishop 13-Mar-13 15:45pm

It is not running successfully, that is why you are getting an error when calling .ExecuteNonQuery().

Member 9581488 13-Mar-13 15:48pm

so the error is in his stored procedure where he is setting up the value of @order_id.

I replaced line set @order_id=@subitem_name+DAY(getdate()) with
set @order_id=@subitem_name
then execute stored procedure

and now if i run
exec str_order 'asdd','test',10.2

its inserting data....

Richard C Bishop 13-Mar-13 16:10pm

wrong line, sorry

Ankit_Sharma1987 13-Mar-13 15:48pm

okk, sir, now tell me, the reason why it will not work....
i have, mention(declared) all parameters...in code, as well as, in storedproceedure...
when i execute the, sql server, it displays

Ankit_Sharma1987 13-Mar-13 15:49pm

Command(s) completed successfully.

Member 9581488 13-Mar-13 16:03pm

Did you tried the solution that I posted?

Richard C Bishop 13-Mar-13 16:11pm

Ok, so it is working now without the GetDate() added to @order_id. Find a different way to name your order_id so it does not mix datatypes.

Member 9581488 13-Mar-13 16:20pm

its working when
set @order_id=@subitem_name+convert (nvarchar(50),DAY(getdate()))

exec str_order 'asdd','test',10.2 --runs perfectly fine.

Richard C Bishop 13-Mar-13 16:21pm

Nice job, hopefully the OP gets it working too.

Ankit_Sharma1987 14-Mar-13 1:27am

Yes, i got it, thanks
set @order_id=@subitem_name+convert (nvarchar(50),DAY(getdate()))
on using this...
My code is working....
there is problem in Stored proceedure!!
thanks

Member 9581488 14-Mar-13 9:34am

Glad to help you!

Member 9581488 13-Mar-13 15:45pm

your day(getDate()) is taking value of 13 or some integer and you are converting it to @order_id which is nvarchar in your stored procedure as well as in your table.!! check that out!!!!

Ankit_Sharma1987 14-Mar-13 1:30am

Yes, i got it, thanks
set @order_id=@subitem_name+convert (nvarchar(50),DAY(getdate()))
on using this...
My code is working....
there is problem in Stored proceedure!!
thanks

Richard C Bishop 13-Mar-13 15:20pm

Looking at your table Jin_order, the only data type that has an int is "[activestatus]". Where is that getting its value from? Also, what is the value of Session["subitem_name"] when you debug?

Ankit_Sharma1987 13-Mar-13 15:25pm

in db the activestatus default value is "1" , and, after debugging
Session["subitem_name"] = lbl.Text; on lbl.Text it is showing value "asdd"

Ankit_Sharma1987 14-Mar-13 1:27am

Yes, i got it, thanks
set @order_id=@subitem_name+convert (nvarchar(50),DAY(getdate()))
on using this...
My code is working....
there is problem in Stored proceedure!!
thanks

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 9581488 · Accepted Answer · 2013-03-13T09:52:00

USE [JIN]
GO
/****** Object:  StoredProcedure [dbo].[str_order]    Script Date: 03/13/2013 22:17:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[str_order]
@subitem_name nvarchar(50),
@item_descript nvarchar(100),
@item_rate decimal(18,2)
as
begin
declare @entrydate date
set @entrydate=GETDATE()
declare @time time
set @time= CURRENT_TIMESTAMP
declare @order_id nvarchar(50)
set @order_id=@subitem_name+convert (nvarchar(50),DAY(getdate()))
insert into Jin_order(subitem_name,item_descript,item_rate,entrydate,time,order_id)values(@subitem_name,@item_descript,@item_rate,@entrydate,@time,@order_id)
end

Try above stored procedure in your SQL server.

then execute below statement.

exec str_order 'asdd','test',10.2

Error: Conversion failed when converting the nvarchar value to data type int

4 solutions

Solution 2

Solution 4

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us