VB, SQL Linq, 2 Joins and multiple Groups - Visual Basic Discussion Boards

This is how I do it.
I hope you should just be able to use it as is:

Sub datagridToExcel(filename As String, source As DataGridView, header As String, footer As String, convertToXls As Boolean)


    ' use a static file path for the moment
    'Dim BackupPath As String

    Dim datatable As DataTable = CType(source.DataSource, Data.DataTable)

    Dim Headers As String = String.Empty
    Dim TempRow As String = String.Empty
    Dim SeparationMarker As String = ControlChars.Tab

    'Dim FolderBrowserDialog1 As New System.Windows.Forms.FolderBrowserDialog()


    'FolderBrowserDialog1.ShowDialog()
    'BackupPath = FolderBrowserDialog1.SelectedPath

    If filename <> String.Empty Then

        If File.Exists(filename) Then

            File.Delete(filename)

        End If

        Dim SW As New System.IO.StreamWriter(filename, False, System.Text.Encoding.Unicode)

        ' get the column header names and add to the backup file

        Dim i As Integer = 0

        If Not header = String.Empty Then

            SW.WriteLine(header)
            SW.WriteLine(String.Empty)

        End If


        ' add the headers
        For i = 0 To source.Columns.Count - 1

            Headers &= source.Columns(i).Name & SeparationMarker

        Next

        SW.WriteLine(Headers.ToString)


        ' add each row
        For Each row As DataGridViewRow In source.Rows


            For a As Integer = 0 To datatable.Columns.Count - 1


                'check if it's null

                If IsDBNull(row.Cells(a)) Then

                    TempRow &= String.Empty & SeparationMarker

                Else


                    Dim fmt As String = String.Empty

                    For gridIdx As Integer = 0 To source.ColumnCount - 1

                        If source.Columns(gridIdx).Name = datatable.Columns(a).ColumnName AndAlso source.Columns(gridIdx).DefaultCellStyle.Format() <> String.Empty Then

                            fmt = "{0:" + source.Columns(gridIdx).DefaultCellStyle.Format() + "}"
                            Exit For

                        End If


                    Next

                    If fmt <> String.Empty Then

                        Dim tmpdat As String = String.Format(fmt, row.Cells(a).Value)
                        tmpdat = tmpdat.ToString.Replace(SeparationMarker, String.Empty)

                        TempRow &= tmpdat & SeparationMarker

                    Else

                        TempRow &= row.Cells(a).Value.ToString.Replace(SeparationMarker, String.Empty) & SeparationMarker


                    End If

                End If



            Next

            TempRow = TempRow.Replace(vbCrLf, String.Empty).Replace(vbCr, String.Empty).Replace(vbLf, String.Empty)

            SW.WriteLine(TempRow.ToString)
            TempRow = String.Empty

        Next

        If Not footer = String.Empty Then

            SW.WriteLine(String.Empty)
            SW.WriteLine(footer)

        End If



        SW.Close()


        If convertToXls Then

            Try


                If File.Exists(filename.Substring(0, filename.Length - 4) + ".xlsx") Then

                    File.Delete(filename.Substring(0, filename.Length - 4) + ".xlsx")

                End If


                Dim xlsapp As New Microsoft.Office.Interop.Excel.Application
                Dim xlswb As Microsoft.Office.Interop.Excel.Workbook
                xlsapp.Visible = False

                xlswb = CType(xlsapp.Workbooks.Open(filename), Excel.Workbook)
                CType(xlswb.Worksheets(1), Excel.Worksheet).Activate()
                xlsapp.ActiveWindow.SplitColumn = 0
                xlsapp.ActiveWindow.SplitRow = 1
                xlsapp.ActiveWindow.FreezePanes = True


                CType(xlswb.Worksheets(1), Excel.Worksheet).Cells.AutoFilter(1)
                CType(xlswb.Worksheets(1), Excel.Worksheet).Columns.AutoFit()
                xlswb.SaveAs(filename.Substring(0, filename.Length - 4) + ".xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing)
                xlswb.Close()
                xlsapp.Quit()
                File.Delete(filename)

                MsgBox("data exported to:" + vbNewLine + filename.Substring(0, filename.Length - 4) + ".xlsx", vbInformation, "exported to excel")

            Catch ex As Exception

            End Try


        Else

            MsgBox("Data exported to:" + vbNewLine + filename, vbInformation, "Exported to text file")


        End If



    Else

        MsgBox("Data not exported.", vbInformation, "Exported Cancelled")

    End If


End Sub

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Hi all,
I'm looking for a good library that is able to read/detect barcodes inside a pdf file.

Until now I only found paying ones who are quite expensive and the customer where I work for is a small company that starts with digital archiving but where we need to be able to detect barcodes in tiff and pdf files.

Can anybody advise me a component for this that is not paying ?

Kind regards,
Vincent

Something like this[^]?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

I need a help to add a DTPicker in a column of VSFlexGrid or grid in vb6.0.
Is anyone have suggestion or answer ? Plzz reply as soon as possible.

Thank You.

VB6 is deprecated; it is no longer actively supported.

If memory serves correctly, you'd have to position the control by hand to have it on the location in the grid where you want it.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

How To Set H-Scrolling And V-Scrolling With AspxpivoteGrid Devexpress ver 12.1

Have you tried the DevExpress forums? It is a commercial control, and they have a helpdesk to support their stuff.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

This is related to my post in the database forum from yesterday.
So I took the knowledge from my post below, and applied it to all my other stuff that required it and everything works fine. But I have a question.
I'm led to believe that you can do multiple Joins, but can only do 1 Group By

I have this sql linq statement, and it would be cool or efficient to just get it all in 1 call.
Right now, I just made another statement to get the vendor info.
I not even sure if it's possible to do.

Here's what I have right now.
I thought Group By pi.PartNumber, vi.VendorID Into productsSold would do the trick
perhaps it should be Group By { pi.PartNumber, vi.VendorID } Into productsSold

But then I understand the first Select product = productsSold, the group,
It seems you can only do it once, or you have to group all the Joins into it somehow. This is the part I'm fuzzy on.

Dim m_startDate As DateTime = New DateTime(2015, 1, 1, 0, 0, 0, 0)
Dim m_stopDate As DateTime = New DateTime(2015, 8, 31, 23, 59, 59, 999)
Dim p_PartNumber as string = "24-FLP44"
using context As New DBContext()

        Dim pResult = _
        (
            From cohc In context.Order_History_Cart
            Join pi In context.ProductInfo On cohc.PartNumber Equals pi.PartNumber
            Join vi In context.ProductInfo_Vendors On pi.VendorID Equals vi.VendorID
            Where cohc.PartNumber = p_PartNumber _
            And cohc.OrderDate >= m_startDate _
            And cohc.OrderDate <= m_stopDate _
            Order By cohc.PartNumber
            Group By pi.PartNumber Into productsSold = Group,
            totalQty = Sum(cohc.Qty),
            totalCost = Sum(cohc.Qty * cohc.Cost),
            totalPrice = Sum(cohc.Qty * cohc.Price)
            Select product = productsSold.Select(Function(m) m.pi).FirstOrDefault(), totalQty, totalCost, totalPrice
            Select 
                product.PartNumber,
                product.MPartNumber,
                product.Cost,
                product.Price,
                totalQty,
                totalCost,
                totalPrice,
                vendor.contactname,
                vendor.streetAddress1
                vendor.streetAddress2
        )   

        pResult.Dump()

end using

This is a different problem, but basically the same as I posted in Database.
The difference is that my issues are VB based, and not really database related.
So I can sort of see how to do it in C#, but it doesn't translate to VB EF 6
I can't figure it out. I'm close on it.

So I wrote this years ago, works fine in TSQL. It's an Order Table, that has a SalesTax State Code and SalesTax collected columns. It joins a Table called SalesTaxStates, on the StateCode, and the TSQL Sums up the Order SubTotal and SalesTax, and then gets the StateName, TaxRate.

"DECLARE @startDate AS DATE; " & _
"DECLARE @stopDate AS DATE; " & _
"SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0); " & _
"SET @stopDate = DATETIMEFROMPARTS(@Year, @LastMonth, @LastDay, 23, 59, 59, 999); " & _
"SELECT " & _
"  sts.StateCode " & _
", sts.StateName " & _
", sts.HasSalesTax " & _
", SUM(oh.SubTotal) AS SubTotal " & _
", sts.StateTaxRate " & _
", SUM(oh.SalesTax) AS SalesTax " & _
" From ORDER_HISTORY oh " & _
"LEFT JOIN Shipping_TaxStates sts " & _
"ON sts.StateCode = oh.SalesTaxState " & _
"WHERE oh.OrderDate > @startDate " & _
"AND ohOrderDate < @stopDate " & _
"GROUP BY sts.StateCode, sts.StateName, sts.StateTaxRate, sts.HasSalesTax " & _
"ORDER BY sts.StateName "

I have tried many things today.
I tried a Group Join Into xxx = group
I tried Group By oh = new With / Select New With group.Sum(function(m) m.Qty)
In each case, I can the oh stuff and the sums, but I can't access the Join columns for taxRate
So below is a stable version that works with oh. I figure I'll post something that works rather than complete slop.

Dim m_startDate As DateTime = New DateTime(p_Year, 1, 1, 0, 0, 0, 0)
Dim m_stopDate As DateTime = New DateTime(p_Year, p_LastMonth, p_LastDay, 23, 59, 59, 999)

        Dim context As New DBContext()

        pResults = _
        (
           From oh In context.Order_History
           Where oh.OrderDate >= m_startDate _
           And oh.OrderDate <= m_stopDate _
           And oh.OrderStatus = "COMPLETED"
           Group By oh = New With
           {
               oh.SalesTaxState,
               oh.SubTotal,
               oh.SalesTax
           } Into Group
           Select New With
           {
               .StateCode = oh.SalesTaxState,
               .subTotal = Group.Sum(Function(m) m.SubTotal),
               .salesTax = Group.Sum(Function(m) m.SalesTax)
           }
       )

Something like this should work:

VB.NET

From oh In context.Order_History
Join sts In context.Shipping_TaxStates On oh.SalesTaxState Equals sts.SalesTaxState
Where oh.OrderDate >= m_startDate And oh.OrderDate <= m_stopDate And oh.OrderStatus = "COMPLETED"
Group By sts.SalesTaxState Into GroupedHistory = Group, SubTotal = Sum(oh.SubTotal), SalesTax = Sum(oh.SalesTax)
Select State = GroupedHistory.Select(Function(m) m.sts).First(), SubTotal, SalesTax
Select State.StateCode, State.StateName, State.HasSalesTax, SubTotal, State.StateTaxRate, SalesTax
Order By StateName

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Oh I see what I was missing now.
The Select statement, that selects the group of records first from the Group
And then do the select.

I was baffled, kept getting missing type, missing ), conversion failed for Decimal, etc.
I would of never figured it out, not many vb examples online, plus the VB was slightly different like = Group.

Thanks Richard!

Hello,

I am looking for a way for excel to read the cpu usage of a particular application

One way would be to query WMI from the VBA.

For an example to run a WQL query, have a look at http://stackoverflow.com/questions/287369/use-wmi-to-get-current-windows-username-in-vba[^].

By querying the WMI you can find process specific perfomance data for example from Win32_PerfFormattedData_PerfProc_Process class[^]

As a side-note, not sure why you got downvoted...

This is what I was looking for... Thanks for your help

Not sure why I got downvoted either. Weird

Member 11915345 wrote:
Not sure why I got downvoted either.

Probably because you did not show that you had attempted anything, reversed.

Never underestimate the power of human stupidity
RAH

Mika Wendelius wrote:
As a side-note, not sure why you got downvoted...

I down-voted the question, and having slept on it, I stand by my decision.

The OP has clearly made no effort to answer their own question. If you paste the question into Google, the answer appears within the first 10 results.

But why bother asking Google, when you can paste your question into a forum and get someone else to ask Google, sift through the search results, and present the answer to you on a silver plate? After all, this site was created to be a human search-engine, providing help to those who can't be bothered to help themselves, wasn't it? Dead | X|

And no, I didn't post an explanation for the down-vote. In my experience, that only leads to insults or arguments from the OP. I have yet to see anyone accept the criticism and adjust their approach to the site.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Thanks for the explanation!

I agree that the OP could have Googled and tried to find solutions. However, another aspect is that if you don't quite know what you're looking for where do you start from? For experienced people it's often obvious what search terms to use and what to search for, but what if you're not familiar with the technology in the first place.

Don't get me wrong, I completely agree that people should do the background work properly before asking incomplete questions.

Especially I appreciate that you took time to explain the reason behind the downvote since it got me thinking about the threshold of answering a question at all.

VB

Public Class BaseForm
    Inherits System.Windows.Forms.Form
  ......
    Friend WithEvents MnuBaseFile As System.Windows.Forms.MenuItem
    Friend WithEvents MnuBaseWindow As System.Windows.Forms.MenuItem
    Friend WithEvents MnuBaseFileExit As System.Windows.Forms.MenuItem
....
For Each fp In My.Forms.GetType.GetProperties 
  If fp.Name = "BaseForm" Then
                For Each mnu As MenuItem In Me.Menu.MenuItems

Here I can get mnu.text = &File,
But mnu.Name = "", Nothing.

And I try to use the following block:

VB

 Dim fi As System.Reflection.FieldInfo() = bfType.GetFields(BindingFlags.NonPublic Or BindingFlags.Instance Or BindingFlags.DeclaredOnly)

For Each f As System.Reflection.FieldInfo In fi
    If (f.FieldType.Name.ToString() = "MenuItem") Then
       mnuNameStr = f.Name.Remove(0, 1)
    End If

Now I can get field's name, but I can't use field to set text for the menu.

How can I get a field's name, the Menuitem's name, then set the text for the Menuitem?

Sorry ... I don't understand what you trying to achieve ...
Could you explain it once more (perhaps with an example) ?

I want to change the Text of a Menuitem dynamically in runtime. I can get a Menuitem in the
Menuitems collection, but it's Name property is empty.

VB

For Each fp In My.Forms.GetType.GetProperties
  If fp.Name = "BaseForm" Then
      For Each mnu As MenuItem In Me.Menu.MenuItems

But in property window, the Menuitem has Name property, it's same as a name of field variable, like: MnuBaseFile

The Problem is that you ask the wrong question to the wrong partner.
As I understand :
MenuItem is a Control on your Form - which type has it ?
Why do you search inside the Forms.Properties ? You should search it inside the Form.Controls-Collection. I could specify it with more information from you.

Hello !
I'm creating a vb.net program that will use a sql server database. But for a lot's of record there's a document or image attached. I know that I can save the file on a folder and keep on database only the file path.

But I've read something about FileStream storage in Sql server 2008 , or the possibility to save an entire file on a Varbinary(MAX) filed.

So I'm asking if is a good idea to save files inside the database ?

Thank you !

That depends on what the app has to do with the files. Storing them in the database is a good idea in many cases, but offers no benefits in others.

One benefit of using the database is that you can implement versioning of the files.
Another is that it makes hacking the files more difficult.

The application need only to open the file ( and display its content on a control , or open with default programs on windows ). Also if the user want to change the file that is attached to a record ( for example has saved the wrong file and needs to save another file ).

So , to be more clear , the application only save a file an open it , but there's no need to modify this file.

Then it sounds like you want the file system.