Hi Guys,
I just want some one to look over this for me really, and possibly can some one tell me how to call the function! as you can see below (VB.Net Code) this is running of a button and it will connect to SQL obtain the relevant information and then write it to a CSV file, but unfortunately im stuck! due to looking at this code for the past hour or so i cant actually think how to do it :-/
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
callReport("FULL", "Self Employed Weekly Extract")
End Sub
Private Sub callReport(ByVal report_type As String, ByVal Address As String)
If report_type = "FULL" Then
'Format(DateAdd(DateInterval.Day, -1, CDate("01/" & Month(Date.Now) & "/" & Year(Date.Now))))
End If
End Sub
Private Function DataSet1(ByVal StrName As String, ByVal StrAddr1 As String, ByVal strAddr2 As String, ByVal strAddr3 As String, ByVal strAddr4 As String, ByVal strPcode As String, ByVal Stats As Boolean) As DataSet
Dim GetReport As New SqlCommand
Dim Datasett As New SqlDataAdapter
Dim reports As New SqlConnection(Configuration.ConfigurationManager.AppSettings("sql").ToString)
Dim DS As New DataSet
Try
If reports.State = ConnectionState.Closed Then reports.Open()
GetReport.Connection = reports
GetReport.CommandType = CommandType.StoredProcedure
If Stats Then
GetReport.CommandText = "dbo.Self_Employed_Weekly_Extract"
End If
GetReport.CommandTimeout = (300)
Datasett.SelectCommand = GetReport
Datasett.Fill(DS, "Information")
GetReport.Dispose()
Return DS
Catch ex As Exception
MsgBox(ex.Message)
Return Nothing
End Try
End Function
Public Shared Sub Main()
Dim Save As String = "C:\Self_Employed_Weekly_Extract.csv"
Dim csvFile As String = My.Application.Info.DirectoryPath & "\Self_Employed_Weekly_Extract.csv"
Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(csvFile, False)
Dim ds As New DataSet '= GetData
'Write Header
Dim strHeader As String = ""
For Each Col As DataColumn In ds.Tables(0).Columns
strHeader += Col.ColumnName & ","
Next
'Write Header
'Write Data
For Each Row As DataRow In ds.Tables(0).Rows
Dim strLine As String = ""
For Each Col As Object In Row.ItemArray
strLine += Col.ToString() & ","
Next
'Write Line
Next
Console.WriteLine(My.Computer.FileSystem.ReadAllText(csvFile))
Dim Svfile As String = ".csv"
Svfile = "\Self_Employed_Weekly_Extract.csv"
MessageBox.Show("this is now complete")You can see at the top where it says call if report_type = full then but i need to call the Dataset function but im unsure how to do this :-/
can some one help me please
the stored procedure is below if you need to see that....
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Scott Atkinson>
-- Create date: <28/09/2010>
-- Description: <Self_Employed_Weekly_Extract>
-- ===========================================
CREATE PROCEDURE [dbo].[Self_Employed_Weekly_Extract]
AS
BEGIN
SELECT addr1, addr2, addr3, addr4, Pcode, YY.[Name]
FROM THAMESBANK.DBO.IC_yyclient YY INNER JOIN
THAMESBANK.DBO.Ic_brpolicy BR on YY.B@=BR.B@ And YY.REF@=BR.REF@ Inner Join
THAMESBANK.DBO.Ic_brcledger LED on BR.POLREF@=LED.POLRef@ And BR.B@=LED.B@ AND
(Led.trantype='New Business' or LED.Trantype='Renewal' or LED.Trantype='Transferd NB') Left Outer Join
THAMESBANK.DBO.Ic_bd_PC DPC on DPC.POLRef@=BR.POLRef@ And DPC.B@=BR.B@ Left Outer Join
THAMESBANK.DBO.Ic_bd_HN DHN on DHN.POLRef@=BR.POLRef@ And DHN.B@=BR.B@
where (DHN.Empstat1 = 'Self Employed' OR DHN.Empstat1 = 'Company Director'
or DPC.Status = 'Self Employed' or DPC.Status = 'Company Director')
And LED.DateCreated >= DATEADD(dd, -7, GETDATE()) and LED.Datecreated <= DATEADD(dd, -1, GETDATE())
AND (BR.B@=2 OR BR.B@=3)
END
GOIf any one else can spot something to why this code wont work besides calling the Dataset function please let me know.
Thanks in advance guys this is much appreciated!!