Solved

sqlSelectCommand.ExecuteReader() Not working on null fields

Posted on 2013-05-23
13
451 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
  • 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

791 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