สอบถามคำสั่ง Sql ในการดึงข้อมูลมาแสดงใน DataGridView มาลงบัญชี เดบิตเครดิต ด้วยครับ เขียนโปรเจ็กจบครับ (win app) VB.Net

USE [test] -- << Change to your database name
GO

/* ===== BEGIN CREATION SECTION ==== */

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Debit]') AND type in (N'U'))
DROP TABLE [dbo].[Debit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Debit](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[active_date_time] [DateTime] NULL,
	[value] [Money] NULL,
	[opcode] [varchar](10) NULL,
	[account] [varchar](50) NULL
	)

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Credit]') AND type in (N'U'))
DROP TABLE [dbo].[Credit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Credit](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[active_date_time] [DateTime] NULL,
	[value] [Money] NULL,
	[opcode] [varchar](10) NULL,
	[account] [varchar](50) NULL 
	)

GO

/* ===== END CREATION SECTION ==== */


/* ===== BEGIN INITIALIZED SECTION ==== */

INSERT INTO [Debit] ([active_date_time] ,[value] ,[opcode],[account] )
VALUES ( CAST('2011-02-11T14:05:00' AS DATETIME) ,1000 ,'cash' ,'12100'),
	   ( CAST('2011-02-24T09:30:00' AS DATETIME) ,7000 ,'cash' ,'12100'),
       ( CAST('2011-08-17T11:10:23' AS DATETIME) ,2100 ,'cash' ,'12100'); 
       
INSERT INTO [Credit] ([active_date_time] ,[value] ,[opcode],[account] )
VALUES ( CAST('2011-01-05T09:12:00' AS DATETIME) ,8000 ,'cash' ,'12100'),
       ( CAST('2011-01-08T15:11:00' AS DATETIME) ,1200 ,'cash' ,'12100'),
	   ( CAST('2011-02-10T14:20:00' AS DATETIME) ,2200 ,'cash' ,'12100'),
	   ( CAST('2011-02-25T13:59:00' AS DATETIME) ,3200 ,'cash' ,'12100'),
       ( CAST('2011-10-07T10:09:00' AS DATETIME) ,8200 ,'cash' ,'12100');       
       
/* ===== END INITIALIZED SECTION ==== */

SELECT [active_date_time] 
	,[value] AS [debit_value] ,NULL AS [credit_value]
	,[opcode] ,[account]
FROM [debit]
WHERE [account] = '12100'

UNION

SELECT [active_date_time] 
	,NULL AS [debit_value] ,[value] AS [credit_value]
	,[opcode] ,[account]
FROM [credit]
WHERE [account] = '12100'

ORDER BY [active_date_time] ;

/* ===== END ===== */

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Credit]') AND type in (N'U'))
DROP TABLE [dbo].[Credit]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Debit]') AND type in (N'U'))
DROP TABLE [dbo].[Debit]
GO

sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
sqlStr += ",sum ([nettotal]) as [sumDR],sum ([nettotal]) as [sumCR]"
sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"

 Try
            ds.Clear()
            Dim sqlStr As String
            sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
            sqlStr += ",sum ([nettotal]) as [sumDR]"
            sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
            sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
            sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And  mp.[AccNoType4_ID]=(12)"
            sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"

            da = New SqlDataAdapter(sqlStr, Conn)
            da.Fill(ds, "sell")
            dtSell = ds.Tables("sell")
            DataGridView1.DataSource = ds.Tables("Mapping")


            sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
            sqlStr += ",sum ([nettotal]) as [sumCR]"
            sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
            sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
            sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And  mp.[AccNoType4_ID]=(19)"
            sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"

            da = New SqlDataAdapter(sqlStr, Conn)
            da.Fill(ds, "Cash")
            dtCash = ds.Tables("Cash")
            DataGridView1.DataSource = ds.Tables("Mapping")


            Dim dt As New Data.DataTable("GL")
            dt.Columns.Add("AccNoType1_ID", GetType(System.String))
            dt.Columns.Add("AccNoType2_ID", GetType(System.String))
            dt.Columns.Add("AccNoType3_ID", GetType(System.String))
            dt.Columns.Add("AccNoType4_ID", GetType(System.String))
            dt.Columns.Add("AccNoType4_Name", GetType(System.String))

            'Dim i As String
            For i As Int16 = 0 To ds.table("Sale").rows.count(-1)
              dtSell    = ds.table("Sale").rows(i).("nettotal").tostring
              dtCash    = ds.table("Cash").rows(i).("nettotal").tostring
            Next

 Dim dtSale, dtCash As New DataTable

        Dim strConn As String
        strConn = DBConnString.strConn

        Conn = New SqlConnection()
        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConn
            .Open()
        End With
        Try
            ds.Clear()
            Dim sqlStr As String
            sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
            sqlStr += ",sum ([nettotal]) as [sumDR]"
            sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
            sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
            sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And  mp.[AccNoType4_ID]=(12)"
            sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"

            da = New SqlDataAdapter(sqlStr, Conn)
            da.Fill(ds, "sell")
            dtSale = ds.Tables("sell")



            sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
            sqlStr += ",sum ([nettotal]) as [sumCR]"
            sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
            sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
            sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And  mp.[AccNoType4_ID]=(19)"
            sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"

            da = New SqlDataAdapter(sqlStr, Conn)
            da.Fill(ds, "Cash")
            dtCash = ds.Tables("Cash")



            Dim dt As New Data.DataTable("Mapping")
            dt.Columns.Add("AccNoType1_ID", GetType(System.String))
            dt.Columns.Add("AccNoType2_ID", GetType(System.String))
            dt.Columns.Add("AccNoType3_ID", GetType(System.String))
            dt.Columns.Add("AccNoType4_ID", GetType(System.String))
            dt.Columns.Add("AccNoType4_Name", GetType(System.String))

            'Dim i As String
            For i As Int16 = 0 To ds.table("nettotal").rows.count(-1)
                dtSale = ds.table("Sale").rows(i)("nettotal").tostring
                dtCash = ds.table("Cash").rows("nettotal").tostring

            Next

            DataGridView1.DataSource = ds.Tables("Mapping")


            Dim SumDR As Double = 0
            Dim SumCR As Double = 0
            For i = 0 To DataGridView1.Rows.Count - 1
                SumDR += CDbl(DataGridView1.Rows(i).Cells(7).Value)
                SumCR += CDbl(DataGridView1.Rows(i).Cells(6).Value)
            Next
            txtsumCR.Text = SumCR.ToString("#,##0.00")
            txtsumDR.Text = SumDR.ToString("#,##0.00")
            txtD.Text = CStr(CInt(txtsumDR.Text) - CInt(txtsumCR.Text))





        Catch ex As Exception
            MessageBox.Show(ex.Message, "Connection Failed!", MessageBoxButtons.OK)
        End Try
     
        Conn.Close()
    End Sub

Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing.Drawing2D
Imports System.Globalization

Public Class frmGeneralLedger
    Dim Conn As SqlConnection
    Dim com As SqlCommand
    Dim tr As SqlTransaction
    Dim ds As DataSet = New DataSet
    Dim cmd As SqlCommand = New SqlCommand
    Dim da As SqlDataAdapter
    Dim tmpMSG As String = ""
    Dim sqlPV As SqlDataAdapter
    Dim sb As New StringBuilder
    Dim dtfInfo As DateTimeFormatInfo



    Private Sub frmLedger_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dtfInfo = DateTimeFormatInfo.InvariantInfo
    End Sub

    Private Sub btnProduct_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProduct.Click
        Dim strConn As String
        strConn = DBConnString.strConn

        Conn = New SqlConnection()
        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConn
            .Open()
        End With
        Try
            ds.Clear()
            Dim sqlStr As String
            sqlStr = "Select pt.[AccNoType1_ID], pt.[AccNoType2_ID], pt.[AccNoType3_ID], pt.[AccNoType4_ID],[AccNoType4_Name]"
            sqlStr += ",sum([DiscountPrice]) as [DiscountPrice]"
            sqlStr += "FROM [SellDetail]sd left join [Product] pd on sd.[ProductID] = pd.[ProductID]"
            sqlStr += "left join [ProductType] pt on pt.[ProductTypeID] = pd.[ProductTypeID]"
            sqlStr += "left join [Sells] s on sd.[sellID] = s.[sellID]"
            sqlStr += "left join [AccNoType4] a4 on pt.[AccNoType4_ID]=a4.[AccNoType4_ID]"
            sqlStr += " Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "' And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "Group by pt.[AccNoType1_ID],pt.[AccNoType2_ID],pt.[AccNoType3_ID],pt.[AccNoType4_ID],[AccNoType4_Name]"
            da = New SqlDataAdapter(sqlStr, Conn)
            da.Fill(ds, "SellDetail")
            DataGridView1.DataSource = ds.Tables("SellDetail")

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Connection Failed!", MessageBoxButtons.OK)
        End Try
        Conn.Close()
    End Sub
 

    Private Sub btnsell_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsell.Click


        Dim dtSale, dtCash As New DataTable

        Dim strConn As String
        strConn = DBConnString.strConn

        Conn = New SqlConnection()
        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConn
            .Open()
        End With
        Try
            ds.Clear()
            Dim sqlStr As String
            sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
            sqlStr += ",sum ([nettotal]) as [sumDR]"
            sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
            sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
            sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And  mp.[AccNoType4_ID]=(12)"
            sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"

            da = New SqlDataAdapter(sqlStr, Conn)
            da.Fill(ds, "sell")
            dtSale = ds.Tables("sell")



            sqlStr = "Select mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name] "
            sqlStr += ",sum ([nettotal]) as [sumCR]"
            sqlStr += "FROM [Mapping] mp left join [AccNoType4] a4 on mp.[AccNoType4_ID] = a4.[AccNoType4_ID]"
            sqlStr += "left join [Sells] s on s.[Mapping_ID] = mp.[Mapping_ID]"
            sqlStr += "Where SellDate Between '" & DateTimePicker1.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And '" & DateTimePicker2.Value.ToString("d", dtfInfo) & "'"
            sqlStr += "And  mp.[AccNoType4_ID]=(19)"
            sqlStr += "Group by mp.[AccNoType1_ID],mp.[AccNoType2_ID],mp.[AccNoType3_ID],mp.[AccNoType4_ID],[AccNoType4_Name]"

            da = New SqlDataAdapter(sqlStr, Conn)
            da.Fill(ds, "Cash")
            dtCash = ds.Tables("Cash")

            Dim dt As New Data.DataTable("show")
            dt.Columns.Add("AccNoType1_ID", GetType(System.String))
            dt.Columns.Add("AccNoType2_ID", GetType(System.String))
            dt.Columns.Add("AccNoType3_ID", GetType(System.String))
            dt.Columns.Add("AccNoType4_ID", GetType(System.String))
            dt.Columns.Add("AccNoType4_Name", GetType(System.String))
            dt.Columns.Add("sumCR", GetType(System.String))
            dt.Columns.Add("sumDR", GetType(System.String))

            For i As Int16 = 0 To ds.table("Sale").rows.count(-1)
                dtSale = ds.table("Sale").rows(i)("nettotal").tostring
                dtCash = ds.table("Cash").rows("nettotal").tostring

            Next

            DataGridView1.DataSource = dt

            Dim SumDR As Double = 0
            Dim SumCR As Double = 0
            For i = 0 To DataGridView1.Rows.Count - 1
                SumDR += CDbl(DataGridView1.Rows(i).Cells(7).Value)
                SumCR += CDbl(DataGridView1.Rows(i).Cells(6).Value)
            Next
            txtsumCR.Text = SumCR.ToString("#,##0.00")
            txtsumDR.Text = SumDR.ToString("#,##0.00")
            txtD.Text = CStr(CInt(txtsumDR.Text) - CInt(txtsumCR.Text))





        Catch ex As Exception
            MessageBox.Show(ex.Message, "Connection Failed!", MessageBoxButtons.OK)
        End Try
     
        Conn.Close()
    End Sub