dcsimg
Results 1 to 18 of 18

Thread: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Hello:

    For the record, this code worked great the last time I used it, with the Try/Catch that I have now remarked out.

    Code:
                ' Try
                rs.MoveNext()
                If RawMatl IsNot Nothing Then
                    Try
                        If IsDBNull(rs.Fields(8)) = False Then
                            If RawMatl <> rs.Fields(8).Value Then
                                thisposition = count
    
                                If IsDBNull(rs.Fields(3)) = False Or IsDBNull(rs.Fields(9)) = False Then
                                    Dim rsb2 As Object = rs.Bookmark
                                    For c As Integer = thisposition To lastposition Step -1
                                        rs.MovePrevious()
                                        summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value)
    
                                        If MatlUnits = "in" And IsDBNull(rs.Fields(9).Value) = False Then
                                            summationFt = summation / 12
    
                                        End If
    
                                        If MatlUnits = "in^2" And IsDBNull(rs.Fields(9).Value) = False Then
                                            summationFt = summation / 144
    
                                        End If
    
                                    Next
    
                                    rs.Bookmark = rsb2
    
                                End If
    
                            End If
    
                            Debug.WriteLine("2 --> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & "" & ", " & "" & ", " & "" & ", " & Nothing & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & summation & ", " & "" & ", " & summationFt & ", " & "")
                            CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", summation, "", summationFt, "")
                            CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", Nothing, "", Nothing, "")
                            thisposition = count + 2
                            lastposition = count + 1
    
                            summation = 0.0
                            summationFt = 0.0
    
                        End If
    
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
    
                    End Try
    
                End If
    Somewhere in the above code, and it is not on my mutipliers that I can detect, the Operator '*' is not defined for type 'DBNull' and type 'DBNull' error is occurring.

    The gist of the design is that the system is looking at values in a CSV file, through ADO, and being treated as a database. The green line of code below represents the first object to be passed in, and the blue lines represent the totals. The file creates a file called filename2.csv, and then bases filename3.csv off of filename2.csv with some column sorting, and then when complete deletes filename2.csv and renames filename3.csv back to filename2.csv. The program bookmarks recordset positions and loks forward and backward a little to determine when to break.

    Like I said, all was working great just using Try / Catch statements. I have remmed some of these out and am trying to determine what values are NULL.

    I have to wonder if a reference quit working or something, though everything appears well with the project.

    I have attached before / after files which represent when it was working before. I had to rename from csv to txt in order to legally attach them.

    My hope is maybe there is something you will quickly see that I am not after staring at it for a day already.

    Much Thanks!

    Attachments: before.txt after2.txt

    Now here is the full code:

    Note, I have wrapped the above code inside of this code.

    Code:
        Private Sub SortCSV(ByVal _strNewCSVFile As String)
            Debug.WriteLine("-> SUB: SortCSV")
            CreateSchema()
    
            DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv")
    
            Dim ReadText() As String = File.ReadAllLines(CSVFULLFILEPATH)
            Dim s As String
            For Each s In ReadText
                CreateCSVFileHeader(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3" & ".csv", s)
                Exit For ' Line 1 only
    
            Next
    
            Dim cn As ADODB.Connection = New ADODB.Connection
            Dim sql As String = "SELECT * FROM [" & _strNewCSVFile & "] ORDER BY 11, 9, 8, 1"
            Dim rs As ADODB.Recordset = New ADODB.Recordset
    
            Debug.WriteLine(CSVFULLFOLDERPATH & ": " & sql)
            cn.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & CSVFULLFOLDERPATH & "; Extended Properties = ""text;HDR=Yes;FMT=CSVDelimited""; ")
            rs.Open(sql, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, CommandTypeEnum.adCmdText)
    
            'Try
            rs.MoveFirst()
            Dim lastposition As Integer = 1
            Dim count As Integer = 1
            Dim thisposition As Integer
            Dim summation As Decimal = 0.0
            Dim summationFt As Decimal = 0.0
    
            Dim Level As String
            Dim PN As String
            Dim Desc As String
            Dim Qty As Integer
            Dim Rev As String
            Dim Op As String
            Dim Python As String
            Dim Asm_Wld As String
            Dim RawMatl As String
            Dim MatlQty As Decimal
            Dim MatlUnits As String
            Dim MatlQtyFt As Decimal
            Dim MatlUnitsFt As String
    
            Dim recnum As Integer = 0
    
            Do Until rs.EOF
                If IsDBNull(rs.Fields(0).Value) = False Then Level = rs.Fields(0).Value
                If IsDBNull(rs.Fields(1).Value) = False Then PN = rs.Fields(1).Value
                If IsDBNull(rs.Fields(2).Value) = False Then Desc = rs.Fields(2).Value
                If IsDBNull(rs.Fields(3).Value) = False Then Qty = rs.Fields(3).Value
                If IsDBNull(rs.Fields(4).Value) = False Then Rev = rs.Fields(4).Value
                If IsDBNull(rs.Fields(5).Value) = False Then Op = rs.Fields(5).Value
                If IsDBNull(rs.Fields(6).Value) = False Then Python = rs.Fields(6).Value
                If IsDBNull(rs.Fields(7).Value) = False Then Asm_Wld = rs.Fields(7).Value
                If IsDBNull(rs.Fields(8).Value) = False Then RawMatl = rs.Fields(8).Value
                If IsDBNull(rs.Fields(9).Value) = False Then MatlQty = rs.Fields(9).Value
                If IsDBNull(rs.Fields(10).Value) = False Then MatlUnits = rs.Fields(10).Value
                If IsDBNull(rs.Fields(11).Value) = False Then MatlQtyFt = rs.Fields(11).Value
                If IsDBNull(rs.Fields(12).Value) = False Then MatlUnitsFt = rs.Fields(12).Value
    
                Debug.WriteLine("1 --> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & Level & ", " & PN & ", " & Desc & ", " & Qty & ", " & Rev & ", " & Op & ", " & Python & ", " & Asm_Wld & ", " & RawMatl & ", " & MatlQty & ", " & MatlUnits & ", " & MatlQtyFt & ", " & MatlUnitsFt)
                CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", Level, PN, Desc, Qty, Rev, Op, Python, Asm_Wld, RawMatl, MatlQty, MatlUnits, MatlQtyFt, MatlUnitsFt)
    
                ' Check previous rs for different Raw material quantity
                Dim rsb As Object = rs.Bookmark
    
    
    Code:
                ' Try
                rs.MoveNext()
                If RawMatl IsNot Nothing Then
                    Try
                        If IsDBNull(rs.Fields(8)) = False Then
                            If RawMatl <> rs.Fields(8).Value Then
                                thisposition = count
    
                                If IsDBNull(rs.Fields(3)) = False Or IsDBNull(rs.Fields(9)) = False Then
                                    Dim rsb2 As Object = rs.Bookmark
                                    For c As Integer = thisposition To lastposition Step -1
                                        rs.MovePrevious()
                                        summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value)
    
                                        If MatlUnits = "in" And IsDBNull(rs.Fields(9).Value) = False Then
                                            summationFt = summation / 12
    
                                        End If
    
                                        If MatlUnits = "in^2" And IsDBNull(rs.Fields(9).Value) = False Then
                                            summationFt = summation / 144
    
                                        End If
    
                                    Next
    
                                    rs.Bookmark = rsb2
    
                                End If
    
                            End If
    
                            Debug.WriteLine("2 --> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & "" & ", " & "" & ", " & "" & ", " & Nothing & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & summation & ", " & "" & ", " & summationFt & ", " & "")
                            CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", summation, "", summationFt, "")
                            CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", Nothing, "", Nothing, "")
                            thisposition = count + 2
                            lastposition = count + 1
    
                            summation = 0.0
                            summationFt = 0.0
    
                        End If
    
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
    
                    End Try
    
                End If
    ' Catch ex As Exception ' End Try rs.Bookmark = rsb rs.MoveNext() Debug.WriteLine(recnum.ToString & ": Moved Next!") recnum = recnum + 1 count = count + 1 thisposition = count Loop For c As Integer = thisposition To lastposition + 1 Step -1 rs.MovePrevious() Try If IsDBNull(rs.Fields(9).Value) = False Or Len(rs.Fields(9).Value.ToString) > 0 Then MessageBox.Show("Summation Value: " & rs.Fields(9).Value & " * " & rs.Fields(3).Value) summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value) If MatlUnits = "in" And IsDBNull(rs.Fields(9).Value) = False Then summationFt = summation / 12 End If If MatlUnits = "in^2" And IsDBNull(rs.Fields(9).Value) = False Then summationFt = summation / 144 End If Debug.WriteLine("Summation Value: " & rs.Fields(9).Value & " * " & rs.Fields(3).Value & " = " & summation) End If Catch ex As Exception Debug.WriteLine(ex.Message) End Try Next CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", summation, "", summationFt, "") summation = 0.0 summationFt = 0.0 rs.Close() rs = Nothing ' DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv") ' RenameCSV_3_2(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv") End Sub
    Finally, below are a few more of the called subs and functions that go along with the portion of program in discussion.

    Code:
        Private Sub GetADOfromCSV()
            Debug.WriteLine("-> SUB: GetADOfromCSV")
            DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv")
            Dim ReadText() As String = File.ReadAllLines(CSVFULLFILEPATH)
    
            'Dim reader As StreamReader = New StreamReader(CSVFULLFILEPATH)
            'Dim ReadTextFile = reader.ReadToEnd()
            'reader.Close()
    
            CreateSchema()
    
            Dim s As String
            For Each s In ReadText
                CreateCSVFileHeader(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2" & ".csv", s)
                Exit For ' Line 1 only
    
            Next
    
            Dim cn As ADODB.Connection = New ADODB.Connection
            Dim sql As String = "Select * FROM [" & CSVFILENAME & "]" ' ORDER BY 1" ' Cannot add this because of 1.1.10 issue
            Dim rs As ADODB.Recordset = New ADODB.Recordset
            cn.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & CSVFULLFOLDERPATH & "; Extended Properties = ""text;HDR=Yes;FMT=CSVDelimited""; ")
            rs.Open(sql, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
    
            rs.MoveFirst()
            Debug.WriteLine("New File to Create: " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2" & ".csv")
            Dim rsb As Object = rs.Bookmark
            Do Until rs.EOF
                For i As Integer = 0 To rs.Fields.Count - 1
                    Debug.WriteLine("record: " & i & ": " & " = " & rs.Fields(i).Value)
    
                Next
    
                Dim Level As String
                Dim PN As String
                Dim Desc As String
                Dim Qty As Integer
                Dim Rev As String
                Dim Op As String
                Dim Python As String
                Dim Asm_Wld As String
                Dim RawMatl As String
                Dim MatlQty As Decimal
                Dim MatlUnits As String
                Dim MatlQtyFt As Decimal
                Dim MatlUnitsFt As String
    
                If IsDBNull(rs.Fields(0).Value) = False Then Level = rs.Fields(0).Value
                If IsDBNull(rs.Fields(1).Value) = False Then PN = rs.Fields(1).Value
                If IsDBNull(rs.Fields(2).Value) = False Then Desc = rs.Fields(2).Value
    
                ' Check Root Quantity
                Dim RootLevel As String = GetRootLevel(Level)
                Dim QTY_Multiplier As Integer = 1
    
                rsb = rs.Bookmark
    
                Do Until rs.BOF
                    rs.MovePrevious()
                    If rs.BOF = False Then
                        If (rs.Fields(0).Value = RootLevel) Then
                            QTY_Multiplier = rs.Fields(3).Value
                            Exit Do
    
                        Else
                            QTY_Multiplier = 1
    
                        End If
    
                    End If
    
                Loop
    
                rs.Bookmark = rsb
    
                If IsDBNull(rs.Fields(3).Value) = False Then Qty = rs.Fields(3).Value * QTY_Multiplier
                If IsDBNull(rs.Fields(4).Value) = False Then Rev = CStr(rs.Fields(4).Value)
                If IsDBNull(rs.Fields(5).Value) = False Then Op = CStr(rs.Fields(5).Value)
                If IsDBNull(rs.Fields(6).Value) = False Then Python = CStr(rs.Fields(6).Value)
                If IsDBNull(rs.Fields(7).Value) = False Then Asm_Wld = CStr(rs.Fields(7).Value)
                If IsDBNull(rs.Fields(8).Value) = False Then RawMatl = rs.Fields(8).Value
                If IsDBNull(rs.Fields(9).Value) = False Then MatlQty = rs.Fields(9).Value
                If IsDBNull(rs.Fields(10).Value) = False Then MatlUnits = rs.Fields(10).Value
    
                If MatlUnits = "in" And IsDBNull(rs.Fields(9).Value) = False Then
                    MatlQtyFt = rs.Fields(9).Value / 12
                    MatlUnitsFt = "ft"
    
                End If
    
                If MatlUnits = "in^2" And IsDBNull(rs.Fields(9).Value) = False Then
                    MatlQtyFt = rs.Fields(9).Value / 144
                    MatlUnitsFt = "ft^2"
    
                End If
    
                rsb = rs.Bookmark
    
                Do Until rs.EOF
                    rs.MoveNext()
                    Dim EvalPN As String = ""
                    If rs.EOF = False Then
                        If IsDBNull(rs.Fields(1).Value) = False Then EvalPN = rs.Fields(1).Value
                        Debug.WriteLine("EvalPN: " & EvalPN & ", " & "PN: " & PN)
    
                    End If
    
                    If EvalPN.Contains(PN) Then
                        If IsDBNull(rs.Fields(8).Value) = False Then RawMatl = rs.Fields(8).Value
                        If IsDBNull(rs.Fields(9).Value) = False Then MatlQty = rs.Fields(9).Value
                        If IsDBNull(rs.Fields(10).Value) = False Then MatlUnits = rs.Fields(10).Value
    
                        If MatlUnits = "in" And IsDBNull(rs.Fields(9).Value) = False Then
                            MatlQtyFt = rs.Fields(9).Value / 12
                            MatlUnitsFt = "ft"
    
                        End If
    
                        If MatlUnits = "in^2" And IsDBNull(rs.Fields(9).Value) = False Then
                            MatlQtyFt = rs.Fields(9).Value / 144
                            MatlUnitsFt = "ft^2"
    
                        End If
    
                        Debug.WriteLine(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & Level & ", " & PN & ", " & Desc & ", " & Qty & ", " & Rev & ", " & Op & ", " & Python & ", " & Asm_Wld & ", " & RawMatl & ", " & MatlQty & ", " & MatlUnits & ", " & MatlQtyFt & ", " & MatlUnitsFt)
                        If Len(RawMatl) > 0 And RawMatl IsNot Nothing Then CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv", Level, PN, Desc, Qty, Rev, Op, Python, Asm_Wld, RawMatl, MatlQty, MatlUnits, MatlQtyFt, MatlUnitsFt)
    
                        Else
                        Exit Do
    
                    End If
    
                Loop
    
                rs.Bookmark = rsb
                rs.MoveNext()
    
            Loop
    
            rs.Close()
            rs = Nothing
    
            ' Sort CSV file
            SortCSV(CSVFILENAMEWITHOUTEXT & "2" & ".csv")
    
            MessageBox.Show("Updated: " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv")
    
        End Sub

    Code:
        Private Sub CreateSchema() ' 13 Columns
            Debug.WriteLine("-> SUB: CreateSchema")
            ' Valid options for Format are CSV and Tab
            Dim Schema As String = "[" & Path.GetFileName(CSVFILENAME) & "]" &
                vbCrLf & "Format=CSVDelimited" &
                vbCrLf & "" &
                vbCrLf & "Col1=Level Text Width 20" &
                vbCrLf & "Col2=PN Text Width 15" &
                vbCrLf & "Col3=Desc Text Width 150" &
                vbCrLf & "Col4=Qty Long" &
                vbCrLf & "Col5=Rev Text Width 1" &
                vbCrLf & "Col6=Op Text Width 50" &
                vbCrLf & "Col7=Python Text Width 10" &
                vbCrLf & "Col8=Asm_Wld Text Width 10" &
                vbCrLf & "Col9=RawMatl Text Width 120" &
                vbCrLf & "Col10=MatlQty Double" &
                vbCrLf & "Col11=MatlUnits Text Width 10" &
                vbCrLf & "Col12=MatlQtyFt Double" &
                vbCrLf & "Col13=MatlUnitsFt Text Width 10"
    
            File.WriteAllText(CSVFULLFOLDERPATH & "\schema.ini", Schema)
    
        End Sub

    Code:
        Private Sub CreateCSVFileHeader(ByVal _strNewCSVPath As String, ByVal _s As String)
            Debug.WriteLine("-> SUB: CreateCSVFileHeader")
            Try
                Dim objWriter As IO.StreamWriter = IO.File.AppendText(_strNewCSVPath)
                If IO.File.Exists(_strNewCSVPath) Then
                    objWriter.Write(_s)
                    objWriter.Write("" & Chr(10) & Chr(13)) ' New Line
    
                End If
    
                objWriter.Close()
    
            Catch ex As Exception
    
            End Try
    
        End Sub

    Code:
        Private Sub DeleteFile(ByVal _strNewCSVPath As String)
            Debug.WriteLine("-> SUB: DeleteFile")
            Try
                If IO.File.Exists(_strNewCSVPath) Then
                    IO.File.Delete(_strNewCSVPath)
    
                End If
    
            Catch ex As Exception
    
            End Try
    
        End Sub

    Code:
        Private Sub RenameCSV_3_2(ByVal _strNewCSVPath As String) ' *3.csv to *2.csv
            Debug.WriteLine("-> SUB: RenameCSV_3_2")
            Try
                If IO.File.Exists(_strNewCSVPath) Then
                    Dim RenameFileBack_2 As String = Replace(_strNewCSVPath, "3.csv", "2.csv")
                    System.IO.File.Move(_strNewCSVPath, RenameFileBack_2)
    
                End If
    
            Catch ex As Exception
    
            End Try
    
        End Sub


    Thanks again!
    Last edited by ssabc; May 15th, 2018 at 02:26 PM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    It looks like it is probably this bit:
    Quote Originally Posted by ssabc View Post
    rs.MovePrevious()
    summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value)
    As you aren't checking if either of the recordset values are null, you can't tell if it will generate the error or not.


    By the way, you menioned ADO.NET in the thread title, but this is not ADO.NET... it is ADODB (or "Classic ADO").

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Correct, Classic.

    So how should you check for null, because I tried things like this all morning.

    I have added them back in. the "2--> " Creating the sub items just gets skipped every time. I think it's a fair assessment that we are moving next, and that there is data on later lines, based on my attachments in the last post.

    Code:
            'Try
            rs.MoveFirst()
            Dim lastposition As Integer = 1
            Dim count As Integer = 1
            Dim thisposition As Integer
            Dim summation As Decimal = 0.0
            Dim summationFt As Decimal = 0.0
    
    
            Dim Level As String
            Dim PN As String
            Dim Desc As String
            Dim Qty As Integer
            Dim Rev As String
            Dim Op As String
            Dim Python As String
            Dim Asm_Wld As String
            Dim RawMatl As String
            Dim MatlQty As Decimal
            Dim MatlUnits As String
            Dim MatlQtyFt As Decimal
            Dim MatlUnitsFt As String
            Do Until rs.EOF
                If IsDBNull(rs.Fields(0).Value) = False Then Level = rs.Fields(0).Value
                If IsDBNull(rs.Fields(1).Value) = False Then PN = rs.Fields(1).Value
                If IsDBNull(rs.Fields(2).Value) = False Then Desc = rs.Fields(2).Value
                If IsDBNull(rs.Fields(3).Value) = False Then Qty = rs.Fields(3).Value
                If IsDBNull(rs.Fields(4).Value) = False Then Rev = rs.Fields(4).Value
                If IsDBNull(rs.Fields(5).Value) = False Then Op = rs.Fields(5).Value
                If IsDBNull(rs.Fields(6).Value) = False Then Python = rs.Fields(6).Value
                If IsDBNull(rs.Fields(7).Value) = False Then Asm_Wld = rs.Fields(7).Value
                If IsDBNull(rs.Fields(8).Value) = False Then RawMatl = rs.Fields(8).Value
                If IsDBNull(rs.Fields(9).Value) = False Then MatlQty = rs.Fields(9).Value
                If IsDBNull(rs.Fields(10).Value) = False Then MatlUnits = rs.Fields(10).Value
                If IsDBNull(rs.Fields(11).Value) = False Then MatlQtyFt = rs.Fields(11).Value
                If IsDBNull(rs.Fields(12).Value) = False Then MatlUnitsFt = rs.Fields(12).Value
    
                MessageBox.Show("1--> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & Level & ", " & PN & ", " & Desc & ", " & Qty & ", " & Rev & ", " & Op & ", " & Python & ", " & Asm_Wld & ", " & RawMatl & ", " & MatlQty & ", " & MatlUnits & ", " & MatlQtyFt & ", " & MatlUnitsFt)
                CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", Level, PN, Desc, Qty, Rev, Op, Python, Asm_Wld, RawMatl, MatlQty, MatlUnits, MatlQtyFt, MatlUnitsFt)
    
                ' Check previous rs for different Raw material quantity
                Dim rsb As Object = rs.Bookmark
    
                Try
                    rs.MoveNext()
                    If RawMatl IsNot Nothing Then
                        If RawMatl <> rs.Fields(8).Value Then
                            thisposition = count
                            Dim rsb2 As Object = rs.Bookmark
    
                            If IsDBNull(rs.Fields(3).Value) = False Or (rs.Fields(9).Value) = False Then
                                For c As Integer = thisposition To lastposition Step -1
                                    rs.MovePrevious()
                                    summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value)
    
                                    If MatlUnits = "in" And IsDBNull(rs.Fields(9).Value) = False Then
                                        summationFt = summation / 12
    
                                    End If
    
                                    If MatlUnits = "in^2" And IsDBNull(rs.Fields(9).Value) = False Then
                                        summationFt = summation / 144
    
                                    End If
    
                                Next
    
                            End If
    
                            rs.Bookmark = rsb2
    
                            MessageBox.Show("2--> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & "" & ", " & "" & ", " & "" & ", " & Nothing & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & summation & ", " & "" & ", " & summationFt & ", " & "")
                            CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", summation, "", summationFt, "")
                            CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", Nothing, "", Nothing, "")
                            thisposition = count + 2
                            lastposition = count + 1
    
                            summation = 0.0
                            summationFt = 0.0
    
                        End If
    
                    End If
    
                Catch ex As Exception
    
                End Try
    
                rs.Bookmark = rsb
                rs.MoveNext()
    
                count = count + 1
                thisposition = count
    
            Loop
    Whats killing me is the logic worked great a month ago! I have just remmed out the Try Catch not to try and isolatee...

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    I haven't used ADODB in .Net so I'm not sure, but this is how it would work in classic VB:
    Code:
          If (rs.Fields(3).Value) Is Null) = False Or (rs.Fields(9).Value Is Null) = False Then
    ...however, this won't fix the line I indicated before, because this If statement only deals with entering the loop - it doesn't deal with the values inside the loop (you need an If statement around the summation= line, to avoid running it if either of the values are null)

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,773

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    By the way... it never worked... even with the try / catch in place... your catch was empty, so the error has been happening all this time but it was being ignored. In short, you were doing the .NET equivalent of On Error Resume Next ... when you commented it out, it no longer had anywhere to go when the error happened giving the appearance of "suddenly" breaking. If you at least had had a message box in there, you'd have known something was up.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,925

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Presumably a null would be the equivalent of zero in business terms, is that right?

    If so this link shows how to coerce it into a zero. I prefer to do it in the SQL query but I don't think you're using one so you'd have to use the VB.Net solution.

    You'd want to apply it to rs.Fields(9).Value and rs.Fields(3).Value before you calculate summation.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Hello again:

    This is about the biggest cluster I have encountered, and I do not believe my query is returning anything from the CSV using ADODB. I have literally spend most of the week on these few lines of code, which ran great a month or so ago.

    I have attached the files.

    And here is the code with a bunch of comments added. Note, the goal is to read the file: E7068911-Export-2nd.csv (renamed to txt), and this just is not happening.

    Help! and Thank you.

    Code:
        Private Sub SortCSV(ByVal _strNewCSVFile As String)
            Debug.WriteLine("-> SUB: SortCSV")
            CreateSchema()
    
            DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv")
    
            Dim ReadText() As String = File.ReadAllLines(CSVFULLFILEPATH)
            Dim s As String
            For Each s In ReadText
                CreateCSVFileHeader(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3" & ".csv", s)
                Exit For ' Line 1 only
    
            Next
    
            Dim cn As ADODB.Connection = New ADODB.Connection
            Dim sql As String = "SELECT * FROM [" & _strNewCSVFile & "] ORDER BY 11, 9, 10, 1" ' Orders by Columns K, I, J and A
            Dim rs As ADODB.Recordset = New ADODB.Recordset
    
            ' This line of code opens the CSV file completed in the last step.  Filename is E7068911-Export-2nd2.csv for this case.
            cn.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & CSVFULLFOLDERPATH & "; Extended Properties = ""text;HDR=Yes;FMT=CSVDelimited""; ")
            rs.Open(sql, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, CommandTypeEnum.adCmdText)
    
            Try
                rs.MoveFirst() ' Moves to the first record
                Dim lastposition As Integer = 1
                Dim count As Integer = 1
                Dim thisposition As Integer
                Dim summation As Decimal = 0.0
                Dim summationFt As Decimal = 0.0
    
                Dim Level As String ' Initialize variables
                Dim PN As String
                Dim Desc As String
                Dim Qty As Integer
                Dim Rev As String
                Dim Op As String
                Dim Python As String
                Dim Asm_Wld As String
                Dim RawMatl As String
                Dim MatlQty As Decimal
                Dim MatlUnits As String
                Dim MatlQtyFt As Decimal
                Dim MatlUnitsFt As String
                Do Until rs.EOF ' Okay, the file has 161 rows
                    If IsDBNull(rs.Fields(0).Value) = False Then Level = rs.Fields(0).Value ' If there is a value, set a variable
                    If IsDBNull(rs.Fields(1).Value) = False Then PN = rs.Fields(1).Value
                    If IsDBNull(rs.Fields(2).Value) = False Then Desc = rs.Fields(2).Value
                    If IsDBNull(rs.Fields(3).Value) = False Then Qty = rs.Fields(3).Value
                    If IsDBNull(rs.Fields(4).Value) = False Then Rev = rs.Fields(4).Value
                    If IsDBNull(rs.Fields(5).Value) = False Then Op = rs.Fields(5).Value
                    If IsDBNull(rs.Fields(6).Value) = False Then Python = rs.Fields(6).Value
                    If IsDBNull(rs.Fields(7).Value) = False Then Asm_Wld = rs.Fields(7).Value
                    If IsDBNull(rs.Fields(8).Value) = False Then RawMatl = rs.Fields(8).Value
                    If IsDBNull(rs.Fields(9).Value) = False Then MatlQty = rs.Fields(9).Value
                    If IsDBNull(rs.Fields(10).Value) = False Then MatlUnits = rs.Fields(10).Value
                    If IsDBNull(rs.Fields(11).Value) = False Then MatlQtyFt = rs.Fields(11).Value
                    If IsDBNull(rs.Fields(12).Value) = False Then MatlUnitsFt = rs.Fields(12).Value
    
                    ' Nothing happens here.  Apparently there are no values for the first record, even though there are 161 rows.  What a cluster.
                    MessageBox.Show("1--> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & Level & ", " & PN & ", " & Desc & ", " & Qty & ", " & Rev & ", " & Op & ", " & Python & ", " & Asm_Wld & ", " & RawMatl & ", " & MatlQty & ", " & MatlUnits & ", " & MatlQtyFt & ", " & MatlUnitsFt)
                    CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", Level, PN, Desc, Qty, Rev, Op, Python, Asm_Wld, RawMatl, MatlQty, MatlUnits, MatlQtyFt, MatlUnitsFt)
    
                    ' Check next rs for different Raw material quantity
                    Dim rsb As Object = rs.Bookmark
                    rs.MoveNext() ' Move to the next record 
                    If RawMatl <> rs.Fields(8).Value Then ' Moved next...  If the Raw Material in the last record is unequal to the record in this record, proceed
                        thisposition = count
                        Dim rsb2 As Object = rs.Bookmark
    
                        For c As Integer = thisposition To lastposition Step -1
                            rs.MovePrevious()
                            summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value)
    
                            If MatlUnits = "In" Then
                                summationFt = summation / 12
    
                            End If
    
                            If MatlUnits = "In^2" Then
                                summationFt = summation / 144
    
                            End If
    
                        Next
    
                        rs.Bookmark = rsb2
    
                        ' Below, because the RawMatl values are unequal, we are totaling up the values for all material that is sorted in the query above (ORDER BY 11, 9, 10, 1" ' Orders by Columns K, I, J and A)
                        MessageBox.Show("2--> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & "" & ", " & "" & ", " & "" & ", " & Nothing & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & summation & ", " & "" & ", " & summationFt & ", " & "")
                        CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", summation, "", summationFt, "")
                        CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", Nothing, "", Nothing, "")
                        thisposition = count + 2
                        lastposition = count + 1
    
                        summation = 0.0
                        summationFt = 0.0
    
                    End If
    
                    rs.Bookmark = rsb
                    rs.MoveNext()
    
                    count = count + 1
                    thisposition = count
    
                Loop
    
                For c As Integer = thisposition To lastposition + 1 Step -1
                    rs.MovePrevious()
                    Try
                        summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value)
    
                        If MatlUnits = "In" And IsDBNull(rs.Fields(9).Value) = False Then
                            summationFt = summation / 12
    
                        End If
    
                        If MatlUnits = "In^2" And IsDBNull(rs.Fields(9).Value) = False Then
                            summationFt = summation / 144
    
                        End If
    
                    Catch ex As Exception
    
                    End Try
    
                Next
    
                ' This section picks up the last row using the same logic as above
                CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", summation, "", summationFt, "")
    
                summation = 0.0
                summationFt = 0.0
    
                rs.Close()
                rs = Nothing
    
            Catch
    
            End Try
    
            ' We then delete the file created before this whole routine started
            DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv")
    
            ' And then we rename the newly created file back to the original filename.
            RenameCSV_3_2(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv")
    
        End Sub
    Attached Files Attached Files

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    The first step is to remove (or comment out) your Try/Catch blocks, because the empty Catch blocks mean that all they are doing making is making it far more likely that you will get situations where it doesn't work properly but you don't know why (or only partly work but you don't realise). An empty Catch block is usually a very bad thing, and it is in this case.

    After that try running the code to see if it works, or gives you an error that you/we can fix.

    If there aren't any errors (or you get them all fixed), the next step is debugging to find out what data is available at each stage of the process. There is a good tutorial on debugging here:
    https://docs.microsoft.com/en-gb/vis...h-the-debugger

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    I actually pulled out all of the Try/Catch statements yesterday.

    I do not believe I am getting the MoveNext here.

    I'm going to look at the debugging, but let me know if you see anything.

    New code below:

    Thanks for your help!

    Code:
        Private Sub SortCSV(ByVal _strNewCSVFile As String)
            Debug.WriteLine("-> SUB: SortCSV")
            CreateSchema()
    
            DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv")
    
            Dim ReadText() As String = File.ReadAllLines(CSVFULLFILEPATH)
            Dim s As String
            For Each s In ReadText
                CreateCSVFileHeader(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3" & ".csv", s)
                Exit For ' Line 1 only
    
            Next
    
            Dim cn As ADODB.Connection = New ADODB.Connection
            Dim sql As String = "SELECT * FROM [" & _strNewCSVFile & "] ORDER BY 11, 9, 10, 1" ' Orders by Columns K, I, J and A
    
            Dim rs As ADODB.Recordset = New ADODB.Recordset
    
            ' This line of code opens the CSV file completed in the last step.  Filename is E7068911-Export-2nd2.csv for this case.
            cn.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & CSVFULLFOLDERPATH & "; Extended Properties = ""text;HDR=Yes;FMT=CSVDelimited""; ")
            rs.Open(sql, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, CommandTypeEnum.adCmdText)
    
            rs.MoveFirst() ' Moves to the first record
            Dim lastposition As Integer = 1
            Dim count As Integer = 1
            Dim thisposition As Integer
            Dim summation As Decimal = 0.0
            Dim summationFt As Decimal = 0.0
    
            Dim Level As String ' Initialize variables
            Dim PN As String
            Dim Desc As String
            Dim Qty As Integer
            Dim Rev As String
            Dim Op As String
            Dim Python As String
            Dim Asm_Wld As String
            Dim RawMatl As String
            Dim MatlQty As Decimal
            Dim MatlUnits As String
            Dim MatlQtyFt As Decimal
            Dim MatlUnitsFt As String
            Do Until rs.EOF ' Okay, the file has 161 rows
                If IsDBNull(rs.Fields(0).Value) = False Then Level = rs.Fields(0).Value ' If there is a value, set a variable
                If IsDBNull(rs.Fields(1).Value) = False Then PN = rs.Fields(1).Value
                If IsDBNull(rs.Fields(2).Value) = False Then Desc = rs.Fields(2).Value
                If IsDBNull(rs.Fields(3).Value) = False Then Qty = rs.Fields(3).Value
                If IsDBNull(rs.Fields(4).Value) = False Then Rev = rs.Fields(4).Value
                If IsDBNull(rs.Fields(5).Value) = False Then Op = rs.Fields(5).Value
                If IsDBNull(rs.Fields(6).Value) = False Then Python = rs.Fields(6).Value
                If IsDBNull(rs.Fields(7).Value) = False Then Asm_Wld = rs.Fields(7).Value
                If IsDBNull(rs.Fields(8).Value) = False Then RawMatl = rs.Fields(8).Value
                If IsDBNull(rs.Fields(9).Value) = False Then MatlQty = rs.Fields(9).Value
                If IsDBNull(rs.Fields(10).Value) = False Then MatlUnits = rs.Fields(10).Value
                If IsDBNull(rs.Fields(11).Value) = False Then MatlQtyFt = rs.Fields(11).Value
                If IsDBNull(rs.Fields(12).Value) = False Then MatlUnitsFt = rs.Fields(12).Value
    
                ' These values are repeated several times, recordset 1
                MessageBox.Show("1--> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & Level & ", " & PN & ", " & Desc & ", " & Qty & ", " & Rev & ", " & Op & ", " & Python & ", " & Asm_Wld & ", " & RawMatl & ", " & MatlQty & ", " & MatlUnits & ", " & MatlQtyFt & ", " & MatlUnitsFt)
                CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", Level, PN, Desc, Qty, Rev, Op, Python, Asm_Wld, RawMatl, MatlQty, MatlUnits, MatlQtyFt, MatlUnitsFt)
    
                ' Check next rs for different Raw material quantity
                Dim rsb As Object = rs.Bookmark
                rs.MoveNext() ' Move to the next record - must not be happening
                If IsDBNull(rs.Fields(8).Value) = False Then
                    If RawMatl <> rs.Fields(8).Value Then ' Moved next...  If the Raw Material in the last record is unequal to the record in this record, proceed
                        thisposition = count
                        Dim rsb2 As Object = rs.Bookmark
    
                        For c As Integer = thisposition To lastposition Step -1
                            rs.MovePrevious()
                            summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value)
    
                            If MatlUnits = "In" Then
                                summationFt = summation / 12
    
                            End If
    
                            If MatlUnits = "In^2" Then
                                summationFt = summation / 144
    
                            End If
    
                        Next
    
                        rs.Bookmark = rsb2
    
                        ' Below, because the RawMatl values are unequal, we are totaling up the values for all material that is sorted in the query above (ORDER BY 11, 9, 10, 1" ' Orders by Columns K, I, J and A)
                        MessageBox.Show("2--> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & "" & ", " & "" & ", " & "" & ", " & Nothing & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & "" & ", " & summation & ", " & "" & ", " & summationFt & ", " & "")
                        CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", summation, "", summationFt, "")
                        CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", Nothing, "", Nothing, "")
                        thisposition = count + 2
                        lastposition = count + 1
    
                        summation = 0.0
                        summationFt = 0.0
    
                    End If
    
                End If
    
                rs.Bookmark = rsb
                rs.MoveNext()
    
                count = count + 1
                thisposition = count
    
            Loop
    
            For c As Integer = thisposition To lastposition + 1 Step -1
                rs.MovePrevious()
                summation = summation + (rs.Fields(9).Value * rs.Fields(3).Value)
    
                If MatlUnits = "In" And IsDBNull(rs.Fields(9).Value) = False Then
                    summationFt = summation / 12
    
                End If
    
                If MatlUnits = "In^2" And IsDBNull(rs.Fields(9).Value) = False Then
                    summationFt = summation / 144
    
                End If
    
            Next
    
            ' This section picks up the last row using the same logic as above
            CreateCSVFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv", "", "", "", Nothing, "", "", "", "", "", summation, "", summationFt, "")
    
                summation = 0.0
                summationFt = 0.0
    
                rs.Close()
                rs = Nothing
    
            ' We then delete the file created before this whole routine started
            DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "2.csv")
    
            ' And then we rename the newly created file back to the original filename.
            RenameCSV_3_2(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv")
    
        End Sub

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Quote Originally Posted by ssabc View Post
    I do not believe I am getting the MoveNext here.
    If you haven't debugged, that is only a guess... and acting based on guesses is unlikely to make things work properly.

    Debugging is how you discover the truth of what it happening, so you can work out what the issue actually is. Once you have that knowledge you can then fix it appropriately.

    I'm going to look at the debugging, but let me know if you see anything.
    There is a lot of code to check, so I haven't looked at the full flow of everything, but there are several things that are dubious (in addition to the item I mentioned in post #2, which you still haven't acted on).

    First of all this chunk of code:
    Code:
            Dim ReadText() As String = File.ReadAllLines(CSVFULLFILEPATH)
            Dim s As String
            For Each s In ReadText
                CreateCSVFileHeader(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3" & ".csv", s)
                Exit For ' Line 1 only
            Next
    ...can be re-written in a shorter and clearer way:
    Code:
            Dim ReadText() As String = File.ReadAllLines(CSVFULLFILEPATH)
            If ReadText.Length > 0 Then
                CreateCSVFileHeader(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3" & ".csv", ReadText(0))
            End If

    There are a couple of odd things here:
    Code:
            rs.Open(sql, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, CommandTypeEnum.adCmdText)
    
            rs.MoveFirst() ' Moves to the first record
    The Lock type is an odd choice, read-only (if it is valid in the circumstances) should be faster and less likely to cause unnecessary errors. For more information, see the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

    The other thing is that there is no valid reason to have .MoveFirst just after opening a recordset (it will already be at the first record), but there is a very good reason not to use it: if there aren't any records, a .MoveFirst will cause an error.


    Another concern is how you set the variables such as Level/PN/Desc, because if the value is null you are just keeping whatever value was previously in those variables. To avoid that, change the lines like this:
    Code:
                If IsDBNull(rs.Fields(1).Value) = False Then PN = rs.Fields(1).Value
    ...to something like this (as it is a String, I set the value to an empty string if the field is null):
    Code:
                PN = If (IsDBNull(rs.Fields(1).Value) = False, rs.Fields(1).Value, "")

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    I have applied most of your principles. I have also concluded that the problem is with the ORDER BY clause on the SQL variable. I suspected this yesterday, but was getting inconsistent results.

    The problem is, I need to reorder the data in order to read the CSV file and add my calculations in each area.

    So, either something is wrong with the CSV file, or I need to use different options on the opening of the recordset.

    Simplified code below:

    Code:
        Private Sub SortCSV(ByVal _strNewCSVFile As String)
            Debug.WriteLine("-> SUB: SortCSV")
            CreateSchema()
            MessageBox.Show("Schema Created")
    
            DeleteFile(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv")
            MessageBox.Show("Old csv file deleted: " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv")
    
            Dim ReadText() As String = File.ReadAllLines(CSVFULLFILEPATH)
            Dim s As String
            For Each s In ReadText
                CreateCSVFileHeader(CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3" & ".csv", s)
                MessageBox.Show("Write text completed, line 1 " & s & " of " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3" & ".csv")
                Exit For ' Line 1 only
    
            Next
    
            Dim cn As ADODB.Connection = New ADODB.Connection
            Dim sql As String = "SELECT * FROM [" & _strNewCSVFile & "] ORDER BY 9, 1" ' Orders by Columns K, I, J and A
            MessageBox.Show("SQL: " & sql)
    
            Dim rs As ADODB.Recordset = New ADODB.Recordset
    
            ' This line of code opens the CSV file completed in the last step.  Filename is E7068911-Export-2nd2.csv for this case.
            MessageBox.Show("We are reading from file : " & _strNewCSVFile & " in " & CSVFULLFOLDERPATH)
    
            cn.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & CSVFULLFOLDERPATH & "; Extended Properties = ""text;HDR=Yes;FMT=CSVDelimited""; ")
            rs.Open(sql, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
            MessageBox.Show("Recordset opened")
    
            ' rs.MoveFirst() ' Moves to the first record
            MessageBox.Show("Moved to the first record")
    
            Dim lastposition As Integer = 1
            Dim count As Integer = 1
            Dim thisposition As Integer
            Dim summation As Decimal = 0.0
            Dim summationFt As Decimal = 0.0
    
            Dim Level As String ' Initialize variables
            Dim PN As String
            Dim Desc As String
            Dim Qty As Integer
            Dim Rev As String
            Dim Op As String
            Dim Python As String
            Dim Asm_Wld As String
            Dim RawMatl As String
            Dim MatlQty As Decimal
            Dim MatlUnits As String
            Dim MatlQtyFt As Decimal
            Dim MatlUnitsFt As String
            Do Until rs.EOF ' Okay, the file has 161 rows
                ' If IsDBNull(rs.Fields(0).Value) = False Then Level = rs.Fields(0).Value ' If there is a value, set a variable
                Level = If(IsDBNull(rs.Fields(0).Value) = False, rs.Fields(0).Value, "")
    
                ' If IsDBNull(rs.Fields(1).Value) = False Then PN = rs.Fields(1).Value
                PN = If(IsDBNull(rs.Fields(1).Value) = False, rs.Fields(1).Value, "")
    
                ' If IsDBNull(rs.Fields(2).Value) = False Then Desc = rs.Fields(2).Value
                Desc = If(IsDBNull(rs.Fields(2).Value) = False, rs.Fields(2).Value, "")
    
                ' If IsDBNull(rs.Fields(3).Value) = False Then Qty = rs.Fields(3).Value
                Qty = If(IsDBNull(rs.Fields(3).Value) = False, rs.Fields(3).Value, 0)
    
                ' If IsDBNull(rs.Fields(4).Value) = False Then Rev = rs.Fields(4).Value
                Rev = If(IsDBNull(rs.Fields(4).Value) = False, rs.Fields(4).Value, "")
    
                ' If IsDBNull(rs.Fields(5).Value) = False Then Op = rs.Fields(5).Value
                Op = If(IsDBNull(rs.Fields(5).Value) = False, rs.Fields(5).Value, "")
    
                ' If IsDBNull(rs.Fields(6).Value) = False Then Python = rs.Fields(6).Value
                Python = If(IsDBNull(rs.Fields(6).Value) = False, rs.Fields(6).Value, "")
    
                ' If IsDBNull(rs.Fields(7).Value) = False Then Asm_Wld = rs.Fields(7).Value
                Asm_Wld = If(IsDBNull(rs.Fields(7).Value) = False, rs.Fields(7).Value, "")
    
                ' If IsDBNull(rs.Fields(8).Value) = False Then RawMatl = rs.Fields(8).Value
                RawMatl = If(IsDBNull(rs.Fields(8).Value) = False, rs.Fields(8).Value, "")
    
                ' If IsDBNull(rs.Fields(9).Value) = False Then MatlQty = rs.Fields(9).Value
                MatlQty = If(IsDBNull(rs.Fields(9).Value) = False, rs.Fields(9).Value, 0.0)
    
                ' If IsDBNull(rs.Fields(10).Value) = False Then MatlUnits = rs.Fields(10).Value
                MatlUnits = If(IsDBNull(rs.Fields(10).Value) = False, rs.Fields(10).Value, "")
    
                ' If IsDBNull(rs.Fields(11).Value) = False Then MatlQtyFt = rs.Fields(11).Value
                MatlQtyFt = If(IsDBNull(rs.Fields(11).Value) = False, rs.Fields(11).Value, 0.0)
    
                ' If IsDBNull(rs.Fields(12).Value) = False Then MatlUnitsFt = rs.Fields(12).Value
                MatlUnitsFt = If(IsDBNull(rs.Fields(12).Value) = False, rs.Fields(12).Value, "")
    
                ' These values are repeated several times, recordset 1
                MessageBox.Show(" 1--> " & CSVFULLFOLDERPATH & "\" & CSVFILENAMEWITHOUTEXT & "3.csv" & ", " & Level & ", " & PN & ", " & Desc & ", " & Qty & ", " & Rev & ", " & Op & ", " & Python & ", " & Asm_Wld & ", " & RawMatl & ", " & MatlQty & ", " & MatlUnits & ", " & MatlQtyFt & ", " & MatlUnitsFt)
    
                rs.MoveNext()
    
            Loop
    
        End Sub

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    I really think there must have been a Windows Update or something that changed this functionality. Do I not need to create a sort function differently?

    I was also reading that I needed to specify CursorLocationEnum.adUseClient on my cn.Open, but I do not see where this can be specified in the allowable syntax.

    Thanks again!

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Quote Originally Posted by ssabc View Post
    I have applied most of your principles.
    That looks better.

    I have also concluded that the problem is with the ORDER BY clause on the SQL variable.
    How did you get to that conclusion? (I assume there was some kind of evidence, but you haven't hinted what it might be)

    Quote Originally Posted by ssabc View Post
    I really think there must have been a Windows Update or something that changed this functionality.
    While that is a possibility (an extremely unlikely one), it is best to always assume that code is not 100% correct unless you can totally prove otherwise.

    Do I not need to create a sort function differently?
    I don't know, as you haven't explained why you might need to do that.

    I was also reading that I needed to specify CursorLocationEnum.adUseClient on my cn.Open, but I do not see where this can be specified in the allowable syntax.
    It isn't part of cn.Open, and it doesn't actually make a difference in this situation... so ignore it.

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    I read that Cursor Location had to be set to adUseClient for sorting, and am wondering if it is also required for ORDER BY. Though this all worked a month or so back.

    Any ORDER BY produces no data, yet when it is removed, there is data. Also, I have ordered by just one column, instead of four, and this is still the case.

    I really wanted to use a SQL statement to get the order, but perhaps there was a Windows Update that changed something. Or, perhaps there is a setting in VS (32 bit vs 64 bit or something) that needs to be changed??

    Currently I am using .NET Framework 4.6.1 and the Target CPU is Any, prefer 32-bit.

    Thoughts??

    Thank you for all the help.

    If I cannot reconcile this, I will have to add a sort function, I suppose.

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Quote Originally Posted by ssabc View Post
    I read that Cursor Location had to be set to adUseClient for sorting,
    I used classic ADO at work for about 10 years, and that does not seem correct to me... I certainly didn't need to do it.

    Any ORDER BY produces no data, yet when it is removed, there is data. Also, I have ordered by just one column, instead of four, and this is still the case.
    In that case the database engine (which is JET, not Windows or VS) is having issues of some kind with the data... given that the source of data is a file, it implies that the file itself may well be the cause.

    Given that the way JET deals with data files is to read the first X rows to guess the data types of the columns, it may well be that the file in this case doesn't have enough data in the first few rows in order for the data types to be determined. As the first few rows of the file have several blank columns, that is likely to be the cause.

    One way to test that is to temporarily add a row of fake data to the start of the file, making sure the values use appropriate data types for the columns.

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Mar 2013
    Posts
    207

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Yes but the issue is this:

    This works:
    Code:
    Dim sql As String = "SELECT * FROM [" & _strNewCSVFile & "]"
    This does not:
    Code:
    Dim sql As String = "SELECT * FROM [" & _strNewCSVFile & "] ORDER BY 11, 9, 10, 1" ' Orders by Columns K, I, J and A
    And sorting only one column, any column also fails. As I understand it, order by doesn't care about the data type. Alpha or Numeric... That said, a Schema file is generated each time (I have attached the last, renamed from ini to txt).

    And the code (the original code I posted), while not perfect, worked a month or so ago.

    Let me know what you think, but I just do not see data types as the issue.

    Thanks again!
    Attached Files Attached Files

  17. #17
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,773

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Quote Originally Posted by ssabc View Post
    I read that Cursor Location had to be set to adUseClient for sorting, and am wondering if it is also required for ORDER BY. Though this all worked a month or so back.

    Any ORDER BY produces no data, yet when it is removed, there is data. Also, I have ordered by just one column, instead of four, and this is still the case.
    That's if you use the .Sort method of the RecordSet ... if you use Order By in the SQL, it doesn't matter where the cursor location is. It could be on the moon for all VB cares. The reason the cursor location matters if you use .Sort is because the all of the data has to be on the client before it can be sorted. If the cursor is server-side, then it can't sort because it doesn't have all of the data. Given that you're going against a CSV file, I don't think it matters either way where the cursor is because CSVs don't support server-side cursors.

    Order by does care about the data because numbers and alphas sort differently... so if it can't tell within the first couple of rows what the type is, if you then try to sort on it, it will sort it incorrectly.

    What I'd do is this... forget Classic ADO... read it into ADO.NET datatables... should give you a bit more flexibility in working with the data... you can pre-create a datatable with the correct types for each column, read the data in and get it sorted correctly, and then run your calculations.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'

    Quote Originally Posted by ssabc View Post
    That said, a Schema file is generated each time (I have attached the last, renamed from ini to txt).
    In that case you should be using it... I'm pretty sure you can specify it in the connection string, see connectionstrings.com for examples etc

    Quote Originally Posted by techgnome View Post
    What I'd do is this... forget Classic ADO... read it into ADO.NET datatables... should give you a bit more flexibility in working with the data... you can pre-create a datatable with the correct types for each column, read the data in and get it sorted correctly, and then run your calculations.
    Me too. It should also make the code cleaner in a few ways.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width