[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SqlDataReader Null Values

Posted on 2009-04-20
5
Medium Priority
?
730 Views
Last Modified: 2012-08-13
How can I handle null values with SqlDataReader?

Basically with the code attached I want to place items in a combo box if the field "deptname" has a value.
Dim con As New SqlConnection(ConfigurationManager.AppSettings("ConnString"))
con.Open()
 
        Dim cmd As New SqlCommand("SELECT * FROM tblDept", con)
        Dim reader As SqlDataReader = cmd.ExecuteReader()
 
        While reader.Read()
            'If reader("deptname")value is null then ignore
            Else
            'If reader("deptname") has a value 
            txtDept.Tag = reader("id").ToString
            txtDept.Items.Add(reader("deptname").ToString)
 
            End If
 
        End While
 
        reader.Close()

Open in new window

0
Comment
Question by:richard_gar
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24183994
simplest:
Dim con As New SqlConnection(ConfigurationManager.AppSettings("ConnString"))
con.Open()
 
        Dim cmd As New SqlCommand("SELECT * FROM tblDept WHERE deptname is not null ", con)
        Dim reader As SqlDataReader = cmd.ExecuteReader()
 
        While reader.Read()
            txtDept.Tag = reader("id").ToString
            txtDept.Items.Add(reader("deptname").ToString) 
        End While
 
        reader.Close()

Open in new window

0
 

Author Comment

by:richard_gar
ID: 24184044
no that is still adding all items to the combobox
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24184051
maybe the value is not null, but empty?
Dim con As New SqlConnection(ConfigurationManager.AppSettings("ConnString"))
con.Open()
 
        Dim cmd As New SqlCommand("SELECT * FROM tblDept WHERE deptname is not null AND rtrim(deptname) <> ''  ", con)
        Dim reader As SqlDataReader = cmd.ExecuteReader()
 
        While reader.Read()
            txtDept.Tag = reader("id").ToString
            txtDept.Items.Add(reader("deptname").ToString) 
        End While
 
        reader.Close()

Open in new window

0
 

Author Closing Comment

by:richard_gar
ID: 31572199
exactly what i needed
0
 

Author Comment

by:richard_gar
ID: 24184559
Also I wonder if you can help with this.
I have the code below When I click on an item in the listview the first click is fine it retrives my details but if I click on another item i get this error:

InvalidArgument=Value of '0' is not valid for 'index'. Parameter name: index



    Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged
 
        'On Error Resume Next
 
        Dim con As New SqlConnection(ConfigurationManager.AppSettings("ConnString"))
        con.Open()
 
        Dim cmd As New SqlCommand("SELECT * FROM tblPLU WHERE id =" & CLng(ListView1.SelectedItems(0).Tag), con)
        Dim reader As SqlDataReader = cmd.ExecuteReader()
 
        Call ClearScreenItems()
 
        While reader.Read()
            txtPLU.Text = reader("id").ToString
            txtName.Text = reader("itemname").ToString
            lblDept.Text = reader("deptlink").ToString
            lblGroup.Text = reader("grouplink").ToString
            txtBarcode.Text = reader("randomcode").ToString
            txtDept.SelectedIndex = reader("deptlink").ToString
            txtGroup.SelectedIndex = reader("grouplink").ToString
            txtPrice1.Text = reader("price1level1").ToString
            txtPrice2.Text = reader("price2level1").ToString
            txtPrice3.Text = reader("price3level1").ToString
            txtQTY1.Text = reader("qty1price").ToString
            txtQTY2.Text = reader("qty2price").ToString
            txtQTY3.Text = reader("qty3price").ToString
            txtKP.Text = reader("kp").ToString
            txtFlag2.Text = reader("pluflag2").ToString
            txtStockFlag.Text = reader("stockflag").ToString
            txtMinStock.Text = reader("minstock").ToString
            txtOption1.SelectedIndex = reader("window1").ToString
            txtOption2.SelectedIndex = reader("window2").ToString
            txtOption3.SelectedIndex = reader("window3").ToString
            txtOption4.SelectedIndex = reader("window4").ToString
            txtOption5.SelectedIndex = reader("window5").ToString
            txtOption6.SelectedIndex = reader("window6").ToString
            lblTax.Text = reader("taxrate").ToString
 
        End While
 
        reader.Close()
 
    End Sub

Open in new window

0

Featured Post

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!

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

867 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