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.

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

ITMcmcpaConnect With a Mentor Author Commented:
I figured out my issue.  It was actually binding the new contacts, I just wasn't looking up a customer with contacts on the second lookup.  When I did lookup a customer with another contact, it appended the drop down list.  So I was seeing the contact from the previous customer plus the contact for the new customer lookup.

Just had to put in: DDContactNameaaa.Items.Clear() prior to the binding of the SQLDataSource for the contact dropdownlist.  

I also had a static item in the dropdownlist using listitem in the aspx page.  I had to append it programmaticaly via the codebehind after clearing the items from the dropdownlist.

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.

ITMcmcpaAuthor Commented:
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

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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()
ITMcmcpaAuthor Commented:
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:
ITMcmcpaAuthor Commented:
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.
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
All Courses

From novice to tech pro — start learning today.