ASP.NET

19-Jan-07 6:07

I’m developing a web application that uses SQL Server 2005 as its database.

In the SQL database I have two tables that have identical structures. The only difference in them is data contained in them. One holds Company Titles. The other one holds Department names used in the company.

The table schemas are:
tblTitle
TitleID Numeric Identity
Title VarChar(50)
Deleted Bit

tblDepartments
DeptID Numeric Identity
Department VarChar(25)
Deleted Bit

On my web application I have two identical web forms. The form I use for Departments has a SqlDataSource Control on it. The Page Load event is:

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

Dim strSelectSQL = "SELECT [Department], [DeptID] FROM tblDepartments WHERE [Deleted] = 0;"
SqlDataSource.ConnectionString = MRMFunctions.WebConfigConnString
SqlDataSource.SelectCommand = strSelectSQL
ListBox1.DataSourceID = DtaSource.ID.ToString
ListBox1.DataTextField() = "Department"
ListBox1.DataValueField() = "DeptID"
End Sub

The code is pretty straight forward.
1. Assign the SQL string to a variable.
2. Get the connection string from the web.config file.
3. Set the SqlDataSource control to the SQL string.
4. Bind the Listbox control to the SqlDataSource control.
5. Assign the ListBox.DataTextField to the “Department” field in the tblDepartments table.
6. Assign the ListBox.DataValueField to the “DeptID” field in the tblDepartments table.

When I select a Department from the list box and press the edit button it fires the ListBox1_SelectedIndexChanged event. The code is:

Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
strID = ListBox1.SelectedValue
strName = ListBox1.SelectedItem.Text
txtID.Text = strID
txtName.Text = strName
End Sub

This code is straight forward as well. Take the ListBox.SelectedValue and place it in the strID variable. Take the Listbox.SelectedItem.Text and place it in the strName variable. Assign the txtID.Text box to strID and txtName.Text to strName.

When I step through the code the strID will contain the ID number of the selected item and the strName will contain the name of the department.

Now for the problem:
I’m trying to accomplish the same thing, but without having to place the SqlDataSource Control on the web form. This form is for company titles, and its Page_Load event is:

Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Dim dr As SqlDataReader
Dim cn As New SqlConnection
Dim cd As New SqlCommand
cn.ConnectionString = MRMFunctions.WebConfigConnString
cd.Connection = cn
cn.Open()
Dim strSelectSQL = "SELECT [TitleID], [Title] FROM tblTitles WHERE [Deleted] = 0;"
cd.CommandText = strSelectSQL
dr = cd.ExecuteReader
While (dr.Read)
ListBox1.DataTextField = dr("Title")
ListBox1.DataValueField = dr("TitleID")
ListBox1.Items.Add(dr("Title"))
End While
cn.Close()
End Sub

What I’m doing here is declaring a SQL data reader, a SQL Connection and a SQL Command.

1. I set the connection string by reading it from the Web.Config file.
2. I make the connection.
3. I open the connection.
4. I set the variable strSelectSQL equal to the select statement I’m using to return my data with.
5. I set the SqlCommand command text equal to the strSelectSQL variable.
6. I execute the datareader to get the data out of the database.
7. I loop through the data and assign the ListBox.DataTextField to the “Title” field inside the datareader, the Listbox.DataValueField to the “TitleID” field inside the datareader, and add the “Title” to the listbox.

Logically this should be the same as the Page_Load event in the Departments page, but it isn’t.

When I select a Title from the ListBox and press the edit button the ListBox1_SelectedIndexChanged event operates diffently. The code is simular to the ListBox1_SelectedIndexChanged event in the Departments form, but instead of the strID variable returning the ID number of the title selected. I get the Title name. The strName gets assigned the Title name like it should, but there is no ID number for it.

The only explanation I can come up with is the DataTextField and DataValueField behave differently when using a SqlDataSource control compaired to a datareader.

Can someone please tell me what I’m doing wrong?

I have a form that requires five data sources and I would rather only have to hit the database once to populate the controls and the again to update the database.

Thank you,

Quecumber256

Guffa19-Jan-07 6:59

19-Jan-07 6:59

The DataTextField and DataValueField are not used at all when you are not databinding. What you are doing in the code is adding strings to the Items collection. The overload of the Add method that takes a string creates a ListItem object for you with the same string for text and value. That is why you are gettings the title as value.

What you should do is to create a ListItem from the Title and TitleID values and add to the Items collection.

Also, you have forgotten to close the data reader when you are done with it.

Quecumber256 wrote:
and assign the ListBox.DataTextField to the “Title” field inside the datareader, the Listbox.DataValueField to the “TitleID” field inside the datareader

No, you are not. You are setting the DataTextField to the value of Title field and setting the DataValueField to the value of the TitleID field. The reason that this does not result in an error, is that the properties are not used at all.

---
Year happy = new Year(2007);

Quecumber25619-Jan-07 7:19

19-Jan-07 7:19

Guffa wrote:
What you should do is to create a ListItem from the Title and TitleID values and add to the Items collection.

Can you show me an example of how to do this? I know I can concatenate the two fields together and place it in the list. However, I would like to just show the names in the list and have the ID number retrieved from something like the DataValueField.

Another Question:
Can I bind the ListBox control to the DataReader?
Example: ListBox1.DataSourceID = dr

Thank you,

Quecumber256

Guffa19-Jan-07 8:30

19-Jan-07 8:30

Quecumber256 wrote:
Can you show me an example of how to do this?

Sure.

ListBox1.Items.Add(new ListItem(dr.GetString("Title"), dr.GetString("TitleID")))

---
Year happy = new Year(2007);

ToddHileHoffer19-Jan-07 7:17

ToddHileHoffer

19-Jan-07 7:17

//Try this
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Dim dr As SqlDataReader
Dim cn As New SqlConnection
Dim cd As New SqlCommand
cn.ConnectionString = MRMFunctions.WebConfigConnString
cd.Connection = cn
Dim strSelectSQL = "SELECT [TitleID], [Title] FROM tblTitles WHERE [Deleted] = 0;"
cd.CommandText = strSelectSQL
ListBox1.DataTextField = dr("Title")
ListBox1.DataValueField = dr("TitleID")
cn.open
dr = cd.ExecuteReader
ListBox1.datasource = dr
ListBox1.databind
cn.Close()
End Sub

GameFly free trial

Quecumber25619-Jan-07 7:44

19-Jan-07 7:44

This sounded like a good idea, so I gave it a try. Unfortunately it didn't work. All it put in the list box was 17 "System.Data.Common.DbDataRecord". Exactly the number of titles I have stored in the DB.

I even modified the procedure a bit more like so:
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Dim dr As SqlDataReader
Dim cn As New SqlConnection
Dim cd As New SqlCommand
cn.ConnectionString = MRMFunctions.WebConfigConnString
cd.Connection = cn
cn.Open()
Dim strSelectSQL = "SELECT [TitleID], [Title] FROM tblTitles WHERE [Deleted] = 0;"
cd.CommandText = strSelectSQL
dr = cd.ExecuteReader
ListBox1.DataSource = dr
ListBox1.DataBind()
ListBox1.DataTextField = "Title"
ListBox1.DataValueField = "TitleID"
cn.Close()
dr.Close()
End Sub

It returned the same results.

Quecumber256

Guffa19-Jan-07 8:25

19-Jan-07 8:25

Quecumber256 wrote:
All it put in the list box was 17 "System.Data.Common.DbDataRecord". Exactly the number of titles I have stored in the DB.

That's because the code tries to put a data record in the DataTextField property. As the property only accepts a string, the object is converted to a string. As the object has not overridden the ToString method, it returns the name of the class of the object.

Quecumber256 wrote:
It returned the same results.

You have to set the DataTextField and DataValueField before performing the data binding (calling the DataBind method).

---
Year happy = new Year(2007);

Quecumber25619-Jan-07 8:59

19-Jan-07 8:59

This populated the listbox:
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Dim dr As SqlDataReader
Dim cn As New SqlConnection
Dim cd As New SqlCommand
cn.ConnectionString = MRMFunctions.WebConfigConnString
cd.Connection = cn
cn.Open()
Dim strSelectSQL = "SELECT [TitleID], [Title] FROM tblTitles WHERE [Deleted] = 0;"
cd.CommandText = strSelectSQL
dr = cd.ExecuteReader
ListBox1.DataSource = dr
ListBox1.DataTextField = "Title"
ListBox1.DataValueField = "TitleID"
ListBox1.DataBind()
dr.Close()
cn.Close()
End Sub

But there are still some problems.
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles ListBox1.SelectedIndexChanged
strID = ListBox1.SelectedValue
strTitle = ListBox1.SelectedItem.Text
txtID.Text = strID
txtTitle.Text = strTitle
End Sub

On the line strID=ListBox1.SelectedValue the strID variable is empty. The line below it is give me the error message, "Object reference not set to an instance of an object." Why does it need to be set to an instance of an object?

Thank you,

Quecumber256

Guffa19-Jan-07 9:06

19-Jan-07 9:06

The SelectedItem property returns null (Nothing in VB) if there is no selected item. You have to check for this before trying to use it.

Quecumber256 wrote:
Why does it need to be set to an instance of an object?

It has to be set to an instance of an object in order for you to access the properties of that object.

---
Year happy = new Year(2007);

ToddHileHoffer19-Jan-07 8:44

ToddHileHoffer

19-Jan-07 8:44

Sorry, I wasn't paying attention. I think this is correct.

Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Dim dr As SqlDataReader
Dim cn As New SqlConnection
Dim cd As New SqlCommand
cn.ConnectionString = MRMFunctions.WebConfigConnString
cd.Connection = cn
Dim strSelectSQL = "SELECT [TitleID], [Title] FROM tblTitles WHERE [Deleted] = 0;"
cd.CommandText = strSelectSQL
ListBox1.DataTextField = "Title"
ListBox1.DataValueField = "TitleID"
cn.open
dr = cd.ExecuteReader
ListBox1.datasource = dr
ListBox1.databind
cn.Close()
End Sub

GameFly free trial

This Works!!

Quecumber25619-Jan-07 9:19

Re: This Works!! [modified]

19-Jan-07 9:19

This works! I can populate the listbox and retrieve the ID numbers. It seems there is a generic class build just for this purpose.

Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Dim dr As SqlDataReader
Dim cn As New SqlConnection
Dim cd As New SqlCommand
cn.ConnectionString = MRMFunctions.WebConfigConnString
cd.Connection = cn
cn.Open()
Dim strSelectSQL = "SELECT [TitleID], [Title] FROM tblTitles WHERE [Deleted] = 0;"
cd.CommandText = strSelectSQL
dr = cd.ExecuteReader
While (dr.Read)
ListBox1.Items.Add(New ListItem(dr("Title"), dr("TitleId")))
End While
dr.Close()
cn.Close()
End Sub

Thanks for all the help guys.

Quecumber256

Guffa19-Jan-07 10:06