ASP.NET(vb.net) & Export Record/Data To CSV - In this article we will example scripts ASP.NET how to used in the export table data to a comma-separated values (CSV).
ShotDev Focus:
- ASP.NET(vb.net) & Export Record/Data To CSV
Example
AspNetExportDataToCsv.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Import Namespace="System.IO"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
IF NOT Page.IsPostBack() Then
BindData()
End IF
End Sub
Sub BindData()
Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("database/mydatabase.mdb")&";"
objConn = New OleDbConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM customer"
Dim dtReader As OleDbDataReader
objCmd = New OleDbCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
'*** BindData to Repeater ***'
myRepeater.DataSource = dtReader
myRepeater.DataBind()
dtReader.Close()
dtReader = Nothing
objConn.Close()
objConn = Nothing
End Sub
Sub Button1_Click(sender As Object, e As EventArgs)
Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString,strSQL As String
Dim i As Integer
Dim strPath As String = "csv/"
Dim strFileName As String = "customer.csv"
'*** Create Text Files (Columns Default ***' ***'
Dim StrWer As StreamWriter
StrWer = File.CreateText(Server.MapPath(strPath) & strFileName)
StrWer.Write("CustomerID,Name,Email,CountryCode,Budget,Used")
StrWer.Close()
'*** Connect to CSV ***'
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()
Dim chkCusID As CheckBox
Dim lblCustomerID,lblName,lblEmail,lblCountryCode,lblBudget,lblUsed As Label
For i = 0 To myRepeater.Items.Count - 1
chkCusID = myRepeater.Items(i).FindControl("chkCustomerID")
lblCustomerID = myRepeater.Items(i).FindControl("lblCustomerID")
lblName = myRepeater.Items(i).FindControl("lblName")
lblEmail = myRepeater.Items(i).FindControl("lblEmail")
lblCountryCode = myRepeater.Items(i).FindControl("lblCountryCode")
lblBudget = myRepeater.Items(i).FindControl("lblBudget")
lblUsed = myRepeater.Items(i).FindControl("lblUsed")
IF chkCusID.Checked = True Then
'*** Insert to CSV ***'
strSQL = "INSERT INTO " & strFileName & " (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('" & lblCustomerID.Text & "','" & lblName.Text & "','" & lblEmail.Text & "'" & _
" ,'" & lblCountryCode.Text & "','" & FormatNumber(lblBudget.Text,2) & "' " & _
",'" & FormatNumber(lblUsed.Text,2) & "')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
End IF
Next
objCmd = Nothing
objConn.Close()
objConn = Nothing
Me.lblText.Text = "Export Successfully <a href=" & strPath & strFileName & ">Click here</a> to download. "
End Sub
</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<table border="1">
<asp:Repeater id="myRepeater" runat="server">
<HeaderTemplate>
<tr>
<th>Select</th>
<th>CustomerID</th>
<th>Name</th>
<th>Email</th>
<th>CountryCode</th>
<th>Budget</th>
<th>Used</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center"><asp:CheckBox id="chkCustomerID" runat="server"></asp:CheckBox></td>
<td align="center"><asp:Label id="lblCustomerID" runat="server" Text='<%#Container.DataItem("CustomerID") %>'></asp:Label></td>
<td><asp:Label id="lblName" runat="server" Text='<%#Container.DataItem("Name") %>'></asp:Label></td>
<td><asp:Label id="lblEmail" runat="server" Text='<%#Container.DataItem("Email") %>'></asp:Label></td>
<td align="center"><asp:Label id="lblCountryCode" runat="server" Text='<%#Container.DataItem("CountryCode") %>'></asp:Label></td>
<td align="right"><asp:Label id="lblBudget" runat="server" Text='<%#Container.DataItem("Budget") %>'></asp:Label></td>
<td align="right"><asp:Label id="lblUsed" runat="server" Text='<%#Container.DataItem("Used") %>'></asp:Label></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<br />
<asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Submit"></asp:Button>
<hr />
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Screenshot
.
.
.
Download this script.
Loading ...