dataview gets int, need to display value from another table.

[codeamazon]

I have a form which is primarily pulled from Table1. Table1.fieldA is an integer representing a project phase. In the current Access display the form displays not the integer, but a String stored in Table2 that holds integer/String pairs representing all the phases.

I'm trying to figure out the best way to reproduce this in my .NET formview. I could write code that would parse the int and display text, then reverse the process at save or insert, however then the code has to be updated if a new phase is added to the database.

Clearly it would be better to draw the String values from Table2 while preserving the binding to Table1 but I haven't figured out how.

Is this possible? Is there a best practice? I'm working in .NET 2.0, btw, so compatible answers appreciated (though if you're not sure, I can do that research.)

Thanks!

[lolafuertes]

In the form where you pull the data from Table1, change the select string to 'join' both tables using the integer identifying the phase on each one like
"SELECT [Table1].[CurrentPhaseInt], [Table2].[Description] FROM [Table1] INNER JOIN [Table2] ON [Table1].[CurrentPhaseInt] = [Table2].[PhaseIdInt] "

Hope this helps

[codeamazon]

Thank you -- but what I can't figure out is how do I display [Table2].[Description] in the interface but update/insert [Table1].[CurrentPhaseInt]? The user should select from a pulldown listing the Descriptions, but the field that gets updated is CurrentPhaseInt.

[lolafuertes]

Please, put what you've coded so far and a signal where you have doubts.

[codeamazon]

Please, put what you've coded so far and a signal where you have doubts.

I'm asking for help regarding how to architect the solution, not debugging, so there isn't really any code yet. If there were code, it would look something like :

<td class="label">Phase</td>

<td class="input"><asp:TextBox ID="ProjPhaseIDTextBox" runat="server"
Text='<%# Bind("ProjPhaseID") %>' /></td>

The problem is that I need to display "ProjPhasePrettyName" to the user, not "ProjPhaseID", however when I insert or update a record, I need to change the value of ProjPhaseID. So I need one thing for display and a different thing for actual binding to the db.

I presume I can do this by defining a swap to-from in the code, but then that has to be updated (and recompiled) every time the list of values in Table2 changes in the database. That seems like a poor architecture. I'm looking for a way that I can draw this automatically from Table2 in the database, or at the very least can put it outside the compiled code (lookup table or something?) so that it's easier to keep updated. I'm also asking if there is a best practice for solving the problem.

Thanks.

[lolafuertes]

Asumming table2 beewn the PaseId and descriptino table, you can bind this table as datasource for a dropdown listbox in your aspx page. On the dropdownlist task you can select table2 as the data source, select the description in the data text field to display, and select the phaseid for the data value field of the dropdown list.

Hope this helps

[codeamazon]

Thanks. Now that I've figured it out, I understand your answer. For the next confused person, I'll try to write it out.

In short, the dropdownlist is designed to do this. It's the fact that it's sort of "invisible" that had me confused. Here is an example :

<asp:DropDownList ID="SuffixDropDownList" runat="server" AutoPostBack="True" 
          DataSourceID="SuffixSqlDataSource" DataTextField="Suffixes" 
          SelectedValue='<%# Bind("ProjSuffix") %>'
          DataValueField="SuffixCode" EnableViewState="True">
      </asp:DropDownList>
      <asp:SqlDataSource ID="SuffixSqlDataSource" runat="server" 
          ConnectionString="<%$ ConnectionStrings:AnConnString %>" 
          SelectCommand="SELECT SuffixCode, SuffixCode + ' | ' + SuffixDesc as Suffixes FROM TblSuffixes">
          </asp:SqlDataSource>

So as I now understand it, in the DropDownList, the DataSourceID tells it to use the new DataSource designed just for this DropDown. The DataTextField is the thing to display (in this case a concatenation of fields from Table2) and the SelectedValue is bound to the datasource being used by the Form the DropDownList lives inside.

Or put another way, the explicit bind goes to the form's scope, while the implicit binds go to the locally named datasource.

Hope this helps someone else -- very easy once you understand, but I spent hours googling and asked to .NET folks I know and nobody seemed sure. The answer above was accurate, but I couldn't understand it as written until after I'd figured it out. Then I could see what it meant.