Error converting data type nvarchar to int.

[EDN Admin]

Hey Guys,

My names Mark. Irish Software student studying software design and game development. Currently on work experience using C# and Im new to it so had a few problems.
As you can see, Im getting the "Error converting data type nvarchar to int." problem when I try to edit my gridview data and update the database.

Any help would be sincerely appreciated.

Get Equipment Stored Proc

<pre class="prettyprint USE [MarksDatabase]
GO
/****** Object: StoredProcedure [dbo].[spGetEquipment] Script Date: 06/01/2012 09:47:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spGetEquipment]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT E.ID, DT.[Description], M.Name, E.SerialNumber, DM.[Description], W.YearEnding, CP.CarePackStatus, CP.CarePackNumber, CP.CarePackExpiry , CP.CarePackExpiry, AT.AssetNumber, E.DateOfPurchase, E.CostPrice, DP.DepriciationType, DR.[Description], E.TotalDepreciation, E.NetBookValue
FROM Equipment E
inner join DeviceType DT
ON E.DeviceType = DT.ID
inner join Manufacturer M
ON E.Manufacturer = M.ID
inner join DeviceModel DM
ON E.Model = DM.ID
inner join Warranty W
ON E.Warrenty = W.ID
inner join CarePack CP
ON E.CarePack = CP.ID
INNER JOIN AssetTag AT
on E.AssetTag = AT.ID
INNER JOIN DepreciationType DP
ON E.DepreciationType = DP.ID
INNER JOIN DepriciationRate DR
ON E.DepreciationRate = DR.ID





END[/code]
<br/>

The Update Equipment Query
<pre class="prettyprint USE [MarksDatabase]
GO
/****** Object: StoredProcedure [dbo].[spUpdateEquipment] Script Date: 06/01/2012 09:48:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spUpdateEquipment]
-- Add the parameters for the stored procedure here
@ID AS Numeric,
@DeviceType int,
@Manufacturer int,
@SerialNumber nvarchar(50),
@Model int,
@Warrenty int,
@CarePack int,
@AssetTag int,
@DateOfPurchase date,
@CostPrice money,
@DepreciationType int,
@DepreciationRate int,
@TotalDepreciation money,
@NetBookValue money
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE [Equipment]
SET [DeviceType] = @DeviceType,
[Manufacturer] = @Manufacturer,
[SerialNumber] = @SerialNumber,
[Model] = @Model,
[Warrenty] = @Warrenty,
[CarePack] = @CarePack,
[DateOfPurchase] = @DateOfPurchase,
[CostPrice] = @CostPrice,
[DepreciationType] = @DepreciationType,
[DepreciationRate] = @DepreciationRate,
[TotalDepreciation] = @TotalDepreciation,
[NetBookValue]= @NetBookValue
WHERE [ID] = @ID

END[/code]

Here is the ASP.Net file for the ShowEquipment

<pre class="prettyprint <%@ Page Title = "Show Equipment" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="ShowEquipment.aspx.cs" Inherits="ShowEquipment" %>

<%@ Register assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" namespace="System.Web.UI.DataVisualization.Charting" tagprefix="asp" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent

<style type="text/css
body
{
background-image:url(RBKSmall.png);
}
.All
{
height: 268px;
width: 918px;
}
</style>
</asp:Content>


<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent


<div class = "All

<asp:GridView ID="GridView1" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing"
OnRowUpdating="GridView1_RowUpdating" runat="server

<Columns>
<asp:CommandField HeaderText="Edit-Update" ShowEditButton="True" />
</Columns>

</asp:GridView>



</asp:Content>[/code]

and here is the ShowEquipment C# table

<pre class="prettyprint using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

public partial class ShowEquipment : System.Web.UI.Page
{
public void Page_Load(object sender, EventArgs e)
{

if (!this.IsPostBack)
{
BindData();
}
}

private void BindData()
{
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = " Data Source=ATHDEVMC\SQLExpress;Initial Catalog=MarksDatabase;Integrated Security=false; user id=sa; password=Rbk12345$";
SqlDataAdapter da = new SqlDataAdapter("spGetEquipment", cnn);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindData();
}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
if (((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text == "Insert")
{

SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = " Data Source=ATHDEVMC\SQLExpress;Initial Catalog=MarksDatabase;Integrated Security=false; user id=sa; password=Rbk12345$";
SqlCommand cmd = new SqlCommand("spInsertEquipment", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
else
{
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = " Data Source=ATHDEVMC\SQLExpress;Initial Catalog=MarksDatabase;Integrated Security=false; user id=sa; password=Password.";
SqlCommand cmd = new SqlCommand("spUpdateEquipment", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text;
cmd.Parameters.Add("@DeviceType", SqlDbType.NVarChar,50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
cmd.Parameters.Add("@Manufacturer", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text;
cmd.Parameters.Add("@SerialNumber", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text;
cmd.Parameters.Add("@Model", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text;
cmd.Parameters.Add("@Warrenty", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[6].Controls[0]).Text;
cmd.Parameters.Add("@CarePack", SqlDbType.NVarChar, 50).Value = ((CheckBox)GridView1.Rows[e.RowIndex].Cells[7].Controls[0]).Text;
cmd.Parameters.Add("@AssetTag", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[8].Controls[0]).Text;
cmd.Parameters.Add("@DateOfPurchase", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[9].Controls[0]).Text;
cmd.Parameters.Add("@CostPrice", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[10].Controls[0]).Text;
cmd.Parameters.Add("@DepreciationType", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[11].Controls[0]).Text;
cmd.Parameters.Add("@DepreciationRate", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[12].Controls[0]).Text;
cmd.Parameters.Add("@TotalDepreciation", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[13].Controls[0]).Text;
cmd.Parameters.Add("@NetBookValue", SqlDbType.NVarChar, 50).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[14].Controls[0]).Text;

cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
GridView1.EditIndex = -1;

BindData();
}

}[/code]

I am able to put the equipment into the database no problem.

I can also use the Visual Studio wizard to show the entry and edit the fields and update, but the problem with that is that it only shows the numeric ID and not the description. This means that the users will not be able to know what they are updating.

If I use the stored procedure Ive written I get the table exactly how I want, but the edit and update function will not work and Ive been stuck for at least 2 weeks! Pathetic I know.

Any help would be greatly appreciated as Im getting sick of looking at this without getting anywhere.

Thanks guys, hope you can help and Ive explained myself enough! Let me know if there is anything else you require.

Regards,

Mark.



<br/>

View the full article