Solved

change a file database to a dataset

Posted on 2008-10-23
8
178 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now