Error Conversion Varchar value '' overflowed an int2 column

See more:

SQL-Server-2005

VB

SQL-Server

Hi All

I am importing xml doc into Sql server via Vb.net 2008
Please take a look at my problem (below).

MY Store Procedure Name = SaveTrnbal

USE [NAGCM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SaveTrnBal] 
@filepath AS NVARCHAR(255)
AS
BEGIN

DECLARE @CD TABLE (XMLData XML);
DECLARE @sql  AS NVARCHAR(MAX)

SET @sql = 'SELECT * FROM OPENROWSET(BULK ''' + @filepath + ''', Single_Blob) as tt '

INSERT INTO @CD
Exec (@sql)

INSERT INTO NAGCM.dbo.TrnBal (LR_no, LR_Date, Bkg_Stn, FrmShort, Dst, ToShort, Consignor_Name, Consignee_Name, Pvt_Mark, Discription, Article, 
Weight,Value,Pmt_Mode,DoorDel,LR_Remark,Freight,BC,Handling,DDel,LC,Others,S_Tax,Amount,Initial,Truck_No,Report_No,Rep_Date,Trk_From,Trk_To)

SELECT  LR_No = x.data.value('LR_No[1]', 'Varchar(50)'),
		LR_Date = x.data.value('LR_Date[1]', 'smalldatetime'),
		Bkg_Stn = x.data.value('Bkg_Stn[1]', 'Varchar(30)'),
		FrmShort = x.data.value('FrmShort[1]', 'Varchar(5)'),
		Dst = x.data.value('Dst[1]', 'Varchar(30)'),
		ToShort = x.data.value('ToShort[1]', 'Varchar(5)'),
		Consignor_Name = x.data.value('Consignor_Name[1]', 'Varchar(30)'),
		Consignee_Name = x.data.value('Consignee_Name[1]', 'Varchar(30)'),
		Pvt_Mark = x.data.value('Pvt_Mark[1]', 'Varchar(20)'),
		Discription = x.data.value('Discription[1]', 'Varchar(20)'),
		Article = x.data.value('Article[1]', 'decimal(18,0)'),
		Weight  = x.data.value('Weight[1]', 'decimal(18,0)'),
		Value= x.data.value('Value[1]', 'decimal(18,0)'),
		Pmt_Mode = x.data.value('Pmt_Mode[1]', 'Varchar(5)'),
		DoorDel = x.data.value('DoorDel[1]', 'Varchar(5)'),
		LR_Remark = x.data.value('LR_Remark[1]', 'Varchar(50)'),
		Freight = x.data.value('Freight[1]', 'decimal(18,0)'),
		BC = x.data.value('BC[1]', 'decimal(18,0)'),
		Handling = x.data.value('Handling[1]', 'decimal(18,0)'),
		DDel = x.data.value('DDel[1]', 'decimal(18,0)'),
		LC = x.data.value('LC[1]', 'decimal(18,0)'),
		Others = x.data.value('Others[1]', 'decimal(18,0)'),
		S_Tax = x.data.value('S_Tax[1]', 'decimal(18,0)'),
		Amount = x.data.value('Amount[1]', 'decimal(18,0)'),
		Initial = x.data.value('Initial[1]', 'Varchar(15)'),
		Truck_No = x.data.value('Truck_No[1]', 'Varchar(20)'),
		Report_No = x.data.value('Report_No[1]', 'int'),
		Rep_Date = x.data.value('Rep_Date[1]', 'smalldatetime'),
		Trk_From = x.data.value('Trk_From[1]', 'Varchar(20)'),
		Trk_To = x.data.value('Trk_To[1]', 'Varchar(20)')

FROM @CD t
	CROSS APPLY t.XMLData.nodes('NewDataSet/LRTest') x(data);
END

VB.NET-2008 Code to import Data

Private Sub Impxu()
        Try

            Dim Filnlr As String
            Filnlr = txtimpfn.Text

            If dmlobj.con.State = ConnectionState.Closed Then
                dmlobj.con.Open()
            End If

            Dim comm As New SqlCommand
            comm.CommandType = CommandType.StoredProcedure
            comm.CommandText = "SaveTrnBal"
            Dim sqldb As New SqlDataAdapter
            comm.Connection = dmlobj.con
            'comm.Parameters.Add("filepath", SqlDbType.VarChar, 1000).Value = Filnlr
            comm.Parameters.Add("filepath", SqlDbType.VarChar)
            comm.Parameters("filepath").Value = Filnlr
            'comm.Parameters.Add("@flag", SqlDbType.Bit).Direction = ParameterDirection.Output
            Dim i As Integer
            i = comm.ExecuteNonQuery()
            If i >= 0 Then
                MsgBox("Successfully", MsgBoxStyle.Information, "Import Data")
                insdr()
            Else
                MsgBox("Error", MsgBoxStyle.Critical, "SQL Error")
            End If
        Catch ex As SqlException
            MsgBox(ex.Message.ToString, MsgBoxStyle.Critical, "SQL Error")
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub

Table Structure Name = Trnbal

USE [NAGCM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrnBal](
	[Lr_no] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Lr_Date] [smalldatetime] NULL,
	[Bkg_Stn] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FrmShort] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Dst] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ToShort] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Consignor_Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Consignee_Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pvt_Mark] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Discription] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Article] [decimal](18, 0) NULL,
	[Weight] [decimal](18, 0) NULL,
	[Value] [decimal](18, 0) NULL,
	[Pmt_Mode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DoorDel] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LR_Remark] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Freight] [decimal](18, 0) NULL,
	[BC] [decimal](18, 0) NULL,
	[Handling] [decimal](18, 0) NULL,
	[DDel] [decimal](18, 0) NULL,
	[LC] [decimal](18, 0) NULL,
	[Others] [decimal](18, 0) NULL,
	[S_Tax] [decimal](18, 0) NULL,
	[Amount] [decimal](18, 0) NULL,
	[Initial] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Truck_No] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Report_No] [int] NULL,
	[Rep_Date] [smalldatetime] NULL,
	[Trk_From] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Trk_To] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Arv_No] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ARv_Date] [smalldatetime] NULL,
	[L_Stat] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Now the problem is when i am importing the data
its giving me error :

The conversion of the varchar value '1111619' overflowed an int2 column use a larger integer column.

Guys please help me .... I am waiting for the expert advice...

Thanks in advance

[EDIT]CODE tags added - LOSMAC[/EDIT]