for each item in collection loop does not work for datatable

Mar 6, 2014 at 4:51 PM
Edited Mar 6, 2014 at 4:53 PM
Hi.

The code:
Imports System.Data.SqlClient
Imports Microsoft.ClearScript

Public Class WebForm1
    Inherits System.Web.UI.Page

    Public vbs As Windows.VBScriptEngine = New Windows.VBScriptEngine

    Public conn As SqlConnection = New SqlConnection("connection string")
    Public da As SqlDataAdapter = New SqlDataAdapter("select top 10 * from table", conn), dt As DataTable = New DataTable

    Public Sub Initialize()
        js.AddRestrictedHostObject("me", HostItemFlags.GlobalMembers, Me)
        vbs.AddRestrictedHostObject("me", HostItemFlags.GlobalMembers, Me)

        Try
            conn.Open()

            vbs.Execute("da.Fill dt")
            vbs.Execute("for i = 0 to dt.Rows.Count - 1:for j = 0 to dt.rows.item(i).table.columns.count - 1:response.write dt.rows.item(i).item(j) & "" - "":next:response.write ""<br>"":next")

            ' ------------------------------------------------------------
        ' THIS DOES NOT WORK
        vbs.Execute("for each row in dt.rows: for each col in row.table.columns: response.write row(col.columnname).tostring: next: next")
            ' ------------------------------------------------------------

        Catch ex As Exception
            Response.Write(ex)
        Finally
            da.Dispose()
            dt.Clear()
            dt.Dispose()
            conn.Close()
            conn.Dispose()
        End Try
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
End Class
When I use for i = 0 to object.count - 1 it works, but the second line where I use for each loop it gives me this error:

Microsoft.ClearScript.ScriptEngineException: Object not a collection at Microsoft.ClearScript.ScriptEngine.ThrowScriptError(IScriptEngineException scriptError) in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\ScriptEngine.cs:line 840 at Microsoft.ClearScript.Windows.WindowsScriptEngine.ThrowScriptError(Exception exception) in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\Windows\WindowsScriptEngine.cs:line 642 at Microsoft.ClearScript.Windows.WindowsScriptEngine.<>c__DisplayClass14.b__13() in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\Windows\WindowsScriptEngine.cs:line 611 at Microsoft.ClearScript.ScriptEngine.ScriptInvoke(Action action) in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\ScriptEngine.cs:line 800 at Microsoft.ClearScript.Windows.WindowsScriptEngine.ScriptInvoke(Action action) in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\Windows\WindowsScriptEngine.cs:line 603 at Microsoft.ClearScript.Windows.WindowsScriptEngine.Execute(String documentName, String code, Boolean evaluate, Boolean discard) in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\Windows\WindowsScriptEngine.cs:line 523 at Microsoft.ClearScript.ScriptEngine.Execute(String documentName, Boolean discard, String code) in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\ScriptEngine.cs:line 494 at Microsoft.ClearScript.ScriptEngine.Execute(String documentName, String code) in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\ScriptEngine.cs:line 473 at Microsoft.ClearScript.ScriptEngine.Execute(String code) in c:\Users\Shripal\Documents\Visual Studio 2012\Projects\ClearScript\ClearScript\ScriptEngine.cs:line 454 at WebApplication1.WebForm1.Initialize() in C:\Users\Shripal\Documents\Visual Studio 2012\Projects\WebApplication1\WebApplication1\WebForm1.aspx.vb:line 37
Coordinator
Mar 6, 2014 at 10:19 PM
Hi shripaldalal,

VBScript's for each statement only works with native VBScript arrays and specially crafted COM objects. ClearScript exposes .NET objects by encasing them in COM-aware wrappers, but it doesn't emulate VBScript collections. That would be an interesting feature to add!

Thanks!
Mar 6, 2014 at 11:48 PM
Edited Mar 7, 2014 at 4:13 AM
Hi,

Ok I tried it in JScript and it does not work there too. I tried to:
Public row as DataRow
Public col as DataColumn 
But this too does not help. The error then comes as Invalid Field Assignment.

Does this work in V8 ? Please advise.
Mar 6, 2014 at 11:50 PM
Edited Mar 7, 2014 at 3:49 AM
I believe Microsoft should make a real scripting solution without COM Interop and Reflection or CodeDom it would be a sell out. The MS Script Control was so powerful we could do so many things with it. That's the reason I never moved onto .NET for so many years because there was never proper host scripting support. It won't even take them a lot of time. I wish they take serious notice of this issue.
Coordinator
Mar 7, 2014 at 11:05 PM
Hi shripaldalal,

Ok I tried it in JScript and it does not work there too.

Sorry, what doesn't work? You said above that you could iterate through the data by using the Count and Item properties. The same technique should work with JScript and V8. The only thing that doesn't work is VBScript's for each statement, and that's because VBScript doesn't recognize those objects as collections.

What are you trying to do that yields "Invalid field assignment"?

The MS Script Control was so powerful we could do so many things with it.

Other than the bug you found that affects SqlDataReader (thanks again for that, BTW!), what are you having trouble with?
Mar 8, 2014 at 12:43 PM
Edited Mar 8, 2014 at 1:23 PM
I tried this in JScript and it does not work (same as VBScript):
js.Execute("for(row in dt.Rows) { for(col in row.Table.Columns) { Response.Write row(col.ColumnName).ToString(); } }")
Can this work in V8 ? I don't mind using Count and Item properties for iteration but I would prefer this more.

If I declare row and col (the variables I use in the engine) initially as then I get error invalid assignment:
Public row as DataRow
Public col as DataColumn 

Public Sub Initialize()

js.AddRestrictedHostObject("me", HostItemFlags.GlobalMembers, Me)
js.Execute("for(row in dt.Rows) { for(col in row.Table.Columns) { Response.Write row(col.ColumnName).ToString(); } }")

End Sub
If I dont declare the variables it gives error as: Object not a collection at Microsoft.ClearScript.ScriptEngine
Coordinator
Mar 9, 2014 at 3:36 PM
Hello shripaldalal,

Can this work in V8 ? I don't mind using Count and Item properties for iteration but I would prefer this more.

JavaScript's for/in statement does work, but it doesn't do what you think it does. It iterates through the names of an object's properties. For a .NET DataRowCollection, it returns "Add", "Clear", "Contains", etc. It returns strings, and since you can't assign a string to a .NET property of type DataRow, you get "Invalid field assignment".

In general, script constructs such as JavaScript's for/in and VBScript's for each were designed for script object models that are very different from .NET's. ClearScript aims to provide full access to .NET objects and types; it doesn't (and often can't) automatically create ideal APIs for specific script languages.

That said, it might be possible to expose .NET collections to VBScript's for each. We're looking into it.

If I dont declare the variables it gives error as: Object not a collection at Microsoft.ClearScript.ScriptEngine

"Object not a collection" is a VBScript-specific error message. Are you sure you're seeing it when using JScript?

Thanks!