Link to home
Start Free TrialLog in
Avatar of ITMcmcpa
ITMcmcpaFlag for United States of America

asked on

Rebind Dynamic DropDownList VB

I have a form that allows a user to request an update to a customer record based on the customer number.  Standard stuff like address and phone number.  There are two tables, one for the customer and one for the customer contacts.  I am using a dropdownlist to allow the user to select the contact associated to the customer that they want to update.

I am having difficulty getting the contact dropdownlist to rebind with the new contact names if the user enters a different customer number.  ex: They entered the wrong customer number.

It simply keeps the contact names of the contacts from the first customer number entered.  I've tried a databind on the sqldatasource and on the dropdown list in the button onclick event for the customer number lookup.  But still no luck.  Any ideas?

Here is what the dropdown list looks like:
<asp:DropDownList ID="DDContactNameaaa" runat="server" 
                    DataSourceID="SqlDataSourceContactDrop" DataTextField="ContactName" 
                    DataValueField="ClcContact" AppendDataBoundItems="true" CssClass="bold" ToolTip="Contact Selection" OnSelectedIndexChanged="DDContactNameaaa_SelectedIndexChanged" AutoPostBack="true">
                   <asp:ListItem Value=""></asp:ListItem>
                   <asp:ListItem Value="000000000">Add New</asp:ListItem>                   

Open in new window

Here is the SQLdatasource:
<asp:SqlDataSource ID="SqlDataSourceContactDrop" runat="server" 
                ConnectionString="<%$ ConnectionStrings:CHILTONMSQLConnectionString %>" 
                SelectCommand="SELECT ClcCltID, ClcContact, ClcFname, ClcLname, ClcPhone1, ClcPhone2, ClcBeeper, ClcFax, ClcEmail, ClcOther, ClcEmailInv, ClcEmailAR, ClcAddrSameAsClt, Clcaddr1, Clcaddr2, Clcaddr3, Clccity, Clcst, Clczip, Clccountry, ClcInvaddr1, ClcInvaddr2, ClcInvaddr3, ClcInvcity, ClcInvst, ClcInvzip, ClcInvcountry, ClcCategoryId, ClcPhoneSameAsClt, ClcTitle, ClcSal, ClcLname + ', ' + ClcFname AS ContactName FROM ClientContact WHERE (ClcCltID = @formcltid) AND (Deleted = 0) ORDER BY ContactName">
                    <asp:ControlParameter ControlID="TBClientID" Name="formcltid" 
                        PropertyName="Text" />

Open in new window

I'm sure there is a simple way of doing this but I have looked in my books and googled for a couple of days and nothing that I've tried works.

Avatar of PSSUser
Flag of United Kingdom of Great Britain and Northern Ireland image


Do you have AutoPostBack set to True for the Customer Number field?

If you do, can you post the definition of the Customer Number field so I can see if anything else might be the cause.

Avatar of ITMcmcpa


I didn't, but I changed it to AutoPostBack="true" and it didn't make a difference.

Here is the button click event sub:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        'call form reset
        MultiView1.ActiveViewIndex = "0"
        DropDownListMultiViewaaa.SelectedIndex = 0
        DDContactListDE.SelectedIndex = 0

        ' forced delay for ajax update panel / Progress testing
        ' System.Threading.Thread.Sleep(1000)

        Dim myCltNum As String
        Dim myEngNum As String

        If TBclientnum.Text.Contains(".") Then

            LBLwarning1aaa.Visible = True
            LBLwarning1aaa.Text = ""

            ' word before the "." ex:  12345.TAX  Would return 12345

            myCltNum = readCommand(TBclientnum.Text, 0)

            ' word after the "."  ex:  12345.TAX  Would return TAX

            myEngNum = readCommand(TBclientnum.Text, 2)

            TBCltID.Text = Trim(myCltNum)
            TBCltEng.Text = Trim(myEngNum)

            'Add a 0 to TBCltEng if a "." was used in the Client Number but a number was not entered for the Engagment after the ".".  0 is assumed to avoid an Exception error.

            If TBCltEng.Text = "" Then
                TBCltEng.Text = "0"
            End If

            'Assign Values to labels from Datasource

            Dim displayEngaddr1 As String
            Dim displayEngaddr2 As String
            Dim displayEngaddr3 As String
            Dim displayCinvaddr1 As String
            Dim displayCinvaddr2 As String
            Dim displayCinvaddr3 As String

            Dim dvSql As DataView =
      DirectCast(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
            For Each drvSql As DataRowView In dvSql
                LBLClientNameData.Text = drvSql("Cltname").ToString()
                LBLEngrelatedData.Text = drvSql("Engrelated").ToString()
                LBLSICDescriptionData.Text = drvSql("SICDescription").ToString()
                LBLCltAttnData.Text = drvSql("Cltattn").ToString()
                LBLClientPhoneData.Text = drvSql("Cltphone").ToString()
                LBLClientFaxData.Text = drvSql("Cltfax").ToString()
                LBLClientEmailData.Text = drvSql("CltEmail").ToString()
                LBLClientInvNameData.Text = drvSql("Cinvname").ToString()
                LBLMgrAcctData.Text = drvSql("CPPlname").ToString() & ", " & drvSql("CPPfname").ToString()
                LBLResPartnerData.Text = drvSql("CRPlname").ToString() & ", " & drvSql("CRPfname").ToString()
                LBLBillMgrData.Text = drvSql("CBMlname").ToString() & ", " & drvSql("CBMfname").ToString()
                LBLCoorPartnerData.Text = drvSql("CTRlname").ToString() & ", " & drvSql("CTRfname").ToString()
                TBClientID.Text = drvSql("ID").ToString()

                'Build physical address display field to prevent empty lines in the label

                If drvSql("Engaddr1").ToString() = "" Then
                    displayEngaddr1 = ""

                    ' move the text in the text box down one line if the addr3 field is empty.  This helps center in the text box and makes it easier to read.

                ElseIf drvSql("Engaddr3").ToString() = "" Then
                    displayEngaddr1 = vbCrLf & drvSql("Engaddr1").ToString() & vbCrLf
                    displayEngaddr1 = drvSql("Engaddr1").ToString() & vbCrLf
                End If

                If drvSql("Engaddr2").ToString() = "" Then
                    displayEngaddr2 = ""
                    displayEngaddr2 = drvSql("Engaddr2").ToString() & vbCrLf

                End If

                If drvSql("Engaddr3").ToString() = "" Then
                    displayEngaddr3 = ""
                    displayEngaddr3 = drvSql("Engaddr3").ToString() & vbCrLf

                End If

                'Physical Address display field

                TBPhysicalAddressData.Text = displayEngaddr1 & displayEngaddr2 & displayEngaddr3 & drvSql("Engcity").ToString() & ", " & drvSql("Engst").ToString() & " " & drvSql("Engzip").ToString()

                'Build mailing address display field to prevent empty lines in the label

                If drvSql("Cinvaddr1").ToString() = "" Then
                    displayCinvaddr1 = ""

                    ' move the text in the text box down one line if the addr3 field is empty.  This helps center in the text box and makes it easier to read.

                ElseIf drvSql("Cinvaddr3").ToString() = "" Then
                    displayCinvaddr1 = vbCrLf & drvSql("Cinvaddr1").ToString() & vbCrLf


                    displayCinvaddr1 = drvSql("Cinvaddr1").ToString() & vbCrLf

                End If

                If drvSql("Cinvaddr2").ToString() = "" Then
                    displayCinvaddr2 = ""
                    displayCinvaddr2 = drvSql("Cinvaddr2").ToString() & vbCrLf

                End If

                If drvSql("Cinvaddr3").ToString() = "" Then
                    displayCinvaddr3 = ""
                    displayCinvaddr3 = drvSql("Cinvaddr3").ToString() & vbCrLf

                End If

                If drvSql("Engstatus") = 1 Then
                    LBLwarning1aaa.Text = "Warning: This is an Inactive client."
                End If
                If drvSql("Engstatus") = 2 Then
                    LBLwarning1aaa.Text = "Information: This client is on Hold."
                End If

                ' Mailing Address display field

                TBMailingAddressData.Text = displayCinvaddr1 & displayCinvaddr2 & displayCinvaddr3 & drvSql("Cinvcity").ToString() & ", " & drvSql("Cinvst").ToString() & " " & drvSql("Cinvzip").ToString()

            If postcount <= 0 Then
                LBLwarning1aaa.Text = TBCltID.Text & "." & TBCltEng.Text & " was not found.  Check the client number and try again."

                MultiView1.Visible = False
                DropDownListMultiViewaaa.Visible = False
                LBLSwitchNoteaaa.Visible = False

                MultiView1.Visible = True
                DropDownListMultiViewaaa.Visible = True
                LBLSwitchNoteaaa.Visible = True
            End If

        ElseIf Not TBclientnum.Text.Contains(".") Then

            LBLwarning1aaa.Visible = True
            LBLwarning1aaa.Text = TBclientnum.Text & " is not a valid client number.  Please include the Engagement portion.  Ex. 1234.TAX"

            MultiView1.Visible = False
            DropDownListMultiViewaaa.Visible = False
            LBLSwitchNoteaaa.Visible = False
        End If

    End Sub

Open in new window

This is the button and customer number field:
<asp:TextBox ID="TBclientnum" runat="server" CssClass="bold" AutoPostBack="true"></asp:TextBox>
    <asp:Button ID="Button1" runat="server" Text="Lookup" CssClass="bold" />

Open in new window

Oops, sorry I was assuming the customer number was the TBClientID field.

I've only ever done this sort of thing where the field bound to the parameter of the SqlDataSource is the one the user is changing, usually a DropDownList itself.

I think that the form isn't picking up the change in the TBClientID field as it is being changed in code at the server side. You could either alter your query and parameter used by SqlDataSourceContactDrop to make use of the TBclientnum field/Customer Number (which will need you to include the Customer table in the SQL) instead of the customer Id or you will need to call the refresh from code.

To call from code I'm afraid I don't know whether this would just be a case of adding a line something like (I'm not sure it this is the correct way to pass the existing parameter details, like I said I've never done this in code!).

after line TBClientID.Text = drvSql("ID").ToString()
The customer number that the user enters is not the actual identity field in the database.  They will know the customer as  a number dot code.  ex. 12345.AUD.  
I have to split that field at the dot (.) as the number before the dot is one database field and the portion after the dot is a different field.  Those two fields together make a unique record.

I then store the identity of that customer in a field called TBClientID, so you are right that is the field that the contact is queried from in the one to many relationship. I set the field to visible and watch it as I click the button and it does change the identity in that field.

For some reason the sqldatasource does not pick up that change.

The code snippet that you gave above doesn't seem to be valid.  Unless I need to import something other than System.Data.SqlClient or System.Data.
Ah, sorry. I'm afraid we've reach the limit of my knowledge, I've never had to refresh an SQL Data source from code.

I think the issue with the data source not picking up the new TBClientID is just down to the fact it has been changed in code (server side) and not client side. Client Side would trigger the necessary events to cause a refresh, but I don't know how to call these from server side.

I'd be interested to see what the solution is. I just hope someone else on these forums is able to provide it.

Kind Regards,

just been looking into this a little more.
It looks like the line to refresh may be:
For some reason you seem to only need to pass the Empty arguments, not the existing arguments.

Since the command returns a data view you may need
Dim dvContact As DataView = SqlDataSourceContactDrop.Select(DataSourceSelectArguments.Empty)

You may also need a line:

I've been skimming for information:
That didn't make any difference, unfortunately.  I did notice that if I enter a different customer number in that has a contact, it is appending the dropdown.  So it is rebinding just not deleting the old information from the dropdown.
Avatar of ITMcmcpa
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
lol, cool.

Glad you got the issue resolved. You will need to get the call closed to get your points refunded. I can't recall if they've added a button for that or if you need to log a request in the support forums. Might be best not to ask for this to be deleted, as I'm sure other people will find the solution useful, even if you found it yourself :D
The author of any question at EE can now ask for it to be closed and have the points refunded. They can also ask for it to be deleted. EE's help pages might be useful in explaining these features, specifically:

As PSSUser is the only Expert participating here and appears to be happy with a refund to the Author, I'm starting the close process to accept the Author's self-solution and refund the points.

I trust this is acceptable to all.


Experts Exchange Moderator