<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub btnUpload_OnClick(sender As Object, e As EventArgs)
Dim strPath As String = "csv/"
Dim dt As DataTable
If Me.fiUpload.HasFile Then
Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))
'*** Read CSV to DataTable ***'
dt = CsvCreateDataTable(strPath,fiUpload.FileName)
'*** Insert to Database ***'
InsertToDatabase(dt)
End IF
End Sub
'*** Convert CSV to DataTable ***'
Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
Dim objConn As New OleDbConnection
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"
objConn = New OleDbConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM " & strFilesName
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
Function InsertToDatabase(ByVal dt)
Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString,strSQL As String
Dim i As Integer
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database/stock.mdb") & ";"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()
'*** Loop Insert ***'
For i = 0 To dt.Rows.Count - 1
Try
strSQL = "INSERT INTO stock2 (ID,Location,Back_No,Parts_No,Parts_Name,Quantity,Actual,Quantity_Total) " & _
"VALUES ('" & dt.Rows(i)("ID") & "','" & dt.Rows(i)("Location") & "','" & dt.Rows(i)("Back_No") & "'" & _
" ,'" & dt.Rows(i)("Parts_no") & "','" & dt.Rows(i)("Parts_name") & "','" & dt.Rows(i)("Quantity") & "'" & _
" ,'" & dt.Rows(i)("Actual") & "','" & dt.Rows(i)("Quantity_Total") & "',)"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("ID") & "] Inserted <br>"
Catch err As Exception
Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("ID") & "] Not Insert <br>"
End Try
Next
objCmd = Nothing
objConn.Close()
objConn = Nothing
End Function
</script>
<html>
<head>
<title>ThaiCreate.Com ASP.NET - CSV</title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload id="fiUpload" runat="server"></asp:FileUpload>
<input id="btnUpload" type="button" OnServerClick="btnUpload_OnClick" value="Upload" runat="server" />
<hr />
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Load balance : Server 03
Quote:
