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)
comm.Parameters("filepath").Value = Filnlr
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]