sqlSelectCommand.ExecuteReader() Not working on null fields

I have the following code not bringing back null results. It brings back results fine until it gets to ones that are null.

Here is the query run in sql management studio
query
Here is the vb.net code executing the query
Try
            sqlConnection.Open()
            sqlResults = sqlSelectCommand.ExecuteReader()

            Dim fNextResult As Boolean = True

            Using streamWriterObject As New StreamWriter(CSVFileandLocation)
                Do Until Not fNextResult
                    streamWriterObject.WriteLine(sqlResults.GetName(0) & "," & FixNull(sqlResults.GetName(1)) & "," & FixNull(sqlResults.GetName(2)))
                    'Overwrite the original csv file with new records

                    Do While (sqlResults.Read)
                        streamWriterObject.WriteLine(sqlResults.GetString(0) & "," & FixNull(sqlResults.GetString(1)) & "," & FixNull(sqlResults.GetString(2)))
                    Loop
                    fNextResult = sqlResults.NextResult()
                Loop
                streamWriterObject.Close()
                sqlResults.Close()
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            MessageBox(ex.ToString)
        Finally
            sqlConnection.Close()
        End Try

Open in new window


And here is the FixNull Function is used. I tried it with and without this function
Public Function FixNull(ByVal o As Object) As Object

        If IsDBNull(o) Then
            Return Nothing
        Else
            Return o
        End If
    End Function

Open in new window


The file attached is what i get back
AmiPartNumbers.csv
LVL 2
bignadadAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
did u debug the code?
do u get false on NextResult() when reaching the 4th row?
did u try using SqlDataAdapter instead of reader.
its much easier with no hassle of checking dbnull.
 sqlConnection.Open()
  Dim adapter As New SqlDataAdapter()
Dim dt as DataTable = new DataTable()
        adapter.SelectCommand = sqlSelectCommand
        adapter.Fill(dt)

Open in new window


add this function to save datatable as csv:
Public Sub ToCSV(table As DataTable, file as string)
	Dim result = New StringBuilder()
	For i As Integer = 0 To table.Columns.Count - 1
		result.Append(table.Columns(i).ColumnName)
		result.Append(If(i Is table.Columns.Count - 1, vbLf, ","))
	Next

	For Each row As DataRow In table.Rows
		For i As Integer = 0 To table.Columns.Count - 1
			result.Append(row(i).ToString())
			result.Append(If(i Is table.Columns.Count - 1, vbLf, ","))
		Next
	Next

File.WriteAllText(file, result.ToString())
End Sub

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
did u check if exception is being thrown?
also remove line 17, since u use using keyword no need to explicitly call close on treamWriterObject.
and the sqlResults.Close() move after end using of streamWriterObject.
0
 
bignadadAuthor Commented:
No exceptions are being thrown. the file downloads normally like it should.

It was working fine before i altered my query but that shouldn't matter should it?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Robert SchuttSoftware EngineerCommented:
I'm not saying this should be your final solution but it seems to me that in your FixNull function nothing is being 'fixed', try changing:
Return Nothing

Open in new window

to:
Return ""

Open in new window

0
 
bignadadAuthor Commented:
@robert_schutt
I changed to "" but still didnt work

@sedgwick
I tried that but getting errors on the function ToCSV

Try
            sqlConnection.Open()
            Dim dt As New DataTable
            adapterSQL.SelectCommand = sqlSelectCommand

            adapterSQL.Fill(dt)
            Call ToCSV(dt, CSVFileandLocation)

        Catch ex As Exception
            MessageBox(ex.Message)
            MessageBox(ex.ToString)
        Finally
            sqlConnection.Close()
        End Try


        FileUpload1.Dispose()
    End Sub

Open in new window


Public Sub ToCSV(ByVal table As DataTable, ByVal file As String)
        Dim result = New StringBuilder()
        For i As Integer = 0 To table.Columns.Count - 1
            result.Append(table.Columns(i).ColumnName)
            result.Append(If(i Is table.Columns.Count - 1, vbLf, ","))
        Next

        For Each row As DataRow In table.Rows
            For i As Integer = 0 To table.Columns.Count - 1
                result.Append(row(i).ToString())
                result.Append(If(i Is table.Columns.Count - 1, vbLf, ","))
            Next
        Next

        File.WriteAllText(file, result.ToString())
    End Sub

Open in new window


'Is' operator does not accept operands of type 'Integer'. Operands must be reference or nullable types.

Also WriteAllText is not a member of string error
0
 
Meir RivkinFull stack Software EngineerCommented:
bloody converter, let me fix it
0
 
Meir RivkinFull stack Software EngineerCommented:
    Public Sub ToCSV(ByVal table As DataTable, ByVal csvfile As String)
        Dim result = New StringBuilder()
        For i As Integer = 0 To table.Columns.Count - 1
            result.Append(table.Columns(i).ColumnName)
            result.Append(If(i = table.Columns.Count - 1, vbLf, ","))
        Next

        For Each row As DataRow In table.Rows
            For i As Integer = 0 To table.Columns.Count - 1
                result.Append(row(i).ToString())
                result.Append(If(i = table.Columns.Count - 1, vbLf, ","))
            Next
        Next

        File.WriteAllText(csvfile, result.ToString())
    End Sub

Open in new window

0
 
bignadadAuthor Commented:
i changed IS to = and fixed that error but still getting the
WriteAllText is not a member of string error

Just saw your update. That fixed errors. testing now
0
 
bignadadAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for bignadad's comment #a39192462

for the following reason:

Thank you so much!
0
 
bignadadAuthor Commented:
i tried to accept answer and it tried to close question
0
 
bignadadAuthor Commented:
Thank you so much!
0
 
Meir RivkinFull stack Software EngineerCommented:
pick one of my posts and click accept as answer
0
 
Meir RivkinFull stack Software EngineerCommented:
10x
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.