Help with list box

[RPBLEA]

Is it possible when working with ASP, to read data into a list box via a stored procedure, and list an entire row of the dataabase?

I have created a search field which is a text in asp
I have created a button which calls on the search field and reads from a stored procedure,

The List box currenlty reads in only the current column that I have selected,
Is it possible for a List box to display an entire row? If this is possible can anyone direct me come code or a how to on how to do this?

 

[jmcilhinney]

As far as I am aware, you would have to concatenate the fields into a single string to do what you want. I think a ListView would achieve your aim better than a ListBox.

 

[kulrom]

jmcilhinney is alright ... actually, you can achieve that with ListBox as well (but it will be more tricky anyway) ... but why using of listbox if you have available ListView (is the best control i ever used in my life)

Cheers

 

[RPBLEA]

Well I'm not sure that I can use a listview because I am working in asp, but I'm thinking about a Datalist but I have never used one before, when i'm going to concatenate, Am I going to do that in a stored procedure or in .net itself?

 

[jmcilhinney]

You can concatenate fields using SQL but I've never tried it before. I'm not sure how non-text fields would be treated. Also, if you have null or empty fields you will end up chunks of white space in your concatenated string. If you handle the concatenation in code you can take control of these issues but it also means that you'll have to do more work if you want to use data binding and if you want to update the data.

 

[RPBLEA]

How to concatenate

So by concatenating, I looked this up I'm an idiot just using the & ?

 

[RPBLEA]

I don't understand how to concatenate in these fields i'm working with, I have a list box and would like to add multiple data to the field instead of the columns field below, I'll list the code, where would I concatenate?

This loads the Data List when the button search is clicked

Sub loaddatalist()

Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("strDBConn"))

Try

conn.Open()

Dim cmd As New SqlCommand("searchtest", conn)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@searchword", txtsearchcomments.Text)

cmd.Parameters.Add("@searchword1", txtsearchjack.Text)

cmd.Parameters.Add("@searchword2", txtsearchid.Text)

Dim myAdapter As New SqlDataAdapter(cmd)

Dim ds As New DataSet

myAdapter.Fill(ds)

listbox.DataSource = ds

listbox.DataValueField = "comments"

listbox.DataTextField = "comments"

listbox.DataBind()

Catch ex As Exception

lblerr.Text = ex.Message

End Try

conn.Close()

End Sub


I do not know if this matters but this is when the index of the list box is changed, I don't think it does, but here's what happens when An object is clicked inside the list box

Sub getlistboxitem()

Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("strDBConn"))

Try

conn.Open()

Dim cmd As New SqlCommand("searchlistbox", conn)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@comments", listbox.SelectedItem.Text)

Dim reader As SqlDataReader

reader = cmd.ExecuteReader()

If reader.HasRows Then

reader.Read()

txtid.Text = CStr(reader.GetInt32(0))

txtdevicetype.Text = reader.GetString(1) & " "

txtusertype.Text = reader.GetString(2) & " "

txtbldgroom.Text = reader.GetString(3) & " "

txtswitchname.Text = reader.GetString(4) & " "

txtvlan.Text = reader.GetString(5) & " "

txtblade.Text = CStr(reader.GetInt32(6)) & " "

txtport.Text = CStr(reader.GetInt32(7)) & " "

txtjack.Text = reader.GetString(8) & " "

txtipdn.Text = CStr(reader.GetInt32(9)) & " "

txttelco.Text = reader.GetString(10) & " "

txtdevice.Text = reader.GetString(11) & " "

txtdatemade.Text = reader.GetString(12) & " "

txtcomments.Text = reader.GetString(13) & " "

End If

Catch ex As Exception

lblerr.Text = ex.Message

End Try

conn.Close()

loaddatagrid2()

End Sub

 

[jmcilhinney]

Concatenation options

If you know that all your fields will be non-empty or you don't mind having chunks of white space in the returned value you can concatenate the record fields within the select statement like this:

SELECT field1 || ', ' || field2 ... || ', ' || fieldn FROM table

Note that '||' is the ANSI standard concatenation operator, while SQL Server uses '+' instead and MySQL uses CONCAT(field1, field2, ... fieldn). If you want the fields separate as well you can always select each field individually and add the extra concatenated field as well. If you want to use the fields individually in code you will have to do this.

If you don't want empty (zero-length string or null) fields to leave gaps in your field you will have to concatenate the fields in code and forgo the benefits of data binding. The '&' operator is simple to code but is a relatively inefficient method of concatenting strings. It creates a new String object every time it is used, so should only be used when there are few strings to concatenate. In your case, I would suggest using a System.Text.StringBuilder like so:

[color=Blue]Dim[/color] combinedFieldBuilder [color=Blue]As New[/color] System.Text.StringBuilder

[color=Blue] If[/color] field1.ToString() <> [color=Blue]String[/color].Empty [color=Blue]Then[/color]
  combinedFieldBuilder.Append(field1)
[color=Blue] End If[/color]

[color=Blue] If[/color] field2.ToString() <> [color=Blue]String[/color].Empty [color=Blue]Then[/color]
[color=Blue]   If[/color] combinedFieldBuilder.Length > 0
	combinedFieldBuilder.Append(", ")
[color=Blue]   End If[/color]

  combinedFieldBuilder.Append(field2)
[color=Blue] End If[/color]

[color=Green] '...[/color]

[color=Blue]  If[/color] fieldn.ToString() <> [color=Blue]String[/color].Empty [color=Blue]Then[/color]
[color=Blue]    If[/color] combinedFieldBuilder.Length > 0
 	combinedFieldBuilder.Append(", ")
[color=Blue]    End If[/color]
 
   combinedFieldBuilder.Append(fieldn)
[color=Blue]  End If[/color]

[color=Blue] Dim[/color] combinedField [color=Blue]As String[/color] = combinedFieldBuilder.ToString()

You could put this If block in a loop and iterate over the fields of a record.