How to Import Data from Excel into AS/400?
Import Excel to Datagrid first(SQL):
Dim str As String = ""
str = " Provider = Microsoft.Jet.OLEDB.4.0;"
str = str & " Data Source = " & Trim(Me.txtFileName.Text) & ";"
str = str & " Extended Properties=Excel 8.0;"
con = New OleDbConnection(str)
con.Open()
cmd = New OleDbCommand("select * from [sheet1$] ", con)
dr = cmd.ExecuteReader
Me.grdImportDoc.Rows.Clear()
If dr.HasRows Then
While dr.Read
'Me.grdImportDoc.Rows.Add(dr("#"), dr("#"), dr(""), dr(""), dr("COST/M"), dr("QUANTITY"))
If dr(0).ToString = "" Then
Exit While
End If
Me.grdImportDoc.Rows.Add(dr(0), dr(1), dr(2), dr(3), dr(4), dr(5))
End While
End If
Save to AS/400:
If grdImportDoc.Rows.Count = 0 Then
MsgLbl.Text = "Please import data in grid"
Else
For T = 0 To grdImportDoc.Rows.Count - 1
var1 = Trim(grdImportDoc.Rows(T).Cells(0).Value & "")
var2 = Trim(grdImportDoc.Rows(T).Cells(1).Value & "")
var3 = Trim(grdImportDoc.Rows(T).Cells(2).Value & "")
var4 = Trim(grdImportDoc.Rows(T).Cells(3).Value & "")
var5 = Convert.ToDecimal(grdImportDoc.Rows(T).Cells(4).Va lue & "")
var6 = Convert.ToInt32(grdImportDoc.Rows(T).Cells(5).Valu e & "")
ds = New DataSet
AScon = New OdbcConnection(AS400Str)
MsgLbl.Text = "Records in processing"
AScon.Open()
AScmd = New OdbcCommand("insert into Severname.Lib name.File name (Fields) values ('" & var1 & "','" & var2 & "','" & var3 & "','" & var4 & "'," & var5 & "," & var6 & ")", AScon)
AScmd.ExecuteNonQuery()
Next
MsgLbl.Text = "Records are added successfully in AS400"
AScon.Close()
End If