|
-
May 15th, 2018, 02:19 PM
#1
Thread Starter
Fanatic Member
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote