[RESOLVED] fill textbox from dropdown thanks ACONYBEAR

[d2005]

i am trying to populate a drop down list from 3 columns in one the row

has anyone every did this before, it seems to be a complete nightmare

[aconybeare]

Have you got a few more details on this? Are the vals coming from a db? And are you trying to do it manually or are you using bind()?

Have you got someting to post here, as cut down as possible to keep it simple please.

When I populate dropdownlists from a DB I use a datareader and loop through the recordset, if this is the kind of thing you're trying to do I'll post a simple example.

Cheers Al

If I'm missing the point do let me know

[dogboydelta]

Here's some (pseudo) code to fill a 1-D array from 3 columns. Then you can pop the
array into a dropdown list:

SQLCommand1 = "SELECT a,b,c FROM tablex"

Dim myArray() as String
Dim myReader as SQLDataReader

myReader = SQLCommand1.ExecuteReader

Dim i as integer = 0

While(myReader.Read)
myArray(i) = myReader.Item(0)
myArray(i+1) = myReader.Item(1)
myArray(i+2) = myReader.Item(2)
i = i + 3
End While

[rothjm]

If you are pulling the data from a SQL server...this is how I did it:

http://www.vbforums.com/showthread.php?t=359584

[mendhak]

i am trying to populate a drop down list from 3 columns in one the row

has anyone every did this before, it seems to be a complete nightmare

Do you want all three concatenated? If so, perform the concatenation in your SQL Statement.

EG:

SELECT Field1 + ' ' + Field2 + ' ' + Field3 AS TheCompleteThing FROM TableName

[d2005]

thanks guys, im gonna chack out all these threads now im sure they will help,
basically the drop down will just contain like three words
msg default
custom1
custom2

which will have the drop down linked to the columns in databse,
i need it to say this because the mesages are to long
when i get it fixed ill mark resolved and post the code

[d2005]

i want it to say
if in drop down chosen fill textbox1 with the correspondong column
if that clears it up

[d2005]

Dim selectcommand As New SqlCommand
        SelectCommand = New SqlCommand("SELECT c_msg_default, c_msg_custom1, c_msg_custom2 from tb_comp_detail WHERE (c_companycode = '" & lblUser.Text & "')", oSQLConn)
        Dim myArray() As String
        Dim myReader As SqlDataReader
        myReader = selectcommand.ExecuteReader
 
        Dim i As Integer = 0
 
        While (myReader.Read)
            myArray(i) = myReader.Item(0)
            myArray(i + 1) = myReader.Item(1)
            myArray(i + 2) = myReader.Item(2)
            i = i + 3
        End While

so thats how far i am now,
howe can i move on to loading this to my ddl
i am getting an error now object reference

[aconybeare]

Hi,

This is the way I do it, I use a stored proc.

Private Sub LoadCountriesDDL(Optional ByRef sDefault As String = "0")
 
        ' Open the connection
        If myCon.State = ConnectionState.Closed Then myCon.Open()
 
        ' 2. Create the command object
        Dim myCmd As New SqlCommand
        myCmd.CommandType = CommandType.StoredProcedure
        ' Set command to create your SQL statement
        myCmd.CommandText = "up_GetCountries"
        ' Set the database connection
        myCmd.Connection = myCon
 
        Dim dr As SqlDataReader
        Dim i As Integer
 
        i = 0
 
        Try
            dr = myCmd.ExecuteReader()
 
            ddlCountry.Items.Add("[Select]")
            ddlCountry.Items(i).Value = "0"
 
            Do While dr.Read()
                i += 1
                ddlCountry.Items.Add(Convert.ToString(dr("Country")))
                ddlCountry.Items(i).Value = Convert.ToString(dr("Country_Code"))
                If sDefault.Trim = Convert.ToString(dr("Country_Code")) Then
                    ddlCountry.SelectedIndex = i
                End If
            Loop
 
        Catch ex As Exception
            ' Record any exceptions and exit
            message.Visible = True
            message.Text = "<p><font color=""red"">The following exception occurred: LoadCountriesDDL<br />" + ex.Message + "</font></p>"
        End Try
 
        dr.Close()
        If myCon.State = ConnectionState.Open Then myCon.Close()
    End Sub

[d2005]

using my above code in array how do i pop that into ddl

[aconybeare]

d2005,

I've never used this technique, but I would say it would be something like this

myddl.DataSource = myArray()
myddl.DataBind()

If you don't want to use my approach I would use Mendhak's technique which would remove a step thus making your code more efficient (I think).

My way allows you to set the default selected item where you don't know the index but know the value or text they previously chose ideal if allowing a user to edit their data.

and use the above to bind to the datareader

Dim selectcommand As New SqlCommand
SelectCommand = New SqlCommand("SELECT c_msg_default + ' ' + c_msg_custom1 + ' ' + c_msg_custom2 as threeFields FROM tb_comp_detail WHERE (c_companycode = '" & lblUser.Text & "')", oSQLConn)
Dim myReader As SqlDataReader
myReader = selectcommand.ExecuteReader
 
myddl.DataSource = myReader
myddl.DataBind()

[d2005]

this is how far ive got now

my code looks an absolute mess and have about 100 commented lines lol

when the page loads it does indeed fill my textbox to the default message, but if i change the drop down list to another selection nothin happens
i need it to change the textbox info from selection of drop down

any ideas

thanks

lstMessage.Items.Add(New ListItem("Default", "c_msg_default"))
        lstMessage.Items.Add(New ListItem("Message1", "c_msg_custom1"))
        lstMessage.Items.Add(New ListItem("Message2", "c_msg_custom2"))
 
        Dim imessage = lstMessage.SelectedItem.Value
 
        If lstMessage.SelectedItem.Value = "c_msg_default" Then
            Dim cmdselect As SqlCommand
            cmdselect = New SqlCommand("SELECT c_msg_default from tb_comp_detail  where(c_companycode = '" & lblUser.Text & "')", oSQLConn)
            txtMessageReply.Text = cmdselect.ExecuteScalar().ToString()
        ElseIf lstMessage.SelectedItem.Value = "c_msg_custom1" Then
            Dim cmdselect As SqlCommand
            cmdselect = New SqlCommand("SELECT c_msg_custom1 from tb_comp_detail  where(c_companycode = '" & lblUser.Text & "')", oSQLConn)
            txtMessageReply.Text = cmdselect.ExecuteScalar().ToString()
        ElseIf lstMessage.SelectedItem.Value = "c_msg_custom1" Then
            Dim cmdselect As SqlCommand
            cmdselect = New SqlCommand("SELECT c_msg_custom2 from tb_comp_detail  where(c_companycode = '" & lblUser.Text & "')", oSQLConn)
            txtMessageReply.Text = cmdselect.ExecuteScalar().ToString()
        End If

[aconybeare]

d2005,

Sorry, I posted the above without two necessary settings, here is a fully working snippet

oSQLConn.Open()
Dim cmd As New SqlCommand
cmd = New SqlCommand("SELECT c_msg_default, c_msg_custom1, c_msg_custom2 As ThreeFields FROM tb_comp_detail WHERE (c_companycode = '" & lblUser.Text & "')", oSQLConn)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader
 
With myDDL ' change this to whatever your DDL is called
   .DataSource = dr
   [b].DataTextField = "ThreeFields"
   .DataValueField = "ThreeFields"[/b]
   .DataBind()
End With
 
dr.Close()

[aconybeare]

d2005,

For a start (based on what you've got ie the value in your list is the name of your column) you can tidy your code up like this -

lstMessage.Items.Add(New ListItem("Default", "c_msg_default"))
            lstMessage.Items.Add(New ListItem("Message1", "c_msg_custom1"))
            lstMessage.Items.Add(New ListItem("Message2", "c_msg_custom2"))
 
            Dim imessage = lstMessage.SelectedItem.Value
            Dim cmdselect As SqlCommand
            Dim sSQL As New StringBuilder ' imports system.text
 
            sSQL.Append("SELECT " & lstMessage.SelectedItem.Value & _
                              " FROM tb_comp_detail" & _
                              " WHERE(c_companycode = '" & lblUser.Text & "')")
            
            cmdselect = New SqlCommand(sSQL.ToString, oSQLConn)
            txtMessageReply.Text = cmdselect.ExecuteScalar().ToString()

[d2005]

i did your above code acony and it seems to give me just the last message in the drop down, ie the value of default message 2
i really appreciate all of your replys and your help has given me good insight as to how the whole drop down works

what my web application needs to do is when the user selectes a value from the drop down then it fills the textbox txtmessage out

then when the user presses send i shall use this textbox to write to the database
my problem is filling the textbox frfom the users selection,
as you can see i have tried now a different approach

when the page first loads using my above code it does fill the txtbox with the default message but when i select a different message nothing happens,
it must be a setting or something

ill get there, ill let u know

[d2005]

acony that code abouve works great ,
the setting i have to change is in the post back
i have that code in a function bind message choice,
i think i need to put it into its own function with bindmessage choice working on its own just filling in the three choices

[aconybeare]

The reason your dropdown in only getting one value is I suspect because of your where clause?

Plus I'm not sure whether this is what you want or not

Html

<TR vAlign="top">
  <TD>Countries:</TD>
  <TD><asp:dropdownlist id="myDDL" runat="server" AutoPostBack="True" OnSelectedIndexChanged="myDDL_SelectedIndexChanged" /></TD>
</TR>
<TR vAlign="top">
  <TD>Full Name:</TD>
  <TD><asp:textbox id="txtSelectedName" runat="server" /></TD>
</TR>

CodeBehind

Public Sub myDDL_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles myDDL.SelectedIndexChanged
  txtSelectedName.Text = myDDL.SelectedItem.Value
End Sub

[d2005]

i think the advice you have given me earlier is working but my settings are not right,
ill show you what i have and tell you what it does

all in code behind file

If Not IsPostBack Then
            bindmessagechoice()
        End If

.................

Public Sub bindmessagechoice()
 
      
 
        lstMessage.Items.Add(New ListItem("Default", "c_msg_default"))
        lstMessage.Items.Add(New ListItem("Message1", "c_msg_custom1"))
        lstMessage.Items.Add(New ListItem("Message2", "c_msg_custom2"))
 
   oSQLConn.ConnectionString = "Data Source=(local);" & _
                "Initial Catalog=KIT;" & _
                "Integrated Security=SSPI"
        oSQLConn.Open()
 
        Dim imessage = lstMessage.SelectedItem.Value
        Dim cmdselect As SqlCommand
        Dim sSQL As New StringBuilder ' imports system.text
 
        sSQL.Append("SELECT " & lstMessage.SelectedItem.Value & _
                          " FROM tb_comp_detail" & _
                          " WHERE(c_companycode = '" & lblUser.Text & "')")
 
        cmdselect = New SqlCommand(sSQL.ToString, oSQLConn)
        txtMessageReply.Text = cmdselect.ExecuteScalar().ToString()
 
        oSQLConn.Close()
     
     
 
    End Sub

i know that this does in fact work because i have a datagrid with a select function that does something or other,
my drop down fills with the above three fields,
when the page is loaded the default message is displayed in the textbox as expected
if i change the index of my drop down nothing happens

but
i know it works because if i choose a different option from my drop down and then press select on my datagrid the textbox is filled as expected

i thin k im so close to getting this working and i know its to do with the postback thingy - lol

i have tried the selected index changed function and put the code in there
but it doesnt seem to be dynamic
if i choose a new item in the drop down the textbox should fill immediately

[d2005]

i think the advice you have given me earlier is working but my settings are not right,
ill show you what i have and tell you what it does

all in code behind file

If Not IsPostBack Then
            bindmessagechoice()
        End If

.................

Public Sub bindmessagechoice()
 
      
 
        lstMessage.Items.Add(New ListItem("Default", "c_msg_default"))
        lstMessage.Items.Add(New ListItem("Message1", "c_msg_custom1"))
        lstMessage.Items.Add(New ListItem("Message2", "c_msg_custom2"))
 
   oSQLConn.ConnectionString = "Data Source=(local);" & _
                "Initial Catalog=KIT;" & _
                "Integrated Security=SSPI"
        oSQLConn.Open()
 
        Dim imessage = lstMessage.SelectedItem.Value
        Dim cmdselect As SqlCommand
        Dim sSQL As New StringBuilder ' imports system.text
 
        sSQL.Append("SELECT " & lstMessage.SelectedItem.Value & _
                          " FROM tb_comp_detail" & _
                          " WHERE(c_companycode = '" & lblUser.Text & "')")
 
        cmdselect = New SqlCommand(sSQL.ToString, oSQLConn)
        txtMessageReply.Text = cmdselect.ExecuteScalar().ToString()
 
        oSQLConn.Close()
     
     
 
    End Sub

i know that this does in fact work because i have a datagrid with a select function that does something or other,
my drop down fills with the above three fields,
when the page is loaded the default message is displayed in the textbox as expected
if i change the index of my drop down nothing happens

but
i know it works because if i choose a different option from my drop down and then press select on my datagrid the textbox is filled as expected

i thin k im so close to getting this working and i know its to do with the postback thingy - lol

i have tried the selected index changed function and put the code in there
but it doesnt seem to be dynamic
if i choose a new item in the drop down the textbox should fill immediately

[d2005]

thank you acony bear all for your replyas mendhak and aconybear

thanks acony bear for your quick replys and you patience,
what ive actually done now is put a button beneath my saying use message
this actually has benifits for me as the textbox can be left then blank for people to write thier own messages

the code that has resolved this problem is aconybears code above there fore i shall not repost it,

once again thanks
i really appreciate it