Imports System.Data
Imports System.Data.OracleClient
Imports Excel
Partial Class ADD
Inherits System.Web.UI.Page
Dim Conn As OracleConnection
Dim Cmd As OracleCommand
Dim sqlx As String
Dim dtReader As OracleDataReader
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If Not IsNothing(myFile1.PostedFile) Then
Dim UlFileName As String
Dim I As Integer
UlFileName = "Myfile/" & System.IO.Path.GetFileName(myFile1.Value)
myFile1.PostedFile.SaveAs(Server.MapPath(UlFileName))
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
xlBook = xlApp.Workbooks.Open(Server.MapPath(UlFileName))
xlBook.Application.Visible = False
xlSheet1 = xlBook.Worksheets(1)
Dim dt As New System.Data.DataTable
Dim dr As System.Data.DataRow
dt.Columns.Add("STUDENTCODE")
dt.Columns.Add("STUACTIVITY_ID")
I = 2
Do While Not Trim(xlSheet1.Cells.Item(I, 1).value) = ""
dr = dt.NewRow
dr("STUDENTCODE") = xlSheet1.Cells.Item(I, 1).value
dr("STUACTIVITY_ID") = xlSheet1.Cells.Item(I, 2).value
dt.Rows.Add(dr)
I = I + 1
Loop
For I = 0 To dt.Rows.Count - 1
sqlx = "INSERT INTO DATAAPP.STU_EXCEL (ID,STUDENTCODE,STUACTIVITY_ID)"
sqlx &= "VALUES(SQ_EXCEL.NEXTVAL,'" & dt.Rows(I)("STUDENTCODE") & "','" & dt.Rows(I)("STUACTIVITY_ID") & "')"
'Response.Write(sql_add)
Cmd = New OracleCommand(sqlx, Conn)
Cmd.ExecuteReader()
Next
Me.lblText.Text = "บันทึกข้อมูลเรียบร้อย"
xlApp.Application.Quit()
xlApp.Quit()
xlSheet1 = Nothing
xlBook = Nothing
xlApp = Nothing
End If
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strconnstring As String
strconnstring = "Data Source=dataapp;User Id=dataapp;Password=xxxx;"
Conn = New OracleConnection(strconnstring)
Conn.Open()
End Sub
End Class