[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

VB.net Reader not finding data

Hi

I am getting an error on the line marked XXXX and have no idea why because
my SQL statement pulls data through if tested in a normal query

  Sub Fill_ListBox1()
        Try
            Dim sSQL As String
            Dim oSegment1 As String
            Dim oSegment1Desc As String
            Me.ListBox1.Items.Clear()

            sSQL = "SELECT DISTINCT [Segment 1],[Segment 1 Desc] FROM Accounting"

            If My.Settings.DBType = "Access" Then
                Dim connection As New OleDbConnection(My.Settings.CS_Setting)
                Dim cmd As New OleDbCommand(sSQL, connection)
                connection.Open()
                Dim datareader As OleDbDataReader = cmd.ExecuteReader
                While datareader.Read

                    If Not datareader("Segment 1").Equals(DBNull.Value) Then 'XXXX
                        oSegment1 = datareader("Segment 1")
                        oSegment1Desc = datareader("Segment 1 Desc")
                        Me.ListBox1.Items.Add(oSegment1 & "-" & oSegment1Desc)
                    End If

                End While
                connection.Close()

            ElseIf My.Settings.DBType = "SQL" Then

                Dim connection As New SqlConnection(My.Settings.CS_Setting)
                Dim cmd As New SqlCommand(sSQL, connection)
                connection.Open()
                Dim datareader As SqlDataReader = cmd.ExecuteReader

                If Not datareader("Segment 1").Equals(DBNull.Value) Then
                    oSegment1 = datareader("Segment 1")
                    oSegment1Desc = datareader("Segment 1 Desc")
                    Me.ListBox1.Items.Add(oSegment1 & "-" & oSegment1Desc)
                End If

                connection.Close()

            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
0
Murray Brown
Asked:
Murray Brown
  • 7
  • 2
1 Solution
 
CodeCruiserCommented:
And the error is ...

I would change

If Not datareader("Segment 1").Equals(DBNull.Value) Then

to

If Not IsDBNull(datareader("Segment 1")) Then
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
invalid attempt to read where no data present - is the error
0
 
YZlatCommented:
wrap it in

while datareader.Read()
0
Independent Software Vendors: 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!

 
YZlatCommented:
Sub Fill_ListBox1()
        Try
            Dim sSQL As String
            Dim oSegment1 As String
            Dim oSegment1Desc As String
            Me.ListBox1.Items.Clear()

            sSQL = "SELECT DISTINCT [Segment 1],[Segment 1 Desc] FROM Accounting"

            If My.Settings.DBType = "Access" Then
                Dim connection As New OleDbConnection(My.Settings.CS_Setting)
                Dim cmd As New OleDbCommand(sSQL, connection)
                connection.Open()
                Dim datareader As OleDbDataReader = cmd.ExecuteReader
                While datareader.Read

                    If Not datareader("Segment 1").Equals(DBNull.Value) Then 'XXXX
                        oSegment1 = datareader("Segment 1")
                        oSegment1Desc = datareader("Segment 1 Desc")
                        Me.ListBox1.Items.Add(oSegment1 & "-" & oSegment1Desc)
                    End If

                End While
                connection.Close()

            ElseIf My.Settings.DBType = "SQL" Then

                Dim connection As New SqlConnection(My.Settings.CS_Setting)
                Dim cmd As New SqlCommand(sSQL, connection)
                connection.Open()
                Dim datareader As SqlDataReader = cmd.ExecuteReader
		While datareader.Read
                	If Not IsDBNull(datareader("Segment 1")) Then
                    	oSegment1 = datareader("Segment 1")
                    	oSegment1Desc = datareader("Segment 1 Desc")
                    	Me.ListBox1.Items.Add(oSegment1 & "-" & oSegment1Desc)
                	End If
		End While
                connection.Close()

            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub 

Open in new window

0
 
YZlatCommented:
Actually, why are you creating datareader twice? I'd rewrite your code altogether to make it more efficient.
You are executing the same sSQL both times aren't you?
0
 
YZlatCommented:
Oh, I see, because you are reading from SQL and Access databases. I just noticed that. try the code above
0
 
YZlatCommented:
I think the problem is that you use the same connection string for both Access and SQL databases, which is not possible.

I've noticed that you use My.Settings.CS_Setting for both connections
0
 
YZlatCommented:
SQL Server conenction string would be something like that:

  
"Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;" 

Open in new window


while for MS Access would be something like that:

 
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; User Id=admin; Password=" 

Open in new window

0
 
YZlatCommented:
or for newer versions of MS Access you should use ACE instead of JET and db extension is accdb

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\App1\Your_Database_Name.accdb; User Id=admin; Password=" 

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
That did the trick, thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now