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(B yVal 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.OleDbData Reader = GetDataReader(s qlText)

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

REM -- create the columns in the datatable
dt.Columns.Add( New DataColumn("Rec LocName",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("Aut hNO",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("Tkt RecDt",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("Rcd VolQT",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("Rcd CullQT",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("SpP rod",
System.Type.Get Type("System.St ring")))

While dbRead.Read()
RecLocName = dbRead.Item("Re cLocNM").ToStri ng()
AuthNO = dbRead.Item("Au thNO").ToString ()
TktRecDt =
DateTime.Parse( dbRead.Item("Tk tRecDT").ToStri ng())
RcdVolQT = dbRead.Item("Rc dVolQT").ToStri ng()
RcdCullQT = dbRead.Item("Rc dCullQT").ToStr ing()
SpProd = dbRead.Item("SP PDFMGD").ToStri ng()

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.EventArg s) Handles MyBase.Load
'Set the appropriate ContentType.
Dim filename As String = "orderdetails.c sv"
Dim myCommand As New SqlCommand _
("select * from [order details] ", SqlConnection1)
myCommand.Conne ction.Open()
Dim myReader As SqlDataReader = _
myCommand.Execu teReader(Comman dBehavior.Close Connection)
Dim i As Integer
Dim sb As New System.Text.Str ingBuilder
For i = 0 To myReader.FieldC ount - 1
If i < (myReader.Field Count - 1) Then
sb.Append(Chr(3 4) & myReader.GetNam e(i) & Chr(34) & ",")
Else
sb.Append(Chr(3 4) & myReader.GetNam e(i) & Chr(34) & vbCrLf)
End If
Next

While myReader.Read()
For i = 0 To myReader.FieldC ount - 1
If i < (myReader.Field Count - 1) Then
sb.Append(Chr(3 4) & myReader.GetVal ue(i).ToString & _
Chr(34) & ",")
Else
sb.Append(Chr(3 4) & myReader.GetVal ue(i).ToString & _
Chr(34) & vbCrLf)
End If
Next

End While

myReader.Close( )
SqlConnection1. Close()
Response.Conten tType = "Applicatio n/x-msexcel"
Response.AddHea der _
("content-disposition", "attachment ; filename=""" & filename & """")
'Write the file directly to the HTTP output stream.
Response.Write( sb.ToString)
Response.End()
End Sub

"Steven Thomas" <st******@gapac .com> wrote in message
news:61******** *************** ***@posting.goo gle.com...
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(B yVal 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.OleDbData Reader = GetDataReader(s qlText)

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

REM -- create the columns in the datatable
dt.Columns.Add( New DataColumn("Rec LocName",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("Aut hNO",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("Tkt RecDt",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("Rcd VolQT",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("Rcd CullQT",
System.Type.Get Type("System.St ring")))
dt.Columns.Add( New DataColumn("SpP rod",
System.Type.Get Type("System.St ring")))

While dbRead.Read()
RecLocName = dbRead.Item("Re cLocNM").ToStri ng()
AuthNO = dbRead.Item("Au thNO").ToString ()
TktRecDt =
DateTime.Parse( dbRead.Item("Tk tRecDT").ToStri ng())
RcdVolQT = dbRead.Item("Rc dVolQT").ToStri ng()
RcdCullQT = dbRead.Item("Rc dCullQT").ToStr ing()
SpProd = dbRead.Item("SP PDFMGD").ToStri ng()

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

'Return ds


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