Field Concatenation in SQL Dataset for Combobox

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Stewart Lane
    #1

    Field Concatenation in SQL Dataset for Combobox

    I have read the threads on here about adding a calculated column to a dataset
    to get the concatenation of first and last name fields into a single name
    field and then binding the calculated field to the control on a form.

    I am coming to VB.NET and ADO.NET and SQL Server from Visual Foxpro with its
    embedded SQL. I'm used to being to combine fields on the fly inside my sql
    statement.

    Is it true that this can not be done using the above tools and in the above
    scenario?
    Must one always create caluclated fields by adding them to a dataset?

    Please note.. I'm not arguing for or against VFP vs VB... I just want to
    make sure I'm not missing an alternative that might exist that resembles the
    VFP way.

    Also.. while on the topic... I am having difficulty in getting the
    calculated field's name to show up as a display member option for a combobox
    control who's dropdown style is dropdown list.

    I am using this code in the form's load event without error but without the
    desired result:

    Customers1.Clea r()
    Dim dc As DataColumn
    dc = New DataColumn("Nam e")
    dc.DataType = System.Type.Get Type("System.St ring")
    dc.Expression = "lname + ', ' + fname"
    Customers1.Tabl es(0).Columns.A dd(dc)
    OleDbDataAdapte r1.Fill(Custome rs1)

    The code doesn't generate an error but the new datacolumn "Name" does not
    show up in the display member field on the properties sheet for the control.

    Before I tried using the calculated field, my code was this...

    Customers1.Clea r()
    OleDbDataAdapte r1.Fill(Custome rs1)

    ....and the display member showed the fields and I could bind them to the
    control.

    Please note I am able to see the calculated column when I preview the data
    set.
    Should my new code BE somewhere else rather than form load event?

    If you have the time to comment, I'd appreciate it.

    Tags: None
    • Cor Ligthert [MVP]
      #2
      Re: Field Concatenation in SQL Dataset for Combobox

      Stewart,

      It is not a strongly typed dataset so your fieldnames are from the fill, can
      you try to do that direct after the

      OleDbDataAdapte r1.Fill(Custome rs1)[color=blue]
      > Dim dc As DataColumn
      > dc = New DataColumn("Nam e")
      > dc.DataType = System.Type.Get Type("System.St ring")
      > dc.Expression = "lname + ', ' + fname"
      > Customers1.Tabl es(0).Columns.A dd(dc)
      >[/color]
      I am not sure of that, it is just a try.

      (I assume that lname and fname are exactly the columnnames case sensitive as
      in your select or in your databasetable)

      Cor


        Comment

        • Stewart Lane
          #3
          Re: Field Concatenation in SQL Dataset for Combobox

          Hi Cor,

          I tried moving the code as you suggested. It had no effect and I still can
          not see 'Name' as a display member. Your assumption is correct. I respected
          the spelling and case sensitivity of the field names exactly.

          I appreciate your reply though. Let me know if you can suggest anything else.

          I'm wondering if I should delete the adapter or/and dataset and recreate them.

          Stewart

          "Cor Ligthert [MVP]" wrote:
          [color=blue]
          > Stewart,
          >
          > It is not a strongly typed dataset so your fieldnames are from the fill, can
          > you try to do that direct after the
          >
          > OleDbDataAdapte r1.Fill(Custome rs1)[color=green]
          > > Dim dc As DataColumn
          > > dc = New DataColumn("Nam e")
          > > dc.DataType = System.Type.Get Type("System.St ring")
          > > dc.Expression = "lname + ', ' + fname"
          > > Customers1.Tabl es(0).Columns.A dd(dc)
          > >[/color]
          > I am not sure of that, it is just a try.
          >
          > (I assume that lname and fname are exactly the columnnames case sensitive as
          > in your select or in your databasetable)
          >
          > Cor
          >
          >
          >[/color]

            Comment

            • Cor Ligthert [MVP]
              #4
              Re: Field Concatenation in SQL Dataset for Combobox

              Stewart,

              I have created and added this sample to our website.

              http://www.windowsformsdatagridhelp.com/default.aspx?ID=e8fee3e3-3ca7-4bb0-81dc-f0b560f43e7c


              I hope this helps,

              Cor


                Comment

                • Stewart Lane
                  #5
                  Re: Field Concatenation in SQL Dataset for Combobox

                  Hi Cor,

                  I checked the tip and your site (nice by the way). It is clearly written
                  and I understand it but it does not address my problem directly. I think my
                  problem exists because I do not yet fully understand the ado.net
                  manipulations of datatables within datasets. I will continue my studies.

                  Thanks,

                  Stewart


                  "Cor Ligthert [MVP]" wrote:
                  [color=blue]
                  > Stewart,
                  >
                  > I have created and added this sample to our website.
                  >
                  > http://www.windowsformsdatagridhelp....c-f0b560f43e7c
                  >
                  > I hope this helps,
                  >
                  > Cor
                  >
                  >
                  >[/color]

                    Comment

                    Previous template Next
                    Working...