SQL Linq, IQueryable, Join and multiple contains - Visual Basic Discussion Boards

It can be a bit of a headache at some times. No documentation at all. The way it's build is at least fairly compartmentalized and everything follows the same flow but certain parts like this module is a bit more complex. A lot of time gets eaten up just by understanding how things are supposed to work.

It doesn't help that I got a lot of duties that are quite spread out which means that when something pops up in a part which I haven't familiarized myself with it's so much harder.

And lastly the entire system is a classic case of a program or suit of programs in this case which has grown in scope as different needs has come up.

For example there are several types of "orders" which are stored in different tables. There are customer orders, which is the entire order of what they want and how much, shipping orders which specify when and what things are to be delivered, these are related to the customer order. Then we have at least two types of internal productions order which are there to specify when we need to produce what. All complete with poor name conventions and lack of documentation. Sniff | :^)

The upside is that people see me as some sort of magician when there is a problem and quickly figures it out.

I'm a good problem solver but average programmer with zero database experience before I started working here and originally was hired to program robots. Laugh | :laugh:

Member 11683251 wrote:
I started working here and originally was hired to program robots

Ah, in which case you need one of these[^] to fix all your user/database related problems.

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

― Christopher Hitchens

Hi, I am using Vb.net 2010. I have 2 data tables, which have different set of columns. I would like to copy the rows from one data table to another . How to loop through each of the rows in the first data table and set the values in the second data table. Thanks

One possibility is that you create a LINQ query that queries the source datatable and fetches the columns that match in the target datatable. When looping through the results, add a new row as an object array into the target table or create a new table and use that.

Have a look at Creating a DataTable From a Query (LINQ to DataSet)[^]

whats the code for command buttom to call the report with crystal report there are have 2 details in 1 report with database acces in vb6..pls help thks

Please read http://www.codeproject.com/Messages/1278601/How-to-get-an-answer-to-your-question.aspx[^].

It requires a lot of code and no one can or will just write it for you. Do you have a more specific question?

There are only 10 types of people in the world, those who understand binary and those who don't.

I'm using the example provided earlier in a previous post of mine.
I want to convert this sql linq statement into a queryable, so I can adjust the take and skip,
But I can't figure out how to Join and do Multiple contains using IQueryable.

So I wrote this which works, but now I only want to take the data I need, and not the whole table that matches. I thought I needed to go IQueryable for the Take and Skip, not being to add that to the code below. If I'm wrong, please let me know.

Dim p_pageIndex as integer = 1
Dim p_pageOffset as integer = 10
Dim p_SearchQuery as String = "Kneepads"
Using context As New DBContext()

Dim pResults = _
(
    From pi In context.ProductInfo
    Join pik In context.ProductInfo_Keywords On pik.ProductID Equals pi.ProductID
    Where pik.Keywords.Contains(p_SearchQuery.Trim) _
    Or pik.Stream.Contains(p_SearchQuery.Trim) _
    Or pi.PartNumber.Contains(p_SearchQuery.Trim)
    Order By pi.PartNumber, pi.SDescription, pi.Description Descending
    Select
        pi.ProductID,
        pi.CategoryID,
        pi.SubCategoryID,
        pi.SDescription,
        pi.Description,
        pi.Thumbnail,
        pi.PartNumber,
        pi.Price    
)

In the example above, I have multiple contains from 2 tables that are Joined, I can't figure out the wording for the Join or multiple contains. the multiple OrderBy's was from a previous version in TSQL where I used ORDER By RANK

Dim context As New DBContext()
Dim items As IQueryable(Of PRODUCTINFO) = context.ProductInfo

'Sort
items = items.OrderByDescending(Function(m) m.PartNumber).OrderByDescending(Function(m) m.SDescription).OrderByDescending(Function(m) m.Description)

'Filter by Rank
items = items.Where(Function(m) m.PartNumber.Contains(p_SearchQuery))

'Get a Single Page of Data
If (p_pageIndex = 1) Then
   items = items.Take(p_pageOffset)
Else
   items = items.Skip((p_pageIndex - 1) * p_pageOffset).Take(p_pageOffset)
End If

pResults = _
   items.AsEnumerable().Select(Function(pi) New searchIndex With
   {
       pi.ProductID,
       pi.CategoryID,
       pi.SubCategoryID,
       pi.SDescription,
       pi.Description,
       pi.Thumbnail,
       pi.PartNumber,
       pi.Price
   }
)

How to Use Tessnet2 Library[^]

The above code is easy to use... and works well.
Add the tessnet2.dll to the references, add the "tessdata" folder to your project, insert the VB code in a Sub... done.

BUT TESSNET2 IS DEPRECATED.

Someone knows where to find as easy to use VB.net code and a tessnet302.dll working with Tesseract 3.02??

ezio2000 wrote:
BUT TESSNET2 IS DEPRECATED.

Where does it state that? If it is deprecated, then there will also be a recommended alternative.

Looks more like there's a new version of Tesseract, and not of the Tessnet library.

ezio2000 wrote:
Someone knows where to find as easy to use VB.net code and a tessnet302.dll
working with Tesseract 3.02??

Google never heard of the file. Are you sure there is a new library for the new version of the product?

Bastard Programmer from Hell Suspicious | :suss:

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

Please help me to convert the C# coding below to VB.Net.
Thank you

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.IO;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var XML = "XML=<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
                            "<SMS>\n" +
                            "<operations>\n" +
                            "<operation>SEND</operation>\n" +
                            "</operations>\n" +
                            "<authentification>\n" +
                            "<username></username>\n" +
                            "<password></password>\n" +
                            "</authentification>\n" +
                            "<message>\n" +
                            "<sender>SMS</sender>\n" +
                            "<text>Test message [UTF-8]</text>\n" +
                            "</message>\n" +
                            "<numbers>\n" +
                            "<number messageID=\"msg11\">380972920000</number>\n" +
                            "</numbers>\n" +
                            "</SMS>\n";
                HttpWebRequest request = WebRequest.Create("http://atompark.com/members/sms/xml.php") as HttpWebRequest;
                request.Method = "Post";
                request.ContentType = "application/x-www-form-urlencoded";
                ASCIIEncoding encoding = new ASCIIEncoding();
                byte[] data = encoding.GetBytes(XML);
                request.ContentLength = data.Length;
                Stream dataStream = request.GetRequestStream();
                dataStream.Write(data, 0, data.Length);
                using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
                {
                    if (response.StatusCode != HttpStatusCode.OK)
                        throw new Exception(String.Format(
                        "Server error (HTTP {0}: {1}).",
                        response.StatusCode,
                        response.StatusDescription));
                    StreamReader reader = new StreamReader(response.GetResponseStream());
 
                    Console.WriteLine(reader.ReadToEnd());
                    Console.ReadKey();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.ReadKey();
            }
        }
    }
}

We're not going to do your work for you.

Just Google for "Convert C# to VB.NET" and you'll come up with a bunch of online conversion tools.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

Please I'm using vb.net 2013 and Microsoft office 2013. The below code is able to display the datagridview

into excel 2007. But the code does not display onto excel 2013 and no error gets reported. I suspect the

excel 2013 format could be the cause but i do not know how to go about it.

<pre lang="vb"

Dim ExcelSheet As Object
Dim i As Integer
Dim j As Integer

'create object of excel
ExcelApp = CreateObject("Excel.Application")
ExcelBook = ExcelApp.WorkBooks.Add
ExcelSheet = ExcelBook.WorkSheets(1)

With ExcelSheet
For i = 1 To Me.DataGridViewGCL.RowCount
.cells(i, 1) = Me.DataGridViewGCL.Rows(i - 1).Cells("GclNo").Value
For j = 1 To DataGridViewGCL.Columns.Count - 1
.cells(i, j + 1) = DataGridViewGCL.Rows(i - 1).Cells(j).Value
Next
Next
End With

ExcelApp.Visible = True
'
ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing

></pre>

Which version of Office do your interop-dll's target? If you are using the files from Office 2007, then it probably will not be able to read a 2013 file, since Excel 2013 did not exist at that time.

Bastard Programmer from Hell Suspicious | :suss:

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

Thanks for the response. I have excel 2013 and the code executes well in vb 2008 and excel 2007. From the references tab, Microsoft Excel Object Library 15.0 exists. Is there anything I have to do again, please? If you could link me to any code concerning that, i will be glad. Thanks

Should be the correct version. In your loop, add the statement below;

VB

System.Diagnostics.Debug.WriteLine(ExcelSheet.cells(i, 1).Value)

After that, run the code again and see if any values are printed to the output-screen. If there's no data, then you either have no data at that point, data might get converted from the wrong type, or you might be looking at another file than you think you are.

Bastard Programmer from Hell Suspicious | :suss:

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

VB

ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing

I could be wrong, but I suspect when you set all those objects to Nothing, the Excel app is removed from memory and hence is unable to display your worksheet.

Perhaps you can comment those lines out and try?

Happiness will never come to those who fail to appreciate what they already have. -Anon

Many thanks. I commented out those lines, but nothing displays. I dont know what to do now

Benniiit wrote:
but nothing displays

Does an Excel window open? Do you mean the worksheet has no data in it?

Happiness will never come to those who fail to appreciate what they already have. -Anon

Thanks for the message, big bro. Yes, an excel window opens and the worksheet has no data in it.

Well, I guess then you have to run it through the debugger and make sure your DataGridView actually has data in it.

Happiness will never come to those who fail to appreciate what they already have. -Anon

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.