0

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

GO

If 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!!

1
Contributor
1
Reply
3
Views
7 Years
Discussion Span
Last Post by Scott_Atkins
0

Would it be

Call 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)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.