Solved

change a file database to a dataset

Posted on 2008-10-23
8
184 Views
Last Modified: 2012-05-05
I've got this code i found which opens a file and loads the records into a records set. thats greate but it does not really do what i need as i need to really read from a dataset as i need to update the data in the dataset in realtime and use the dataset for calculations.

what i thought i would do is just change the sub values as follows

private sub opendatabase( byref data as dataset, byref dt as datatable)

and then use these but i'm pretty new to all this and did not get far.

regards


Allan
Public Sub OpenDatabase(ByVal FileName As String, ByVal Delimiter As String)
 
        'Requires 6 fields: Date, Open, High, Low, Close, Volume
        'using any delimiter.
 
        Dim nFile As Integer
        Dim Record As String
        Dim Found As Short
        Dim Index As Short
 
        On Error GoTo ErrHndl
 
        m_Recordset = New RecordSet
        m_Date = New Field
        m_Open = New Field
        m_High = New Field
        m_Low = New Field
        m_Close = New Field
        m_Volume = New Field
 
        'Get record count
        nFile = FreeFile()
        FileOpen(nFile, FileName, OpenMode.Input, , OpenShare.LockRead) 'Open file
        Do While Not EOF(nFile)
            Record = LineInput(nFile)
            Index = Index + 1
        Loop
        m_RecordCount = Index
        FileClose(nFile)
        Index = 0
 
        'Initialize Recordsets
        m_Date.initialize(m_RecordCount, "Date")
        m_Open.initialize(m_RecordCount, "Open")
        m_High.initialize(m_RecordCount, "High")
        m_Low.initialize(m_RecordCount, "Low")
        m_Close.initialize(m_RecordCount, "Close")
        m_Volume.initialize(m_RecordCount, "Volume")
 
        nFile = FreeFile()
        FileOpen(nFile, FileName, OpenMode.Input, , OpenShare.LockRead) 'Open file
 
        Do While Not EOF(nFile)
 
            Index = Index + 1
            Record = LineInput(nFile) 'Grab one line from file
 
            'Date
            Found = InStr(Record, Delimiter)
            If Found <> 0 Then
                m_Date.setStrValue(Index, Mid(Record, 1, Found - 1))
                Record = Mid(Record, Found + 1)
            End If
 
            'Open
            Found = InStr(Record, Delimiter)
            If Found <> 0 Then
                m_Open.setValue(Index, CDbl(Mid(Record, 1, Found - 1)))
                Record = Mid(Record, Found + 1)
            End If
 
            'High
            Found = InStr(Record, Delimiter)
            If Found <> 0 Then
                m_High.setValue(Index, CDbl(Mid(Record, 1, Found - 1)))
                Record = Mid(Record, Found + 1)
            End If
 
            'Low
            Found = InStr(Record, Delimiter)
            If Found <> 0 Then
                m_Low.setValue(Index, CDbl(Mid(Record, 1, Found - 1)))
                Record = Mid(Record, Found + 1)
            End If
 
            'Close
            Found = InStr(Record, Delimiter)
            If Found <> 0 Then
                m_Close.setValue(Index, CDbl(Mid(Record, 1, Found - 1)))
                Record = Mid(Record, Found + 1)
            End If
 
            'Volume
            m_Volume.setValue(Index, CDbl(Record))
 
            System.Windows.Forms.Application.DoEvents()
        Loop
 
        MoveFirst()
 
        m_Recordset.addField(m_Date)
        m_Recordset.addField(m_Open)
        m_Recordset.addField(m_High)
        m_Recordset.addField(m_Low)
        m_Recordset.addField(m_Close)
        m_Recordset.addField(m_Volume)
 
        FileClose(nFile) 'Close file
 
        Exit Sub
ErrHndl:
        FileClose(nFile)
        Err.Raise(1012, "OpenDatabase", Err.Description)
 
    End Sub

Open in new window

0
Comment
Question by:allanhutton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 

Author Comment

by:allanhutton
ID: 22789459
Okay i've work out how to do this with ADO but know that ado is not really the best approach to take in doing things quickly and is a little dated. what should i use oledb? but what do i need to do to change to oledb.

if you can help i would appreciated it.
   Public Sub OpenDatabase(ByVal Databasename As String, ByVal TableName As String)
 
        'Requires 6 fields: Date, Open, High, Low, Close, Volume
        'using any delimiter.
 
        Dim Record As String
        Dim Index As Integer
 
        On Error GoTo ErrHndl
 
        'Setup TA-SDK fields
        m_Recordset = New RecordSet
        m_Date = New Field
        m_Open = New Field
        m_High = New Field
        m_Low = New Field
        m_Close = New Field
        m_Volume = New Field
 
 
        'Connect to the database
        Dim cn As New sqlconnection
        Dim rs As New Sql
        Dim SQL As String = "SELECT * FROM " & TableName & " ORDER BY TickDate, HourInterval"
 
 
 
        cn.Open("Provider=sqloledb;Uid=;Pwd=;Server=127.0.0.1;Database=")
 
 
        rs.Open(SQL, cn, 1, 1)
 
 
        'Get record count
        m_RecordCount = rs.RecordCount
        Index = 0
 
        'Initialize Recordsets
        m_Date.initialize(m_RecordCount, "Date")
        m_Open.initialize(m_RecordCount, "Open")
        m_High.initialize(m_RecordCount, "High")
        m_Low.initialize(m_RecordCount, "Low")
        m_Close.initialize(m_RecordCount, "Close")
        m_Volume.initialize(m_RecordCount, "Volume")
 
 
        Do While Not rs.EOF
 
            Index = Index + 1
 
            'Date
            m_Date.setStrValue(Index, rs.Fields("TickDate").Value & " " & rs.Fields("HourInterval").Value)
 
            'Open
            m_Open.setValue(Index, rs.Fields("Open").Value)
 
            'High
            m_High.setValue(Index, rs.Fields("High").Value)
 
            'Low
            m_Low.setValue(Index, rs.Fields("Low").Value)
 
            'Close
            m_Close.setValue(Index, rs.Fields("Close").Value)
 
            'Volume
            'm_Volume.setValue Index, rs.Fields("Volume").Value
 
            rs.MoveNext()
 
        Loop
 
 
        MoveFirst()
 
        m_Recordset.addField(m_Date)
        m_Recordset.addField(m_Open)
        m_Recordset.addField(m_High)
        m_Recordset.addField(m_Low)
        m_Recordset.addField(m_Close)
        m_Recordset.addField(m_Volume)
 
        GoTo CleanUp
 
        Exit Sub
ErrHndl:
        Err.Raise(1105, "Database.OpenDatabase", "An unexpected error has occured")
CleanUp:
        rs.Close()
        cn.Close()
        rs = Nothing
        cn = Nothing
 
 
    End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:Priest04
ID: 22793881
ADO is definetly not the right way. Using DataTable is quite easy, but I am not sure whay exactly are you doing here. In the 1st example, you open a file, in a second example, you dont. Can you be more specific about what exactly are you after?

Goran
0
 

Author Comment

by:allanhutton
ID: 22794418
the first example is the code used by the library i got to load data. they don't have an example that will read from a dataset. so i've tried to modify it to read from a database using adodb but i dont think using adodb was the way to go but i'm not to sure how to produce the same results with a dataset.

regards
Allan

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Accepted Solution

by:
Priest04 earned 500 total points
ID: 22797182
I will post an example to give you the idea how it works.
Dim dt As New DataTable() ' create object DataTable
 
' create columns in DataTable that will hold data
dt.Columns.Add("date_field", GetType(DateTime)) ' Change to Appropriate field type
dt.Columns.Add("open_field", GetType(String)) ' Change to Appropriate field type
dt.Columns.Add("high_field", GetType(String)) ' Change to Appropriate field type
dt.Columns.Add("low_field", GetType(String)) ' Change to Appropriate field type
dt.Columns.Add("close_field", GetType(String)) ' Change to Appropriate field type
dt.Columns.Add("volume_field", GetType(Double)) ' Change to Appropriate field type
 
Dim dr As DataRow
 
Do While Not EOF(nFile)
    dr = dt.Rows.NeWRow()
    dr("date_field) = date_value ' replace date_value with value read from file
    dr("open_field) = date_value ' replace open_value with value read from file
    dr("high_field) = date_value ' replace high_value with value read from file
    dr("low_field) = date_value ' replace low_value with value read from file
    dr("close_field) = date_value ' replace close_value with value read from file
    dr("volume_field) = date_value ' replace volume_value with value read from file
 
    dt.Rows.Add(dr)
Loop
 

Open in new window

0
 
LVL 18

Expert Comment

by:Priest04
ID: 22797201
Sorry, I made a typing mistake, all code that assigns a value to DataRow field is missing double quote ". Example

dr("date_field") = date_value ' replace date_value with value read from file
0
 

Author Comment

by:allanhutton
ID: 22797366
instead of dr=dt.rows.newrow() generates a syntax error  is it just dr=dt.newrow()

finally how do i iterate through each record in the datatable

regards
allan
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22797628
Yes, you are correct, I was typing from head. :) To iterate through each row


For Each dr As DataRow In dt.Rows
    MessageBox.Show(dr("date_field").ToString())
Next

Open in new window

0
 

Author Closing Comment

by:allanhutton
ID: 31509224
thats for helping me work through the problem and asking further questions to get to this problem resolve.

cheers
Allan
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question