-
2 Attachment(s)
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: Attachment 159057 Attachment 159059
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!
-
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
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").
-
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...
-
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)
-
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
-
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.
-
2 Attachment(s)
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
-
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
-
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
-
Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'
Quote:
Originally Posted by
ssabc
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.
Quote:
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, "")
-
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
-
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!
-
Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'
Quote:
Originally Posted by
ssabc
I have applied most of your principles.
That looks better. :)
Quote:
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
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.
Quote:
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.
Quote:
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.
-
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.
-
Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'
Quote:
Originally Posted by
ssabc
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.
Quote:
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.
-
1 Attachment(s)
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!
-
Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'
Quote:
Originally Posted by
ssabc
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
-
Re: ADO.NET with CSV, Operator '*' is not defined for type 'DBNull' and type 'DBNull'
Quote:
Originally Posted by
ssabc
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
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.
