how to make sql pivot with visual basic 6-VBForums
Results 1 to 4 of 4

Thread: how to make sql pivot with visual basic 6

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2017
    Posts
    5

    how to make sql pivot with visual basic 6

    hello sir, iam still new in vb 6
    can someone help me to

    how to code make if selected date from 2017-10-01 - 2017-10-06 (there are 5 day +1 = 6) when click push button idaily added from idaily1 idaily2 idaily3 idaily4 idaily5 idaily6

    i am still using manual code to show idaily, how to make otomatic with the range date
    i use code
    Code:
    Option Explicit
    
    Dim iDaily As Integer
    
    Dim vSubTotal As Double
    Public sQuery As String
    Public pConnection As New ADODB.Connection
    
    Dim rs As New Recordset
    Dim conn As New ADODB.Connection
    
    Private Sub openDB()
    Set rs = New ADODB.Recordset
    Set conn = New ADODB.Connection
    conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSpi;persist security Info=false;Initial Catalog=test"
    
    End Sub
    Private Sub PushButton1_Click()
    OseTextBox1.Text = DateTimePicker2.Value - DateTimePicker1.Value
    End Sub
    
    Private Sub PushButton2_Click()
    FlatEdit1.Text = OseDateEnd.Value - OseDateStart
    FlatEdit1.Text = FlatEdit1.Text + 1
    iDaily = FlatEdit1.Text
    lblLabel1.Caption = iDaily
    
    Call openDB
    
    rs.Open "select * from (select distinct id,item,whouse,st from testdrive )src pivot ( sum(st) for whouse in ([A],[B],[C],[iDaily1],[iDaily2],[iDaily3],[iDaily4],[iDaily5],[iDaily6]) ) piv ", conn, adOpenStatic, adLockReadOnly
    
    Set ReportControl.DataManager.DataSource = rs
    ReportControl.DataManager.DataBind
    'Set other ReportControl options
    ReportControl.PaintManager.VerticalGridStyle = xtpGridSolid
    ReportControl.PaintManager.HorizontalGridStyle = xtpGridSolid
    ReportControl.AllowEdit = False
    ReportControl.FocusSubItems = False
    ReportControl.AllowColumnRemove = False
    ReportControl.PaintManager.FixedRowHeight = False
    ReportControl.AllowColumnSort = False
    ReportControl.FreezeColumnsCount = 3
    link image view my program
    image

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,486

    Re: how to make sql pivot with visual basic 6

    Don't say "SQL" when you mean "SQL Server" they are two entirely different things.

    You might get more and better help more quickly if you post database questions to the database forum.

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,824

    Re: how to make sql pivot with visual basic 6

    I'm not yet convinced that the question is about the SQL itself. It seems like it's a question that is kind of in the middle, but I'm WAY too rusty on some of the technology being used there. Is this a pivot issue, or an issue about getting the range?
    My usual boring signature: Nothing

  4. #4
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    564

    Re: how to make sql pivot with visual basic 6

    Quote Originally Posted by aguswibowo View Post
    hello sir, iam still new in vb 6
    can someone help me to

    how to code make if selected date from 2017-10-01 - 2017-10-06 (there are 5 day +1 = 6) when click push button idaily added from idaily1 idaily2 idaily3 idaily4 idaily5 idaily6

    i am still using manual code to show idaily, how to make otomatic with the range date
    i use code
    Code:
    Option Explicit
    
    Dim iDaily As Integer
    
    Dim vSubTotal As Double
    Public sQuery As String
    Public pConnection As New ADODB.Connection
    
    Dim rs As New Recordset
    Dim conn As New ADODB.Connection
    
    Private Sub openDB()
    Set rs = New ADODB.Recordset
    Set conn = New ADODB.Connection
    conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSpi;persist security Info=false;Initial Catalog=test"
    
    End Sub
    Private Sub PushButton1_Click()
    OseTextBox1.Text = DateTimePicker2.Value - DateTimePicker1.Value
    End Sub
    
    Private Sub PushButton2_Click()
    FlatEdit1.Text = OseDateEnd.Value - OseDateStart
    FlatEdit1.Text = FlatEdit1.Text + 1
    iDaily = FlatEdit1.Text
    lblLabel1.Caption = iDaily
    
    Call openDB
    
    rs.Open "select * from (select distinct id,item,whouse,st from testdrive )src pivot ( sum(st) for whouse in ([A],[B],[C],[iDaily1],[iDaily2],[iDaily3],[iDaily4],[iDaily5],[iDaily6]) ) piv ", conn, adOpenStatic, adLockReadOnly
    
    Set ReportControl.DataManager.DataSource = rs
    ReportControl.DataManager.DataBind
    'Set other ReportControl options
    ReportControl.PaintManager.VerticalGridStyle = xtpGridSolid
    ReportControl.PaintManager.HorizontalGridStyle = xtpGridSolid
    ReportControl.AllowEdit = False
    ReportControl.FocusSubItems = False
    ReportControl.AllowColumnRemove = False
    ReportControl.PaintManager.FixedRowHeight = False
    ReportControl.AllowColumnSort = False
    ReportControl.FreezeColumnsCount = 3
    link image view my program
    image
    Hi,
    if you want to declare a Range (from a Date) in a Pivot table you have to declare it.

    example..
    sorry this is German but I will try to make it clear.
    the first Line states the Parameters for the search
    KW is a number = Calander week
    FJahr is a number = the Year

    Code:
    PARAMETERS KW Long, FJahr Long;
    TRANSFORM Sum([Order Details].Quantity) AS [Summe von Quantity]
    SELECT Products.ProductName
    FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
    WHERE (((Year([OrderDate]))=[FJahr]) AND ((Format([OrderDate],"ww"))=[KW]))
    GROUP BY Products.ProductName, Format([OrderDate],"ww")
    PIVOT Format([OrderDate],"dddd") In ("Montag","Dienstag","Mittwoch","Donnerstag","Freitag","Samstag","Sonntag");
    I use the OrderDate and Group it to "ww" for the Calendar week
    I want the PIVOT Format to show the Days in that Week and Year= ("Monday","Tuesday", etc....")

    hope it Helps

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.