Protected Sub Import_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Import.Click
FileUpload1.PostedFile.SaveAs(Server.MapPath("FlieExcel/" + FileUpload1.FileName))
Dim ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " + Server.MapPath("FlieExcel/" + FileUpload1.FileName) + ";" & _
"Extended Properties=Excel 8.0;"
Dim da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", ConnStr)
Dim ds As New DataSet
da.Fill(ds)
Dim conn As New SqlConnection(MasterClass.ConnStr)
For Each dr As DataRow In ds.Tables(0).Rows
Dim cmd As New SqlCommand()
cmd.CommandText = "INSERT INTO Student(Student_ID,Major_ID,Student_Course,Student_pre,Student_first,Student_last,Student_nick,Student_tower,Student_room,Student_pic,StudentPassword)" & _
" VALUES(@Student_ID,@Major_ID,@Student_Course,@Student_pre,@Student_first,@Student_last,@Student_nick,@Student_tower,@Student_room,@Student_pic,@StudentPassword) "
cmd.Parameters.Add("@Student_ID", SqlDbType.VarChar).Value = dr("Student_ID")
cmd.Parameters.Add("@Major_ID", SqlDbType.VarChar).Value = dr("Major_ID")
cmd.Parameters.Add("@Student_Course", SqlDbType.Int).Value = dr("Student_Course")
cmd.Parameters.Add("@Student_pre", SqlDbType.VarChar).Value = dr("Student_pre")
cmd.Parameters.Add("@Student_first", SqlDbType.VarChar).Value = dr("Student_first")
cmd.Parameters.Add("@Student_last", SqlDbType.VarChar).Value = dr("Student_last")
cmd.Parameters.Add("@Student_nick", SqlDbType.VarChar).Value = dr("Student_nick")
cmd.Parameters.Add("@Student_tower", SqlDbType.VarChar).Value = dr("Student_tower")
cmd.Parameters.Add("@Student_room", SqlDbType.VarChar).Value = dr("Student_room")
cmd.Parameters.Add("@Student_pic", SqlDbType.VarChar).Value = dr("Student_ID") & ".jpg"
cmd.Parameters.Add("@StudentPassword", SqlDbType.VarChar).Value = dr("Student_ID")
cmd.Connection = conn
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Next
MasterClass.Alert.ShowAlertMessage("นำเข้าข้อมูลเรียบร้อย")
End Sub
Protected Sub Import_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Import.Click
FileUpload1.PostedFile.SaveAs(Server.MapPath("FlieExcel/" + FileUpload1.FileName))
Dim ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " + Server.MapPath("FlieExcel/" + FileUpload1.FileName) + ";" & _
"Extended Properties=Excel 8.0;"
Dim da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", ConnStr)
Dim ds As New DataSet
da.Fill(ds)
Dim conn As New SqlConnection(MasterClass.ConnStr)
Dim cmd As New SqlCommand()
cmd.Connection = conn
conn.Open()
cmd.CommandText = "INSERT INTO Student(Student_ID,Major_ID,Student_Course,Student_pre,Student_first,Student_last,Student_nick,Student_tower,Student_room,Student_pic,StudentPassword)" & _
" VALUES(@Student_ID,@Major_ID,@Student_Course,@Student_pre,@Student_first,@Student_last,@Student_nick,@Student_tower,@Student_room,@Student_pic,@StudentPassword) "
For Each dr As DataRow In ds.Tables(0).Rows
Try
cmd.Parameters.Add("@Student_ID", SqlDbType.VarChar).Value = dr("Student_ID")
cmd.Parameters.Add("@Major_ID", SqlDbType.VarChar).Value = dr("Major_ID")
cmd.Parameters.Add("@Student_Course", SqlDbType.Int).Value = dr("Student_Course")
cmd.Parameters.Add("@Student_pre", SqlDbType.VarChar).Value = dr("Student_pre")
cmd.Parameters.Add("@Student_first", SqlDbType.VarChar).Value = dr("Student_first")
cmd.Parameters.Add("@Student_last", SqlDbType.VarChar).Value = dr("Student_last")
cmd.Parameters.Add("@Student_nick", SqlDbType.VarChar).Value = dr("Student_nick")
cmd.Parameters.Add("@Student_tower", SqlDbType.VarChar).Value = dr("Student_tower")
cmd.Parameters.Add("@Student_room", SqlDbType.VarChar).Value = dr("Student_room")
cmd.Parameters.Add("@Student_pic", SqlDbType.VarChar).Value = dr("Student_ID") & ".jpg"
cmd.Parameters.Add("@StudentPassword", SqlDbType.VarChar).Value = dr("Student_ID")
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Catch ex As Exception
'*** Event เมื่อเกิด Error ***'
Finally
'*** (Option) Event เมื่อคำสั่งสมบูรณ์ ***'
End Try
Next
conn.Close()
MasterClass.Alert.ShowAlertMessage("นำเข้าข้อมูลเรียบร้อย")
End Sub