What's a System.__ComObject? Where's my SQL Server value?

[woollymammoth]

I can't assign a MS SQL Server table record value to a simple VB variable, should be a really easy thing. Sample SQL Server table has the data in the record as a char(30) string, the column for that record is named "Try". The VB script function accessing the record value is running on a web hosting server with ASP.Net 2.0 on a web page. Result always is it "can't convert from a Field to type 'String'". Printing out the SQL variable results "System.__ComObject". I know this is a wrapper, but how do I get in and access my value?

I have tried CAST and CONVERT SQL commands. Also, if I create a Gridview and tie the data source to the SQL database, it displays the table values just fine, but I need to apply the values to a formula and can't use the raw values from the SQL table so this won't work for me. Going the opposite way, setting the SQL table value to any VB value always works good. Is ASP.Net 2.0 overtaking the Com wrapper?

Anyone have ideas on how to make this go away?

<script runat="server">
  Dim SampleString as String 'here's where we want the string data from the SQL table
 
  Public Sub SaveSignals()
    Dim Connection1 As Object
    Dim MyTable As Object
    Dim Query As String
    Dim ConnectionString As String
 
    ConnectionString = "Provider=SQLOLEDB;" & _
          "Data Source=server;" & _
          "Initial Catalog=tablename;" & _
          "User ID=userid;" & _
          "Password=pw1234"
    Connection1 = CreateObject("ADODB.Connection") 'make a connection
    Connection1.open(ConnectionString)
 
    MyTable = CreateObject("ADODB.Recordset") 'make a table object
    Query = "SELECT * FROM tablename" 'SQL command to open up the named table, get 1st record
    MyTable.CursorType = 2 'adOpenDynamic
    MyTable.LockType = 2 'adLockOptimistic
    MyTable.open(Query, Connection1) 'opens up the table using the SQL connection
    MyTable.MoveFirst() 'goes to the first record, everything works great up to here
 
 
'problem here
 
    'assign a SQL table value to my VB string variable    
    SampleString=MyTable("Try") '<------ Results in error at web server:
      'Exception Details: System.InvalidCastException: 
      'Conversion from type 'Field' to type 'String' is not valid.
 
    'just for kicks, print out the value to the web page.    
    Response.Write(MyTable("Try")) '<------ so if we comment out the above line so the server is ok,
      'the server prints out: "System.__ComObject" on the web page, instead of my string value
 
 
 
    'when we get here, attempt to put any string value back into the SQL table    
    MyTable("Try") = "TestMe" 'any data, from any string source,
      ' going back to the database works great, no problems at all.
 
    MyTable.update()  ' Save changes, always successful
 
    MyTable.Close()   ' Close table.
    Connection1.Close()   ' Close table. Success always.
  End Sub
</script>
 

[Joshua Dannemann]

Hi, I ran into the same problem and was able to fix it by changing this:

SampleString=MyTable("Try")

to this:

SampleString=MyTable.Fields.Item("Try") .Value

I can't figure out why the syntax needs to be different, but whatever the reasons this seems to work just fine.