[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Data being rounded when added to SQL Express 2005 table...

I am having an issue when adding data to a table using Visual Studio 2008 (Visual Basic) and SQL Server Express 2005.

When I add the decimal data values, they show up in the table as being rounded up.

Here is what I am using to read and enter the data into the table.

Public Class LoadDB

    Public Function AddData(ByVal strDirectory As String) As Boolean
        Dim MyCSVReader As System.IO.StreamReader

        'get array of CSV files
        Dim dir As New DirectoryInfo(strDirectory)
        Dim files As FileInfo() = dir.GetFiles("*.csv")

        'data holders 
        Dim eodSymbol As String
        Dim eodDate As Date
        Dim eodOpen As Decimal
        Dim eodHigh As Decimal
        Dim eodLow As Decimal
        Dim eodClose As Decimal
        Dim eodVolume As Decimal
        Dim eodDayOfWeek As String



        'open the db...
        Using con As New SqlConnection
            con.ConnectionString = "Data Source=.\sqlexpress;Database=EOD;Integrated Security=SSPI;"

            con.Open()

            'for each CSV file, add its contents to the database
            For Each File As FileInfo In files

                'open the CSV file
                MyCSVReader = My.Computer.FileSystem.OpenTextFileReader(File.FullName)

                '   for each line in the CSV file....
                Do While Not MyCSVReader.EndOfStream '.....Repeat until no more data 

                    'get a line of data
                    Dim MyFieldInput() As String = Split(MyCSVReader.ReadLine, ",")

                    'if header then go to next line...
                    If LCase(Trim(MyFieldInput(0))) <> "symbol" Then
                        Try
                            'get the data field values
                            eodSymbol = LCase(Trim(MyFieldInput(0)))
                            eodDate = DateTime.Parse(MyFieldInput(1))
                            eodOpen = LCase(Trim(MyFieldInput(2)))
                            eodHigh = LCase(Trim(MyFieldInput(3)))
                            eodLow = LCase(Trim(MyFieldInput(4)))
                            eodClose = LCase(Trim(MyFieldInput(5)))
                            eodVolume = LCase(Trim(MyFieldInput(6)))
                            eodDayOfWeek = LCase(Trim(eodDate.ToString("ddd")))

                            'try and add symbol in symbol table
                            Try
                                Dim InsertStatement As String = "INSERT Symbols (Symbol) VALUES (@Symbol)"

                                Dim InsertCommand As New SqlCommand(InsertStatement, con)
                                InsertCommand.Parameters.AddWithValue("@Symbol", eodSymbol)

                                Dim result As Integer = InsertCommand.ExecuteNonQuery

                                If result < 1 Then
                                    'there was an error inserting the data
                                End If

                            Catch ex As Exception
                                'just ignore it...
                            End Try

                            'Try and add date to Dates table (date table for counting advanced/declined, dow averages, volume traded, etc. each day)
                            Try
                                Dim InsertStatement As String = "INSERT Dates (Date, DayOfWeek) VALUES (@Date, @DayOfWeek)"

                                Dim InsertCommand As New SqlCommand(InsertStatement, con)
                                InsertCommand.Parameters.AddWithValue("@Date", eodDate)
                                InsertCommand.Parameters.AddWithValue("@DayOfWeek", eodDayOfWeek)

                                Dim result As Integer = InsertCommand.ExecuteNonQuery

                                If result < 1 Then
                                    'there was an error inserting the data
                                End If
                            Catch ex As Exception
                                'just ignore it...
                            End Try

                            'try and add EOD data to EOD table (all data in a single table)
                            Try
                                Dim InsertStatement As String = "INSERT into EOD (Symbol, Date, DayOfWeek, [Open], High, Low, [Close], Volume) VALUES (@Symbol, @Date, @DayOfWeek, @Open, @High, @Low, @Close, @Volume)"

                                Dim InsertCommand As New SqlCommand(InsertStatement, con)
                                InsertCommand.Parameters.AddWithValue("@Symbol", eodSymbol)
                                InsertCommand.Parameters.AddWithValue("@Date", eodDate)
                                InsertCommand.Parameters.AddWithValue("@DayOfWeek", eodDayOfWeek)
                                InsertCommand.Parameters.AddWithValue("@Open", eodOpen)
                                InsertCommand.Parameters.AddWithValue("@High", eodHigh)
                                InsertCommand.Parameters.AddWithValue("@Low", eodLow)
                                InsertCommand.Parameters.AddWithValue("@Close", eodClose)
                                InsertCommand.Parameters.AddWithValue("@Volume", eodVolume)

                                Dim result As Integer = InsertCommand.ExecuteNonQuery
                            Catch ex As Exception
                                'just ignore it...
                                MessageBox.Show("Error adding EOD", "Error addding to EOD table")
                            End Try
                        Catch

                        End Try
                    End If
                Loop

                'close CSV file
                MyCSVReader.Close() '.....Close the StreamReader 

            Next 'next CSV file

            'close the db connection...
            con.Close()

        End Using


    End Function

Open in new window


Attached is the CSV file that I am using.

This is what the table looks like...

 data types highlighted
And this is what the data loaded into the table looks like (rounded)...

 data as showing in table
I need the data to be in the table with its decimal values also.  I am reading 54.75 from the CSV file, but, when I insert it into the table, I see 55.

Any help that you could give would be greatly appreciated.
0
cerksees
Asked:
cerksees
  • 3
1 Solution
 
cerkseesAuthor Commented:
I forgot to attach the CSV file....sorry.  Here it is....
NYSE-20010101.csv
0
 
cerkseesAuthor Commented:
Ummm....nevermind.

Te problem is that the table specifies 18,0 for the data types.  That was supposed to read 10,5.
0
 
cerkseesAuthor Commented:
I just saw my mistake in the code.  Oops.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi, first, change this:

eodOpen = LCase(Trim(MyFieldInput(2)))
eodHigh = LCase(Trim(MyFieldInput(3)))
eodLow = LCase(Trim(MyFieldInput(4)))
eodClose = LCase(Trim(MyFieldInput(5)))
eodVolume = LCase(Trim(MyFieldInput(6)))

Open in new window


With this:
eodOpen = Convert.ToDecimal(Trim(MyFieldInput(2)))
eodHigh = Convert.ToDecimal(Trim(MyFieldInput(3)))
eodLow = Convert.ToDecimal(Trim(MyFieldInput(4)))
eodClose = Convert.ToDecimal(Trim(MyFieldInput(5)))
eodVolume = Convert.ToDecimal(Trim(MyFieldInput(6)))

Open in new window



Then change this:
InsertCommand.Parameters.AddWithValue("@Open", eodOpen)
InsertCommand.Parameters.AddWithValue("@High", eodHigh)
InsertCommand.Parameters.AddWithValue("@Low", eodLow)
InsertCommand.Parameters.AddWithValue("@Close", eodClose)
InsertCommand.Parameters.AddWithValue("@Volume", eodVolume)

Open in new window


With this:
Dim paramOpen As SqlParameter = InsertCommand.Parameters.Add("@Open", SqlDbType.Decimal)
paramOpen.Precision = 18
paramOpen.Scale = 0
paramOpen.Value = eodOpen

Dim paramHigh As SqlParameter = InsertCommand.Parameters.Add("@High", SqlDbType.Decimal)
paramHigh.Precision = 18
paramHigh.Scale = 0
paramHigh.Value = eodHigh

Dim paramLow As SqlParameter = InsertCommand.Parameters.Add("@Low", SqlDbType.Decimal)
paramLow.Precision = 18
paramLow.Scale = 0
paramLow.Value = eodLow

Dim paramClose As SqlParameter = InsertCommand.Parameters.Add("@Close", SqlDbType.Decimal)
paramClose.Precision = 18
paramClose.Scale = 0
paramClose.Value = eodClose

Dim paramVolume As SqlParameter = InsertCommand.Parameters.Add("@Volume", SqlDbType.Decimal)
paramVolume.Precision = 18
paramVolume.Scale = 0
paramVolume.Value = eodVolume

Open in new window


I think that your code can be improved to avoid recreate the instance of InsertCommand on every cycle...
0

Featured Post

Independent Software Vendors: 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!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now