Code Snipit for saving to CSV

[Steven Thomas]

Can some one help me find a code snipit for saving the contents of a
data set to a comma seperated file?

I have created a web service in asp.net that will read the data from a
AS/400 DB2 database via a OleDB connection. Then I create the
dataset, Now what do I do
Here is the function now
------------------------------------------------
Public Function GetTicketData() As DataSet
'Public Function GetTicketData(ByVal varbpno, ByVal
varAcctperdid) As DataSet
Dim RecLocName As String
Dim AuthNO As String
Dim TktRecDt As Date
Dim RcdVolQT As String
Dim RcdCullQT As String
Dim SpProd As String

REM -- get the data from the database
Dim sqlText As String = "select reclocnm, a.authno, tktrecdt,
rcdvolqt, rcdcullqt, sppdfmgd from "
sqlText = sqlText + " ticket a, tktdtl b, recloc c, spprod d ,
auth e where a.tktid = b.tktid and a.acctperdid = b.acctperdid"
sqlText = sqlText + " and a.reclocid = c.reclocid and
b.spprodid = d.spprodid and a.authno = e.authno"
sqlText = sqlText + " and e.splrbpno = 1 and a.acctperdid =
222"
'sqlText = sqlText + " and e.splrbpno = " + varbpno + " and
a.acctperdid = " + varAcctperdid
Dim dbRead As OleDb.OleDbDataReader = GetDataReader(sqlText)

REM -- create the datatable
Dim ds As DataSet = New DataSet("TicketInfo")
Dim dt As DataTable = ds.Tables.Add("TicketData")
Dim dr As DataRow

REM -- create the columns in the datatable
dt.Columns.Add(New DataColumn("RecLocName",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("AuthNO",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("TktRecDt",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("RcdVolQT",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("RcdCullQT",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("SpProd",
System.Type.GetType("System.String")))

While dbRead.Read()
RecLocName = dbRead.Item("RecLocNM").ToString()
AuthNO = dbRead.Item("AuthNO").ToString()
TktRecDt =
DateTime.Parse(dbRead.Item("TktRecDT").ToString())
RcdVolQT = dbRead.Item("RcdVolQT").ToString()
RcdCullQT = dbRead.Item("RcdCullQT").ToString()
SpProd = dbRead.Item("SPPDFMGD").ToString()

REM -- Add to DataSet ds
dr = dt.NewRow()
dt.Rows.Add(dr)
End While

'Return ds




End Function
-------------------------------------------------------------
Thanks for any help.

[Ken Cox [Microsoft MVP]]

Here's some code that might give you some ideas...


Private Sub Page_Load _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Set the appropriate ContentType.
Dim filename As String = "orderdetails.csv"
Dim myCommand As New SqlCommand _
("select * from [order details] ", SqlConnection1)
myCommand.Connection.Open()
Dim myReader As SqlDataReader = _
myCommand.ExecuteReader(CommandBehavior.CloseConne ction)
Dim i As Integer
Dim sb As New System.Text.StringBuilder
For i = 0 To myReader.FieldCount - 1
If i < (myReader.FieldCount - 1) Then
sb.Append(Chr(34) & myReader.GetName(i) & Chr(34) & ",")
Else
sb.Append(Chr(34) & myReader.GetName(i) & Chr(34) & vbCrLf)
End If
Next

While myReader.Read()
For i = 0 To myReader.FieldCount - 1
If i < (myReader.FieldCount - 1) Then
sb.Append(Chr(34) & myReader.GetValue(i).ToString & _
Chr(34) & ",")
Else
sb.Append(Chr(34) & myReader.GetValue(i).ToString & _
Chr(34) & vbCrLf)
End If
Next

End While

myReader.Close()
SqlConnection1.Close()
Response.ContentType = "Application/x-msexcel"
Response.AddHeader _
("content-disposition", "attachment; filename=""" & filename & """")
'Write the file directly to the HTTP output stream.
Response.Write(sb.ToString)
Response.End()
End Sub

"Steven Thomas" <stmthoma@gapac.com> wrote in message
news:613e1cae.0306260843.2bf4f921@posting.google.c om...
Can some one help me find a code snipit for saving the contents of a
data set to a comma seperated file?

I have created a web service in asp.net that will read the data from a
AS/400 DB2 database via a OleDB connection. Then I create the
dataset, Now what do I do
Here is the function now
------------------------------------------------
Public Function GetTicketData() As DataSet
'Public Function GetTicketData(ByVal varbpno, ByVal
varAcctperdid) As DataSet
Dim RecLocName As String
Dim AuthNO As String
Dim TktRecDt As Date
Dim RcdVolQT As String
Dim RcdCullQT As String
Dim SpProd As String

REM -- get the data from the database
Dim sqlText As String = "select reclocnm, a.authno, tktrecdt,
rcdvolqt, rcdcullqt, sppdfmgd from "
sqlText = sqlText + " ticket a, tktdtl b, recloc c, spprod d ,
auth e where a.tktid = b.tktid and a.acctperdid = b.acctperdid"
sqlText = sqlText + " and a.reclocid = c.reclocid and
b.spprodid = d.spprodid and a.authno = e.authno"
sqlText = sqlText + " and e.splrbpno = 1 and a.acctperdid =
222"
'sqlText = sqlText + " and e.splrbpno = " + varbpno + " and
a.acctperdid = " + varAcctperdid
Dim dbRead As OleDb.OleDbDataReader = GetDataReader(sqlText)

REM -- create the datatable
Dim ds As DataSet = New DataSet("TicketInfo")
Dim dt As DataTable = ds.Tables.Add("TicketData")
Dim dr As DataRow

REM -- create the columns in the datatable
dt.Columns.Add(New DataColumn("RecLocName",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("AuthNO",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("TktRecDt",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("RcdVolQT",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("RcdCullQT",
System.Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("SpProd",
System.Type.GetType("System.String")))

While dbRead.Read()
RecLocName = dbRead.Item("RecLocNM").ToString()
AuthNO = dbRead.Item("AuthNO").ToString()
TktRecDt =
DateTime.Parse(dbRead.Item("TktRecDT").ToString())
RcdVolQT = dbRead.Item("RcdVolQT").ToString()
RcdCullQT = dbRead.Item("RcdCullQT").ToString()
SpProd = dbRead.Item("SPPDFMGD").ToString()

REM -- Add to DataSet ds
dr = dt.NewRow()
dt.Rows.Add(dr)
End While

'Return ds




End Function
-------------------------------------------------------------
Thanks for any help.