Solved

sqlSelectCommand.ExecuteReader() Not working on null fields

Posted on 2013-05-23
13
449 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help parsing JSON in my VB.Net application 4 43
VS.net 2010 11 22
MS SQL Inner Join - Multiple Join Parameters 2 18
SQL Select Statement 2 21
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

19 Experts available now in Live!

Get 1:1 Help Now