Beginner question

[AlexanderBB]

Assuming this forum is Ok for newbie question (otherwise please tell me?) I'm just starting and trying to understand the basics.
As a learning tool I copied this from Google and would like to see if it can read a row of data from Excel into a recordset (or equivalent).

Private Sub btnClickThis_Click(sender As Object, e As EventArgs) Handles btnClickThis.Click

    Dim sSheetName As String
    Dim sConnection As String
    Dim oleExcelConnection As OleDbConnection

    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""
    oleExcelConnection = New OleDbConnection(sConnection)
    oleExcelConnection.Open()

    dtTablesList = oleExcelConnection.GetSchema("Tables")

    If dtTablesList.Rows.Count > 0 Then
        sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString
    End If

    dtTablesList.Clear()
    dtTablesList.Dispose()

    If sSheetName <> "" Then

        ' oleExcelCommand = oleExcelConnection.CreateCommand()
        ' oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
        ' oleExcelCommand.CommandType = CommandType.Text

        ' oleExcelReader = oleExcelCommand.ExecuteReader

        ' nOutputRow = 0

        While oleExcelReader.Read

        End While

        oleExcelReader.Close()

    End If

    oleExcelConnection.Close()

End Sub

Just about everything is underlined (error?) and I added the Dims (there were none) to see the effects.
But I can't get very far on my own. Maybe someone can help or point me to a working example?

And when a string says its used before assigned a value, must something be assigned when its dimmed ?

[jmcilhinney]

When you declare a variable, it doesn't refer to anything by default. It is Nothing. If you then try to use the value of that variable, you'll be warned that it doesn't have one. It's not necessarily wrong to do that but it is indicative of a problem or potential problem with the code and the warning is to let you know that you should check it. In your case, I'm guessing that the problem is here:

    Dim sSheetName As String

    '...

    If dtTablesList.Rows.Count > 0 Then
        sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString
    End If

    '...

    If sSheetName <> "" Then

In this case, you're only assigning a value to sSheetName if there are rows in the table, but you then use the value of sSheetName regardless. It looks like the logic is OK but the compiler isn't smart enough to know that for sure, so it warns you. Instead of what you're doing, look for the converse operation in the first If statement and exit the method if it's True:

If dtTablesList.Rows.Count = 0 Then
    Retrun
End If

sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString

This will mean that you aren't disposing your object if you hit that Return statement but you should be creating those objects with a Using statement in the first place, so they will always be implicitly disposed regardless.

[AlexanderBB]

Thank you for the info. Do you have an example of creating an object with a Using statement? That's new to me.

[jmcilhinney]

You should do some research on the subject anyway (should have done that first) but here's a simple example:

Using var As New SomeDisposableType
    'Use var here.
End Using

The object assigned to var will be disposed when you exit that block, regardless of how that exit occurs. It also doesn't matter how the variable is assigned. In this example I'm using a constructor but you could use a method that returned the object if that's appropriate. Any object that implements the IDisposable interface should be created and used this way if it is only needed in a narrow scope.

[Shaggy Hiker]

One of the nice things about the Using block was mentioned by JMC, but the importance of it might be missed: The Using block will correctly clean up the object regardless of how the exit occurred. That means that if the code inside the block throws an exception and jumps to an exception handler that is outside of the block, the object STILL gets cleaned up. That avoids a bunch of tedious code checking and disposing of objects in the case of an exception.

By the way, since you said that almost everything gets underlined, it is also possible that you are missing a reference or two. It depends a lot on what the error messages are. The stuff you are using seems to be things that get included by default into most applications, but it's still possible that a reference is missing. If what JMC suggested doesn't clear up a bunch of the underlines, then let us know what the error message associated with the underline is.

Also, if you are asking a question, then it is assumed that you don't know the answer, which means that everybody who asks a question is a newbie for that particular topic.

[AlexanderBB]

Yes I am and have been using Google for research.
Decided to change to and try as a test some known and working VBA code.

        Dim strSQL As String, conStr As String
        Dim cnn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim ExcelFile As String
        ExcelFile = "O:\Spreadsheet2024.xls"

        conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & ExcelFile & "';" &
             "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
        strSQL = "SELECT * FROM [Sheet2$] WHERE Account = '5300'"

        cnn.Open conStr
	rs.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

Dim cnn As New ADODB.Connection is underlined and tells me it's not defined.
Microsoft ActiveX Data Objects x.x Library is required and found in the Reference manager under COM.
It's ticked and OKd, but doesn't seem to have been added. It's not showing in any list and the Dim command
remains an error.
Is this the correct method to add a Reference? Should it have worked ?

[jmcilhinney]

Don't use ADO in VB.NET. Remove that reference and rewrite your data access code using ADO.NET. If you're targeting .NET Framework then System.Data.dll is probably already referenced and you can use types from the System.Data.OleDb namespace to work with an Access database or other Office data sources. If you're targeting .NET Core (including .NET 5 or later) then you should install the System.Data.OleDb NuGet package to access those types. There are lots of examples around of using ADO.NET in General and OLE DB for Excel. Note that you can use the Jet provider for old Office file formats like XLS, although only in a 32-bit app. If you have installed 64-bit Office or standalone ACE then that provider will only work in a 64-bit app.

I just looked back and saw that you were using ADO.NET to start with. Stick with that. Don't use old technology just because you have used it before. You're using a new language so you should use it properly, with the technology created for that language.

[kareninstructor]

First off, as mentioned, use .xlsx format rather than .xls.

To read a sheet, install this NuGet package, ExcelMapper.

Using ExcelMapper.

Create a class that represents columns in the worksheet to read then read the data into a list. Below is done in a console project but will work in Windows Forms.

Note, in the Fetch method, the second parameter is the worksheet name to read.

Imports Ganss.Excel

Module Program
    Sub Main()

        Const excelFile = "Products.xlsx"

        Dim excel As New ExcelMapper()
        Dim products As List(Of Products) = excel.
                Fetch(Of Products)(excelFile, "Products").
                ToList()

    End Sub
End Module

Full source for above.