How can I populate ASPX Textbox controls using a SQL Inner Join statement in C#
My intention is to populate textbox controls on an ASPX page using a SQL Inner Join. One textbox control(txtContactNum) is to be populated from my CompanyContacts table, while the other two textboxes(txtCity, txtURL) are to be populated from the Companies table. Here are the SQL Inner Join statements I have tried in the btnSelectCompany Event Handler:
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 />
Below is the C# business logic
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
From comments on the question...
Incorrect syntax near '.'
You have a typo in your query:
Select Companies.CompanyNum, Companies.CompanyName, Companies.City, Companies.URL CompanyContacts.ContactNum FROM ...
Note the missing comma between the last two fields in the SELECT list. Selected fields need to be separated by a comma:
Select Companies.CompanyNum, Companies.CompanyName, Companies.City, Companies.URL, CompanyContacts.ContactNum FROM ...
here ---^
The following SQL statement works. (comm = new SqlCommand("Select Companies.CompanyNum, Companies.City, Companies.URL, CompanyContacts.ContactNum FROM Companies, CompanyContacts WHERE Companies.CompanyNum = @CompanyNum", conn);)It is listed as the second SQL statement in my abstract at the top of this post. The reason it did not work initially was because I was pointing to a database that did not contain any data in the CompanyContacts table.
链接地址: http://www.djcxy.com/p/44032.html