Option Strict disallows late binding - Add-in Express .NET forum

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Hi, Andrei

I have developed a Sub attached to an ADX Button, which does the following actions:

(1) Connects to the SQL Server using ADODB connection
(2) Runs a Stored Procedure (kept on Server)
(3) Returns the results to a RecordSet (memory location) on my local computer
(4) Copies the data from RecordSet to my Excel Sheet.
(5) Copies the field headers from RecordSet to the 1st row of my Excel Sheet.

The Sub runs well when the ADX Button is clicked. I was pleasantly surprised how fast the job was done.
There is only one problem: It's step (5).

The program works perfectly if I set Option Strict Off.

But if I set Option Strict On, the program crashes at the word "Name":
Error Msg: COM Exception was unhandled by user code

For icols = 0 To recset.Fields.Count - 1
  'xlWsht.Cells(1, icols + 1).value = recset.Fields(icols).Name       '// original code
   xlWsht.Range(sheetCells(1, icols + 1)).Value = recset.Fields(icols).Name
Next

I spent 2 days trying to solve the problem. Also sent a question on an Excel Forum. Hopeless!

Could you help me sort out the problem?
Even if you could give me some tips, I could continue trying.

Thanks
Leon

===============================================================================================
In case you need it, here is my complete code.
===============================================================================================

Option Strict On
Imports System.Runtime.InteropServices
Imports System.Text
Imports System.ComponentModel
Imports AddinExpress.MSO
Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.DataSet


Private Sub AdxRibbonButton1_OnClick(ByVal sender As System.Object, _
ByVal control As AddinExpress.MSO.IRibbonControl, ByVal pressed As System.Boolean) _
Handles AdxRibbonButton1.OnClick


   Dim Conn As New ADODB.Connection
   Dim recset As New ADODB.Recordset

   Dim sqlQry As String, sConnect As String
   Dim sheets As Excel.Worksheets = TryCast(ExcelApp.Worksheets, Excel.Worksheets)
   Dim xlWsht As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
   Dim sheetCells As Excel.Range = TryCast(xlWsht.Cells, Excel.Range)
   Dim icols As Integer


        xlWsht.Cells.ClearContents()

        sqlQry = "EXECUTE[dbo].[MyStoredProcedure]"

        sConnect = "Driver=SQL Server;Server=MyServer; Database=NEWDB; _
        User Id = sa; Password= 12345"

        Conn.Open(sConnect)
        recset = New ADODB.Recordset

        recset.Open(sqlQry, Conn)


        For icols = 0 To recset.Fields.Count - 1
            'xlWsht.Cells(1, icols + 1).value = recset.Fields(icols).Name  '// original
            xlWsht.Range(sheetCells(1, icols + 1)).Value = recset.Fields(icols).Name

            '// PROBLEM IS WITH ABOVE LINE: AT THE WORD "Name"
        Next    

        

            '// I TRIED THE FOLL. CODES INSTEAD OF For ... Next. WORK WELL!!!
            'xlWsht.Range("A1").Value = recset.Fields(0).Name
            'xlWsht.Range("B1").Value = recset.Fields(1).Name
            'xlWsht.Range("C1").Value = recset.Fields(2).Name
            'xlWsht.Range("D1").Value = recset.Fields(3).Name
            'xlWsht.Range("E1").Value = recset.Fields(4).Name
            'xlWsht.Range("F1").Value = recset.Fields(5).Name
            'xlWsht.Range("G1").Value = recset.Fields(6).Name

      xlWsht.Range("A2").CopyFromRecordset(recset)

      recset.Close()

      Conn.Close()
      recset = Nothing


    End Sub

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

Hello Leon,

C# doesn't have VB-style late binding. I felt this pain when switching from VBA to C#. I believe you understand how difficult is to write this construct (it deals with the Outlook object model):

mail.GetType().InvokeMember("BodyFormat", Reflection.BindingFlags.SetProperty, Nothing, mail, New Object() {mailFormat})

With Option Strict Off, this construct can be simplified down to this one:

mail.BodyFormat = mailFormat

Remember that late binding in VB.NET allows writing code faster. But, regardless of the language used, late binding moves problems from the code-creating phase to the code-testing phase. This is commonly assumed that fixing an error on the code-creating phase is 10-100 times cheaper than fixing an error on the code-testing phase.

As to your issue, it has a bit different root. In COM, every COM interface (such as ADODB.Fields) has the default member. I think this idea was introduced because people want to *write* code faster (but remember about the cost). Anyway, VBA allows you to skip the default member. For example

recset.Fields(icols).Name

this actually means:

recset.Fields.Item(icols).Name

Check this. I don't allow myself skipping default members when writing in VBA; this is sort of psychological training, I believe.

Since we talk about VBA and default members, there's one more potential issue; it mostly relates to VBA coding. In VBA you call a method and pass it a parameter in one of two ways:

MyMethod aParameter
Call MyMethod(aParameter)

Note the brackets. If you write MyMethod(aParameter) - i.e. without using "Call", the actual parameter passed to the method is "aParameter.{default member}". For example, if aParameter is of the ADODB.Recordset type, the actual value passed to the method is of the ADODB.Fields type (since Fields is the default member of the ADODB.Recordset interface). Isn't this nice?

Default members are marked in the VBA Object Browser: 1) open the VBA IDE - in the host application, click Visual Basic on the Developer tab or simply press {Alt+F11}; and 2) choose menu View | Object Browser or press {F2}.

Andrei Smolin
Add-in Express Team Leader

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Andrei,

Thanks for your explanations.

As I mentioned, the foll. code work perfectly (even with Option Strict On)

xlWsht.Range("A1").Value = recset.Fields.Item(0).Name
xlWsht.Range("B1").Value = recset.Fields.Item(1).Name
xlWsht.Range("C1").Value = recset.Fields.Item(2).Name
xlWsht.Range("D1").Value = recset.Fields.Item(3).Name
xlWsht.Range("E1").Value = recset.Fields.Item(4).Name
xlWsht.Range("F1").Value = recset.Fields.Item(5).Name
xlWsht.Range("G1").Value = recset.Fields.Item(6).Name

I could use the hardcoded snippet, but I don't know how many fields there are.

If only I could do a For ... Next Loop, something like below, my problem would be solved:

xlWsht.Range(sheetCells(1, icols + 1)).Value = recset.Fields(icols).Name

But I have to introduce "Cells" and this is when problems start.

Any idea?

Leon

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

Try this one:

sheetCells.Range(1, icols + 1).Value = recset.Fields.Item(icols).Name

If this doesn't work, check what part of this statement breaks: the left one? the right?

Andrei Smolin
Add-in Express Team Leader

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

I tried your code with fixed values as parameters as foll:

sheetCells.Range(1, 1).Value = recset.Fields.Item(0).red

The Statement breaks at red.

This doesn't work even if I set Option Explicit Off.

A fancy question:
Is there a way to discover what code Visual Studio actually creates behind the scenes if we set Option Explicit Off?
I could then "steal" that code so that it works when I set Option Explicit On.

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

So, this occurs at the run time, not at the compile time? If so, does your recset contains any fields at all? Doesn't your query need to have a space after "EXECUTE"?

Andrei Smolin
Add-in Express Team Leader

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

No compile-time errors. Seems to be runtime.

My query (Stored Procedure) is OK.
It returns all the data I need (except the headers). I don't know why Microsoft has chosen to leave out the headers when we use "CopyfromRecordset".

So, what I am trying to do is to copy the headers as a separate exercise.
As soon as I set Option Strict On, problems begin. The incriminated word is "Name".

And this is what is giving me a headache.

Note that the program works perfectly (both headers and data generated are copied to Excel) if I set Option Strict Off.

As a sidenote (just sharing!), why did I chose the ADODB method of connection, and using CopyFromRecordset?

(1) Lightning fast! 2 sec after I clicked the button, 5000 records were displayed on my Excel Sheet.
(2) It uses the disconnected model. More than 20 staff will work continuously on my add-in. Traffic not blocked.
(3) I can delete the recordset once data is retrieved. So memory is not blocked.

As soon as I set Option Strict On, problems begin. The incriminated word is "Name".

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

Leon Lai Kan writes:
As soon as I set Option Strict On, problems begin. The incriminated word is "Name".

In this topic you also said:

Leon Lai Kan writes:
As I mentioned, the foll. code work perfectly (even with Option Strict On)

xlWsht.Range("A1").Value = recset.Fields.Item(0).Name
xlWsht.Range("B1").Value = recset.Fields.Item(1).Name
xlWsht.Range("C1").Value = recset.Fields.Item(2).Name
xlWsht.Range("D1").Value = recset.Fields.Item(3).Name
xlWsht.Range("E1").Value = recset.Fields.Item(4).Name
xlWsht.Range("F1").Value = recset.Fields.Item(5).Name
xlWsht.Range("G1").Value = recset.Fields.Item(6).Name

Does this code fragment still works for you?

Andrei Smolin
Add-in Express Team Leader

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Hi, Andrei

I finally sorted out my problem.

The lines causing the problem should be rewritten as follows:

For icols = 0 To recset.Fields.Count - 1
   TryCast(xlWsht.Range("1:1").Cells(1, icols + 1), Excel.Range) _
                                          .Value = recset.Fields.Item(icols).Name
Next

That's all, but it took me 3 days and nights to sort out, because the Error Messages in Visual Studio were not explicit enough.

I thank you for your time. Your answers did help me towards a solution.

For the benefit of other people who may find my code useful, I share my complete code below:

Option Strict On
Imports System.Runtime.InteropServices
Imports System.ComponentModel
Imports AddinExpress.MSO
Imports System.Data.SqlClient
Imports System.Data

Private Sub AdxRibbonButton1_OnClick(ByVal sender As System.Object, _
     ByVal control As AddinExpress.MSO.IRibbonControl, ByVal pressed As System.Boolean) _
     Handles AdxRibbonButton1.OnClick

        Dim Conn As New ADODB.Connection
        Dim recset As New ADODB.Recordset
        Dim sqlQry As String, sConnect As String
        Dim xlWsht As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
        Dim icols As Integer

        xlWsht.Cells.ClearContents()

        '// CONNECT TO SERVER, RUN STORED PROCEDURE, DUMP RESULTS TO RECORDSET
        sqlQry = "EXECUTE[dbo].[usp_MyStoredProcedure]"

        sConnect = "Driver=SQL Server;Server=MyServer; _
                                Database=MyDataBase; User Id = sa; Password= 12345"
        Conn.Open(sConnect)

        recset = New ADODB.Recordset
        recset.Open(sqlQry, Conn)


        '// COPY HEADERS FROM RECORDSET TO EXCEL SHEET
        For icols = 0 To recset.Fields.Count - 1
            TryCast(xlWsht.Range("1:1").Cells(1, icols + 1), Excel.Range) _
                                               .Value = recset.Fields.Item(icols).Name
        Next

        '// COPY ALL DATA FROM RECORDSET TO EXCEL SHEET (CELL A2)
        xlWsht.Range("A2").CopyFromRecordset(recset)

        recset.Close()
        Conn.Close()
        recset = Nothing

    End Sub

Best Regards
Leon

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

Hello Leon,

Thank you. Note that setting cells one by one may be very slow if the number of cells is great. The fastest way is to specify a Range containing all the cells to be changed and call Range.Value = {an array of values}. Find an example at https://www.add-in-express.com/creating-addins-blog/2011/09/29/excel-read-update-cells/.

Andrei Smolin
Add-in Express Team Leader