CODEWITHSUNDEEP
vb.netms-accessdao
eric schmidt
eric schmidt

Reputation: 19

vb.net access database

I am trying to read fields from a query into a text string arrary. In vb6 I can simply declare the array and then read the fields into it without it caring what type of values are in it. Now when I try to do that same thing I get an "unable to cast com object of type 'dao.fieldclass' to type 'system.string". Do I need to read the field value into a seperarte variable and then convert it to a string? The seqNum is what I am having the problem with 

Public dbEngine As dao.DBEngine
Public db As dao.Database, recSet As dao.Recordset
dbEngine = New dao.DBEngine
Dim seqNum As Long
scExportTemplatePath = "M:\robot\scTemplates\"
db = dbEngine.OpenDatabase(scExportStuffPath & "scExport.mdb")
tsOut = fso.CreateTextFile(wildePath & dte & "-" & fle.Name & ".csv", True)
With recSet
    .MoveFirst()
    Do While Not .EOF
        seg = .Fields("segmentID")
        If seg <> segHold Then
            seqNum = 1
        End If
        arrOut(0) = .Fields("jobnum_AM")
    Loop
End With

Upvotes: 0

Views: 1971

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112259

You have several problems with this code. In addition to the points mentioned by Jeremy:

  1. What was Long in VB6 is now Integer in VB.NET. Long is a 64 bit integer now.

  2. Use System.IO.Path.Combine in order to combine path strings. Combine automatically adds missing backslashes and removes superfluous ones. Path.Combine(scExportTemplatePath, "scExport.mdb")

  3. The Field property does not have a default property any more. Non-indexed properties are never default properties in VB.NET. Get the field value with .Fields("segmentID").Value.

  4. Convert its value to the appropriate type: seg = Convert.ToInt32(.Fields("segmentID").Value)
    Note: VB's Integer type is just an alias for System.Int32.

  5. You are always adding to the same array field. I don't know exactly what you have in mind. If you want to add one field only, you could just use a List(Of String). If you are adding several fields for each record, then a List(Of String()) (i.e. a list of string arrays) would be appropriate. Lists have the advantage that they grow automatically.

Dim list As New List(Of String())
Do While Not .EOF
    Dim values = New String(2) {}
    values(0) = Convert.ToString(.Fields("field_A").Value)
    values(1) = Convert.ToString(.Fields("field_B").Value)
    values(2) = Convert.ToString(.Fields("field_C").Value)
    list.Add(values)
    recSet.MoveNext()
Loop

But it is more comprehensible, if you create a custom class for storing your field values:

Console.WriteLine("{0} {1} ({2})", user.FirstName, user.LastName, user.DateOfBirth)

... reads much better than:

Console.WriteLine("{0} {1} ({2})", values(0), values(1), values(2))

In VB.NET you have other possibilities to work with databases:

Dim list As New List(Of String())
Using conn = New OleDb.OleDbConnection( _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyPath\MyDb.mdb")
    Dim sql = "SELECT myStuff FROM myTable"
    Dim command = New OleDbCommand(sql, conn)
    conn.Open()
    Using reader As OleDbDataReader = command.ExecuteReader()
        While reader.Read()
            Dim values = New String(reader.FieldCount - 1) {}
            For i = 0 To reader.FieldCount - 1
                values(i) = Convert.ToString(reader.GetValue(i))
            Next
            list.Add(values)
        End While
    End Using
End Using

Note that the Using statement closes the resources automatically at the end. Even if an error occurs and the code is terminated prematurely.

In VB.NET you can write to files like this (without using fso, which is not .NET like)

Using writer As New StreamWriter("myFile.txt", False)
    writer.WriteLine("line 1")
    writer.WriteLine("line 2")
    writer.WriteLine("line 3")
End Using

Upvotes: 2

Jeremy Thompson
Jeremy Thompson

Reputation: 65534

1) You dont show how you open the Recordset, eg: 

Set recSet = db.OpenRecordset("query_name or SQL")

2) You dont have a .MoveNext in the While Loop: 

With recSet
 .MoveFirst()
  Do While Not .EOF
  seg = .Fields("segmentID")
    If seg <> segHold Then
     seqNum = 1
    End If
    arrOut(0) = .Fields("jobnum_AM")
    .MoveNext()
  loop

Upvotes: 1

Related Questions