locked
how to get a value from two tables same ddbb working with code behind VB RRS feed

  • Question

  • User1740368681 posted
    Hi, I need your help to find a way to get a value from a DDBB using code behind.
    I have two DDBBs, one table (basic table) with basic data and another one (city table) with a list of cities and a code for each city.
    I want to add the city code into the basic table. The problem is that I am showing on the webpage the name of the city, no the code... but I am not able to get the code that I want to save into the basic table.
    
    
    this is the code to open the DDBBs:
    <asp:SqlDataSource ID="sqlDS" runat="server" 
            ConnectionString="<%$ ConnectionStrings:psCS %>" 
            InsertCommand="INSERT INTO [basic] ([A], [B], [IDcity]) VALUES (@A, @B, @IDcity)" 
            OldValuesParameterFormatString="original_{0}"
            DataSourceMode="DataSet"
            SelectCommand="SELECT * FROM basic INNER JOIN city ON city.ID = basic.IDcity" OnSelected="sqlDS_Select">
            <InsertParameters>
                <asp:Parameter Name="A" Type="String" />
                <asp:Parameter Name="B" Type="String" />
                <asp:Parameter Name="IDcity" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
    
        <asp:SqlDataSource ID="sqlDScity" runat="server" 
            ConnectionString="<%$ ConnectionStrings:psCS %>"
            DataSourceMode="DataSet" 
            SelectCommand="SELECT * FROM [city]">
        </asp:SqlDataSource>

    here the dropdownlist to show the cities:

               <asp:TemplateField HeaderText="City" SortExpression="city">
                    <EditItemTemplate>
                        <asp:DropDownList ID="ddlCity" runat="server" width="100" DataSourceID="sqlDScity" DataTextField="city" DataValueField="city" SelectedValue='<%# Bind("city") %>'></asp:DropDownList>
                    </EditItemTemplate>
                    <ItemTemplate><asp:Label ID="lblCity" runat="server" width="100" Text='<%# Eval("city") %>'></asp:Label></ItemTemplate>
                    <FooterTemplate>
                        <asp:DropDownList ID="ddlfCity" runat="server" width="100" DataSourceID="sqlDScity" DataTextField="city" DataValueField="city" SelectedValue='<%# Bind("city") %>'></asp:DropDownList>
                     </FooterTemplate>
                </asp:TemplateField>


    code behind:

        Protected Sub Add(sender As Object, e As EventArgs)
            Dim control As Control = Nothing
            If (Not (gvEmpresa.FooterRow) Is Nothing) Then
                control = gvBasic.FooterRow
            Else
                control = gvBasic.Controls(0).Controls(0)
            End If
            Dim strA As String = CType(control.FindControl("tbfA"), TextBox).Text
            Dim strB As String = CType(control.FindControl("tbfB"), TextBox).Text
            Dim strIDCity As String = CType(control.FindControl("ddlfIDcity"), DropDownList).Text
            sqlDS.InsertParameters("A").DefaultValue = strA
            sqlDS.InsertParameters("B").DefaultValue = strB
            sqlDS.InsertParameters("IDcity").DefaultValue = strIDCity
            sqlDS.Insert()
    end sub 


    ***********

    so, once here, I am not able to get the city.ID value.

    table structure:

    basic.A

    basic.B

    basic.IDcity

    city.ID

    city.name

    *************

    instead to use:

    sqlDS.InsertParameters("IDcity").DefaultValue = strIDCity
    I would like to use:
    sqlDS.InsertParameters("IDcity").DefaultValue = strID

    *********

    but I do not know how to get or pass the city.ID value to the basic table.

    Tks!

    Tuesday, May 29, 2012 3:28 PM

Answers

  • User1954304945 posted

    Hi, I want to inform that DropDownList has “DataTextField” and “DataValueField” attributes, you can set city ID to “DataValueField”, and set city names to “DataTextField”. In this way, the city names will be displayed with DropDownList. Then you can get the selected city name and city id with the following codes:

    Dim cityID As String = CType(control.FindControl("ddlfIDcity"), DropDownList).SelectedItem.Value
    Dim cityName As String = CType(control.FindControl("ddlfIDcity"), DropDownList).SelectedItem.Text
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 1, 2012 5:54 AM

All replies

  • User1954304945 posted

    Hi, you can bind city ID to DropDownList “DataValueField”, for example:

    <asp:DropDownList ID="ddlCity" runat="server" width="100" DataSourceID="sqlDScity" DataTextField="city" DataValueField="ID" SelectedValue='<%# Bind("ID") %>'></asp:DropDownList>

    and then get city ID value with the following method:

    Dim strID As String = CType(control.FindControl("ddlfIDcity"), DropDownList).SelectedValue

    Wednesday, May 30, 2012 11:26 PM
  • User1740368681 posted

    Hi Allen,

    Yes you are right, the problem is the following, I have two fields in the city table, one called ID with numbers and unique, the other one called city, with the name of the city itself. I want to show the name of the city, not the ID, but I want to safe the ID into the basic table.

    If I put the name of the city into the ddl, how can I get the ID number related to that ID behind the scene.

    is something like:

    ddl showing the name of the city, and in the code behind I need to select the name, get the ID and save the ID into the other table. I know how to do the select command, but I do not know how to assign the ID code once the select command run.

    Dim strCity As String = CType(control.FindControl("ddlfIDcity"), DropDownList).SelectedValue
    sqlcommand = "select ID from city_table where city='" + strcity + "'"
    dim strID = [City_ID] ----> this is what I do not know how to do it.

    Tks!

    Thursday, May 31, 2012 12:08 PM
  • User1954304945 posted

    Hi, I want to inform that DropDownList has “DataTextField” and “DataValueField” attributes, you can set city ID to “DataValueField”, and set city names to “DataTextField”. In this way, the city names will be displayed with DropDownList. Then you can get the selected city name and city id with the following codes:

    Dim cityID As String = CType(control.FindControl("ddlfIDcity"), DropDownList).SelectedItem.Value
    Dim cityName As String = CType(control.FindControl("ddlfIDcity"), DropDownList).SelectedItem.Text
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 1, 2012 5:54 AM
  • User1740368681 posted

    you rock!!!

    Friday, June 1, 2012 8:09 AM