Click here to Skip to main content
11,711,750 members (79,458 online)
Rate this: bad
good
Please Sign up or sign in to vote.
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:
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: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:
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
Edited 13-Mar-13 9:02am
v4
Comments
Member 9581488 at 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 at 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 at 13-Mar-13 13:52pm
   
what is the value of Session["item_rate"]?
Ankit_Sharma1987 at 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 at 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.
richcb at 13-Mar-13 13:49pm
   
You will need to specify which line this exception is taking place.
Ankit_Sharma1987 at 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
richcb at 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 at 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 at 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)
Rate this: bad
good
Please Sign up or sign in to vote.

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;
  Permalink  
v2
Comments
Member 9581488 at 13-Mar-13 14:08pm
   
decicaml item_rate = Convert.ToDecimal(lbl3.text);
should be
decimal item_rate = Convert.ToDecimal(lbl3.text);
richcb at 13-Mar-13 14:09pm
   
Yes, I corrected it.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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

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

c
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();
  Permalink  
Comments
Ankit_Sharma1987 at 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.
ThePhantomUpvoter at 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 at 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);

}
richcb at 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 at 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 at 13-Mar-13 15:14pm
   
try converting it to string.
Ankit_Sharma1987 at 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 at 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 at 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 at 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 at 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 at 13-Mar-13 15:44pm
   
no!! but the procedure is running successfully!!
richcb at 13-Mar-13 15:45pm
   
It is not running successfully, that is why you are getting an error when calling .ExecuteNonQuery().
Member 9581488 at 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....
richcb at 13-Mar-13 16:10pm
   
wrong line, sorry
Ankit_Sharma1987 at 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 at 13-Mar-13 15:49pm
   
Command(s) completed successfully.
Member 9581488 at 13-Mar-13 16:03pm
   
Did you tried the solution that I posted?
richcb at 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 at 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.
richcb at 13-Mar-13 16:21pm
   
Nice job, hopefully the OP gets it working too.
Ankit_Sharma1987 at 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 at 14-Mar-13 9:34am
   
Glad to help you!
Member 9581488 at 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 at 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
richcb at 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 at 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 at 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

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 434
1 CHill60 280
2 OriginalGriff 170
3 Peter Leow 130
4 Abhinav S 125
0 CHill60 280
1 Sergey Alexandrovich Kryukov 125
2 Andy Lanng 120
3 Abhinav S 95
4 Maciej Los 95


Advertise | Privacy | Mobile
Web01 | 2.8.150819.1 | Last Updated 13 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100