asked on

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

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

Meir Rivkin

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.

Addie Baker

ASKER

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?

Robert Schutt

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

ASKER CERTIFIED SOLUTION

Meir Rivkin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Addie Baker

ASKER

@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

Meir Rivkin

bloody converter, let me fix it

Meir Rivkin

    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

Addie Baker

ASKER

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

Addie Baker

ASKER

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!

Addie Baker

ASKER

i tried to accept answer and it tried to close question

Addie Baker

ASKER

Thank you so much!

Meir Rivkin

pick one of my posts and click accept as answer

Meir Rivkin

10x