How to bind datatable to dropdownlist.

jj6052

Member
Joined
May 8, 2013
Messages
6
Programming Experience
Beginner
A newbie here trying to bind a datatable to a dropdownlist. My page loads with no errors but dropdownlist is blank. On aspx page dropdownlist is named lotnumberlist. This is the code behind.
Imports System
Imports System.Data.SqlClient
'
Imports System.Configuration
Imports System.Data
Imports System.Linq

Public Class AddItem
    Inherits System.Web.UI.Page

    Public Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim Constring As String = ConfigurationManager.ConnectionStrings("DBConn").ConnectionString

        Dim objCommand As New SqlCommand
        objCommand.CommandText = "Select Max(LotNumber) As MaxLotNumber From Items"
        objCommand.Connection = New SqlConnection(Constring)
        objCommand.Connection.Open()

        Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()
        Dim CurrentLotNumber As Int32
        Dim TopLotNumber As Int32

        If objDataReader.HasRows Then
            Do While objDataReader.Read()
                CurrentLotNumber = Convert.ToInt32(objDataReader(0))
                TopLotNumber = CurrentLotNumber + 5
                Response.Write("CurrentLotNumber " & CurrentLotNumber & "  Top Lot Number " & TopLotNumber)
            Loop

            'Dim Lotnumbers() As Integer = Enumerable.Range(CurrentLotNumber, TopLotNumber).ToArray()

            Dim LotTable As New DataTable()
            'LotTable.Columns.Add("LotNumber")
            LotTable.Columns.Add(New DataColumn("LotNumber", GetType(Int32)))

            For i As Integer = CurrentLotNumber To TopLotNumber
                'Response.Write(i)
                LotTable.Rows.Add(i)
            Next

            Dim LotNumberView As DataView = New DataView(LotTable)

            If LotTable.Rows.Count > 2 Then
                Dim Count As Int32 = LotTable.Rows.Count
                Response.Write("  Records Found" & Count)
            End If

            Dim LotNumberList As DropDownList = New DropDownList()
            ' Set the properties for the DropDownList control.
            LotNumberList.DataSource = LotTable
            LotNumberList.DataTextField = "LotNumber"
            LotNumberList.DataValueField = "LotNumber"
            LotNumberList.DataBind()
        Else
            Response.Write("No rows returned.")
        End If

    End Sub
End Class
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
The issue is that you're not using the DropDownList that you added to your page. You are creating a new DropDownList and binding that:
VB.NET:
Dim LotNumberList As DropDownList = [B][U]New DropDownList[/U][/B]()
If you want to use a DropDownList that already exists then use it; don't create a new one and use that.

By the way, your DataView seems to be pointless.
 

jj6052

Member
Joined
May 8, 2013
Messages
6
Programming Experience
Beginner
Thanks JM. You are correct and I thought my error is there. However I cant seem to find the right code to link the existing dropdown box. I tried FindControl unsuccessfully. How do I bind to existing dropdownlist by name? example?And you are correct the dataview is pointless. At one point I thought I had to have it to get things to work, same with the recordcount, it was just to help verify. I can see my records get into the table but linking to existing dropdown seems more challenging than I thought.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
You're trying to make this hard when it's not. You already know how to do it because you're already doing it. Your not doing anything by name. You access the control via a variable. You just need to use the existing member variable, which is created automatically when you add the control, instead of the local variable that you are declaring yourself. Simply get rid of that local variable and, if the names are the same, the code will just work as is.
 

jj6052

Member
Joined
May 8, 2013
Messages
6
Programming Experience
Beginner
Thanks JM. I seem to be still missing something. Imports System
Imports System.Data.SqlClient
' We need this namespace to access web.config file
Imports System.Configuration
Imports System.Data
Imports System.Linq
Public Class AddItem
Inherits System.Web.UI.Page
Dim LotNumberList As Object
Public Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim Constring As String = ConfigurationManager.ConnectionStrings("GrowDBConn").ConnectionString
Dim objCommand As New SqlCommand
objCommand.CommandText = "Select Max(LotNumber) As MaxLotNumber From PlantList"
objCommand.Connection = New SqlConnection(Constring)
objCommand.Connection.Open()
Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()
Dim CurrentLotNumber As Int32
Dim TopLotNumber As Int32
If objDataReader.HasRows Then
Do While objDataReader.Read()
CurrentLotNumber = Convert.ToInt32(objDataReader(0))
TopLotNumber = CurrentLotNumber + 5
Response.Write("CurrentLotNumber " & CurrentLotNumber & " Top Lot Number " & TopLotNumber)
Loop
 
Dim LotTable As New DataTable()
'LotTable.Columns.Add("LotNumber")
LotTable.Columns.Add(New DataColumn("LotNumber", GetType(Int32)))
For i As Integer = CurrentLotNumber To TopLotNumber
'Response.Write(i)
LotTable.Rows.Add(i)
Next
 
'Dim LotNumberList As DropDownList = New DropDownList()
' Set the properties for the DropDownList control.
LotNumberList.DataSource = LotTable
LotNumberList.DataTextField = "LotNumber"
LotNumberList.DataValueField = "LotNumber"
LotNumberList.DataBind()
Else
Response.Write("No rows returned.")
End If
End Sub
End
Class . I then receive the following error even though I have a dropdown list on the page with the name LotNumberList. Seems I have to define the link between the two and cant seem to get on target to resolve. With my line storing it to a new dropdown list , no error. But linking the two ????????

[h=1]Server Error in '/' Application.
[/h][h=2]Object variable or With block variable not set.[/h][FONT=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object variable or With block variable not set.

Source Error:


Line 51: 'Dim LotNumberList As DropDownList = New DropDownList()
Line 52: ' Set the properties for the DropDownList control.
Line 53: LotNumberList.DataSource = LotTable
Line 54: LotNumberList.DataTextField = "LotNumber"
Line 55: LotNumberList.DataValueField = "LotNumber"
[/FONT]
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
What's this for:
VB.NET:
Dim LotNumberList As Object
If you have added a DropDownList to your page with that name then that code wouldn't compile at all so you must have used a different name. Get rid of that useless variable declaration and then use the actual name of your DropDownList to refer to your DropDownList.

By the way, I fixed the formatting of your code in your first post and now you've posted more hard to read code. Please post your code as I have done in post #1.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
Does not work..........
Could you be any more vague? If it's not working then you're doing it wrong. Given the several things you were doing wrong previously, I wouldn't like to guess exactly what the issue is. Show what you're doing now (posted CLEARLY as I did for your code in post #1) and tell us EXACTLY what happens.

If it's not working and you've cleared out all the extra variables that you shouldn't have had, I can only assume that you do not have a control on that page with that name. If you type 'Me.' into the code window, do you see your DropDownList in the Intellisense listing? If not, do you see any other controls that you believe that you have on that page?
 

jj6052

Member
Joined
May 8, 2013
Messages
6
Programming Experience
Beginner
I do not have me.lotnumberlist. I have every other part on the page but not that one. It is named as its spelled and I know its there. But not available apparently. Not sure where to go from here.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,570
Location
Sydney, Australia
Programming Experience
10+
Remove it from the page in the designer/source and then add it back again. Either you've done something wrong or something is broken. Either way, hopefully that will fix it.
 
Top Bottom