如何在C＃中使用SQL Inner Join语句填充ASPX Textbox控件？
我的意图是使用SQL Inner Join在ASPX页面上填充文本框控件。 一个文本框控件（txtContactNum）将从我的CompanyContacts表中填充，而其他两个文本框（txtCity，txtURL）将从公司表中填充。 以下是我在btnSelectCompany事件处理程序中尝试的SQL Inner Join语句：
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" />
<br />
<br />
<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" />
<br />
<br />
<span class="widelabel">Contact Number:</span>
<asp:TextBox ID="txtContactNum" runat="server" ReadOnly="True" BackColor="#CCCCCC" />
<br />
<br />
<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>
<br />
<br />
<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>
<br />
<br />
<span class="widelabel">Description:</span>
<asp:TextBox ID="txtDescription" runat="server" />
<br />
<br />
<span class="widelabel">City:</span>
<asp:TextBox ID="txtCity" runat="server" ReadOnly="True" BackColor="#CCCCCC" />
<br />
<br />
<span class="widelabel">URL:</span>
<asp:TextBox ID="txtURL" runat="server" ReadOnly="True" BackColor="#CCCCCC" />
<br />
<br />
<span class="widelabel">Attachment:</span>
<%--<asp:TextBox ID="TextBox1" runat="server" />--%>
<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<br />
<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>
<br />
<br />
<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>
<br />
<br />
<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>
<br />
<br />
<span class="widelabel">Comments:</span>
<asp:TextBox ID="txtComments" TextMode="MultiLine" runat="server"></asp:TextBox>
<br />
<br />
以下是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.<br />";
lblErrorLabel.Text += ex.Message.ToString();
} // End of Catch Block
finally
{
conn.Close();
}
} // End of btnSelectCompany_Click
从对这个问题的评论...
“。”附近的语法错误
您的查询中存在拼写错误：
Select Companies.CompanyNum, Companies.CompanyName, Companies.City, Companies.URL CompanyContacts.ContactNum FROM ...
请注意SELECT列表中最后两个字段之间缺少的逗号。 选定的字段需要用逗号分隔：
Select Companies.CompanyNum, Companies.CompanyName, Companies.City, Companies.URL, CompanyContacts.ContactNum FROM ...
here ---^
以下SQL语句有效。 （comm = new SqlCommand（“Select Companies.CompanyNum，Companies.City，Companies.URL，CompanyContacts.ContactNum FROM Companies，CompanyContacts WHERE Companies.CompanyNum = @CompanyNum”，conn）;）它被列为我的第二条SQL语句摘要在这篇文章的顶部。 它最初不起作用的原因是因为我指向的数据库没有在CompanyContacts表中包含任何数据。
链接地址: http://www.djcxy.com/p/44031.html上一篇: How can I populate ASPX Textbox controls using a SQL Inner Join statement in C#