如何在C＃中使用SQL内部连接语句填充ASPX文本框控件

我的目的是使用SQL内部联接在ASPX页面上填充文本框控件 . 一个文本框控件（txtContactNum）将从我的CompanyContacts表中填充，而另外两个文本框（txtCity，txtURL）将从Companies表中填充 . 以下是我在btnSelectCompany事件处理程序中尝试过的SQL内部连接语句：

• comm = new SqlCommand（"Select CompanyContacts.ContactNum, Companies.CompanyNum, Companies.CompanyName, Companies.City, Companies.URL FROM Companies INNER JOIN CompanyContacts ON CompanyNum = @CompanyNum;"，conn）;

• comm = new SqlCommand（“Select Companies.CompanyNum，Companies.CompanyName，Companies.City，Companies.URL CompanyContacts.ContactNum FROM Companies，CompanyContacts WHERE CompanyNum = @CompanyNum”，conn）;

• comm = new SqlCommand（“Select CompanyName，City，URL FROM Companies WHERE CompanyNum = @CompanyNum”，conn）;

<asp:Calendar ID="calDateOfPosting" SelectionMode="Day" ShowGridLines="True" OnSelectionChanged="DateSelection_Change" runat="server">
    <SelectedDayStyle BackColor="Yellow" ForeColor="Red"></SelectedDayStyle>
</asp:Calendar>
<span class="widelabel">Date Of Posting:</span>
<asp:TextBox ID="txtDateOfPosting" runat="server" />




<span class="widelabel">Company:</span>
<asp:DropDownList ID="ddlCompanies" runat="server">
</asp:DropDownList>

<asp:Button ID="btnSelectCompany" Text="Select Company" runat="server" 
    onclick="btnSelectCompany_Click" />     




<span class="widelabel">Contact Number:</span>
<asp:TextBox ID="txtContactNum" runat="server" ReadOnly="True" BackColor="#CCCCCC" />




<span class="widelabel">Job Type:</span>
<asp:DropDownList ID="ddlJobType" runat="server">
    <asp:ListItem>Software/Development</asp:ListItem>
    <asp:ListItem>Networking</asp:ListItem>
</asp:DropDownList>




<span class="widelabel">Posting Source:</span>
<asp:DropDownList ID="ddlPostingSource" runat="server">
    <asp:ListItem>Select Posting Source</asp:ListItem>
    <asp:ListItem>NEIT</asp:ListItem>
    <asp:ListItem>Web Search Engines</asp:ListItem>
    <asp:ListItem>Tech Collective</asp:ListItem>
</asp:DropDownList>




<span class="widelabel">Description:</span>
<asp:TextBox ID="txtDescription" runat="server" />




<span class="widelabel">City:</span>
<asp:TextBox ID="txtCity" runat="server" ReadOnly="True" BackColor="#CCCCCC" />




<span class="widelabel">URL:</span>
<asp:TextBox ID="txtURL" runat="server" ReadOnly="True" BackColor="#CCCCCC" />




<span class="widelabel">Attachment:</span>
<%--<asp:TextBox ID="TextBox1" runat="server" />--%>
<asp:FileUpload ID="FileUpload1" runat="server" />





<span class="widelabel">Cover Letter Submitted?:</span>
<asp:DropDownList ID="ddlCoverLetter" OnSelectedIndexChanged="Letter" AutoPostBack="true"  runat="server">
    <asp:ListItem>No</asp:ListItem>
    <asp:ListItem>Yes</asp:ListItem>
</asp:DropDownList>




<asp:Panel ID="pnlLetter" runat="server">
<p><b>Date Cover Letter Submitted:</b></p>
<asp:Calendar ID="calCLDateSubmitted"  SelectionMode="Day" ShowGridLines="True" OnSelectionChanged="CLDateSubmitted_Change" runat="server">
    <SelectedDayStyle BackColor="Yellow" ForeColor="Red"></SelectedDayStyle>       
</asp:Calendar>
<span class="widelabel">Date Of Submission:</span>
<asp:TextBox ID="txtCLDateSubmitted" runat="server" />
</asp:Panel>

<span class="widelabel">Resume Submitted?:</span>
<asp:DropDownList ID="ddlResume" OnSelectedIndexChanged="Resume" AutoPostBack="true"  runat="server">
    <asp:ListItem>No</asp:ListItem>
    <asp:ListItem>Yes</asp:ListItem>
</asp:DropDownList>




<asp:Panel ID="pnlResume" runat="server">
<p><b>Date Resume Submitted:</b></p>
<asp:Calendar ID="calRDateSubmitted"  SelectionMode="Day" ShowGridLines="True" OnSelectionChanged="RDateSubmitted_Change" runat="server">
    <SelectedDayStyle BackColor="Yellow" ForeColor="Red"></SelectedDayStyle>       
</asp:Calendar>
<span class="widelabel">Date Of Submission:</span>
<asp:TextBox ID="txtRDateSubmitted" runat="server" />
</asp:Panel>




<span class="widelabel">Comments:</span>
<asp:TextBox ID="txtComments" TextMode="MultiLine" runat="server"></asp:TextBox>

下面是C＃业务逻辑

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

public partial class JobPostings_JobPostings : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {

        pnlLetter.Visible = false;
        pnlResume.Visible = false;
        LoadCompanies();
    }

}


private void LoadCompanies()
{

    SqlConnection conn;
    SqlCommand comm;
    SqlDataReader reader;

    string connectionString = ConfigurationManager.ConnectionStrings["jobSearchDB"].ConnectionString;
    conn = new SqlConnection(connectionString);
    comm = new SqlCommand("Select CompanyNum,CompanyName FROM Companies", conn);
    try
    {
        conn.Open();

        reader = comm.ExecuteReader();


        ddlCompanies.DataSource = reader;
        ddlCompanies.DataValueField = "CompanyNum";
        ddlCompanies.DataTextField = "CompanyName";
        ddlCompanies.DataBind();
        reader.Close();


    } // End of Try Block
    catch (Exception ex)
    {
        lblErrorLabel.Text = "The Following Errors ocurred during the Data Read process: ";
        lblErrorLabel.Text += ex.Message.ToString();
    }
    finally
    {
        conn.Close();
    } // End of Finally Block

    ClearFormClass clearForm = new ClearFormClass();
    clearForm.ClearWebFormControls1(pnlAddJobPosting);
} // End of LoadCompanies()

protected void btnSelectCompany_Click(object sender, EventArgs e)
{
    SqlConnection conn;
    SqlCommand comm;
    SqlDataReader reader;
    string connectionString = ConfigurationManager.ConnectionStrings["jobSearchDB"].ConnectionString;
    conn = new SqlConnection(connectionString);
    // Create a SQL Query with an Inner Join for tables Companies and CompanyContacts so that CITY and URL can be returned from Companies, and ContactNum can be returned from CompanyContacts
// I have listed the SQL Queries which I have tried below. 

    comm = new SqlCommand("Select CompanyContacts.ContactNum, Companies.CompanyNum, Companies.CompanyName, Companies.City, Companies.URL  FROM Companies INNER JOIN CompanyContacts ON CompanyNum = @CompanyNum;", conn);
    comm = new SqlCommand("Select Companies.CompanyNum, Companies.CompanyName, Companies.City, Companies.URL CompanyContacts.ContactNum FROM Companies, CompanyContacts WHERE CompanyNum = @CompanyNum", conn);
comm = new SqlCommand("Select CompanyName, City, URL FROM Companies WHERE CompanyNum = @CompanyNum", conn);

    comm.Parameters.Add("@CompanyNum", System.Data.SqlDbType.Int);
    comm.Parameters["@CompanyNum"].Value = ddlCompanies.SelectedItem.Value;

    try
    {
        conn.Open();
        reader = comm.ExecuteReader();
        if (reader.Read())
        {

            txtContactNum.Text = reader["ContactNum"].ToString();
            txtCity.Text = reader["City"].ToString();
            txtURL.Text = reader["URL"].ToString();

        } // End of IF Block
        reader.Close();

        btnClear.Enabled = true;
        btnCancel.Enabled = true;

    } // End of Try Block
    catch (Exception ex)
    {
        lblErrorLabel.Text = "Error Loading The Company Information For The Job Posting.
";
        lblErrorLabel.Text += ex.Message.ToString();
    } // End of Catch Block
    finally
    {
        conn.Close();
    }
} // End of btnSelectCompany_Click