Solved

sqlSelectCommand.ExecuteReader() Not working on null fields

Posted on 2013-05-23
13
453 Views
Last Modified: 2013-05-23
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
0
Comment
Question by:bignadad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 39191873
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
 
LVL 2

Author Comment

by:bignadad
ID: 39191931
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 39192219
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 39192253
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
 
LVL 2

Author Comment

by:bignadad
ID: 39192462
@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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39192501
bloody converter, let me fix it
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39192516
    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
 
LVL 2

Author Comment

by:bignadad
ID: 39192529
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
 
LVL 2

Author Comment

by:bignadad
ID: 39192565
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
 
LVL 2

Author Comment

by:bignadad
ID: 39192566
i tried to accept answer and it tried to close question
0
 
LVL 2

Author Closing Comment

by:bignadad
ID: 39192568
Thank you so much!
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39192569
pick one of my posts and click accept as answer
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39192570
10x
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question