Solved

sqlSelectCommand.ExecuteReader() Not working on null fields

Posted on 2013-05-23
13
448 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now