Here is my problem. I have created a MSDE database on my local machine holding varrious magizine article information. I am trying to write a page that will allow me to querry the database for key words and show me information about articles that hold that information.
The problem is that when the code runs I get SqlException with the message: "Login failed for user 'SHQ-EGW2W2\ASPNET'. Reason: Not associated with a trusted SQL Server connection."
Here are my current stats:
OS : Win2K service pack 4
.Net version : 1.1.4322
MSDE : 2003 service pack 3
Language : C#
Here is the source code:
File: LibrarySearch.aspx.cs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace MagResource
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.CheckBox chkTitles;
protected System.Web.UI.WebControls.CheckBox chkSum;
protected System.Web.UI.WebControls.CheckBox chkAuth;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.CheckBox chkPub;
protected System.Web.UI.WebControls.TextBox txtSearch;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Button btnSearch;
protected System.Web.UI.WebControls.Label lblTest;
protected System.Web.UI.WebControls.Table tblDisplay;
public void DoSearch(object sender, EventArgs ea)
{
lblTest.Text = "";
// Error checking
if(!chkTitles.Checked && !chkSum.Checked && !chkAuth.Checked && !chkPub.Checked)
return;
if(txtSearch.Text.Trim().Length == 0)
return;
// End error checking
const string CONNECTION = "workstation id=\"myComp\";packet size=4096;Trusted_Connection=true;data source=localhost;persist security info=False;initial catalog=MagTestSQL";
SqlDataAdapter sqlda = new SqlDataAdapter();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strSQL = "SELECT Publications.Publication, Volume.Date, Volume.Volume, Volume.Number, Articles.Title, Articles.Summery, Articles.PageNumber, Authors.LastName, Authors.FirstName, Authors.MiddleName ";
strSQL = strSQL + "FROM Articles INNER JOIN Authors ON Articles.AuthorIndex = Authors.Indx INNER JOIN Volume ON Articles.VolumeIndx = Volume.Indx INNER JOIN Publications ON Volume.PublicationIndex = Publications.Indx ";
strSQL = strSQL + "WHERE ";
if(chkTitles.Checked)
strSQL = strSQL + SQLBuild("Articles.Title");
if(chkSum.Checked)
{
if(chkTitles.Checked)
strSQL = strSQL + " OR ";
strSQL = strSQL + SQLBuild("Articles.Summery");
}
if(chkAuth.Checked)
{
if(chkSum.Checked || chkSum.Checked)
strSQL = strSQL + " OR ";
strSQL = strSQL + SQLBuild("Authors.LastName") + " OR ";
strSQL = strSQL + SQLBuild("Authors.MiddleName") + " OR ";
strSQL = strSQL + SQLBuild("Authors.FirstName");
}
if(chkPub.Checked)
{
if(chkSum.Checked || chkSum.Checked || chkAuth.Checked)
strSQL = strSQL + " OR ";
strSQL = strSQL + SQLBuild("Publications.Publication");
}
strSQL = strSQL + "ORDER BY dbo.Articles.VolumeIndx, dbo.Articles.Title;";
lblTest.Text = strSQL;
try
{
if(dt != null)
dt.Dispose();
if(ds != null)
ds.Dispose();
if(sqlda != null)
sqlda.Dispose();
sqlda = new SqlDataAdapter(strSQL, CONNECTION);
sqlda.Fill(ds);
sqlda.AcceptChangesDuringFill = false;
dt = ds.Tables[0];
TableRow tr = new TableRow();
TableCell tc = new TableCell();
tc.Text = "Publication";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Published Date";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Volume #";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Issue #";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Title";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Summary";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Page #";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Author's Last Name";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Author's First Name";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Author's Middle Name/Intitial";
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(12);
tc.Font.Bold = true;
tr.Cells.Add(tc);
foreach(DataRow row in dt.Rows)
{
TableRow trow = new TableRow();
TableCell tcell = new TableCell();
tcell.Text = row["Publication"].ToString();
trow.Cells.Add(tcell);
tcell.Text = row["Date"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tcell.Text = row["Volume"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tcell.Text = row["Number"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tcell.Text = row["Title"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tcell.Text = row["Summery"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tcell.Text = row["PageNumber"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tcell.Text = row["LastName"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tcell.Text = row["FirstName"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tcell.Text = row["MiddleName"].ToString();
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Cells.Add(tcell);
tblDisplay.Rows.Add(trow);
tc.Font.Name = "Times New Roman";
tc.Font.Size = new FontUnit(8);
trow.Dispose();
}
tblDisplay.Visible = true;
}
catch(SqlException ************)
{
lblTest.Text = ************.Message;
}
catch(Exception ex)
{
lblTest.Text = ex.Message;
}
if(dt != null)
dt.Dispose();
if(ds != null)
ds.Dispose();
if(sqlda != null)
sqlda.Dispose();
}
private string SQLBuild(string ColumnName)
{
return "(" + ColumnName + " LIKE '%" + txtSearch.Text + "%')";
}
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
DoSearch(this, EventArgs.Empty);
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnSearch.Click += new System.EventHandler(this.DoSearch);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
File Name: LibrarySearch.aspx
<%@ Page language="c#" Codebehind="LibrarySearch.aspx.cs" AutoEventWireup="false" Inherits="MagResource.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:TextBox id="txtSearch" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 32px" runat="server"
Width="328px">Web</asp:TextBox>
<asp:Label id="Label2" style="Z-INDEX: 107; LEFT: 32px; POSITION: absolute; TOP: 64px" runat="server"
Width="96px" Height="24px" Font-Bold="True">Search Areas</asp:Label>
<asp:CheckBox id="chkPub" style="Z-INDEX: 106; LEFT: 320px; POSITION: absolute; TOP: 88px" runat="server"
Width="128px" Height="24px" Text="Publication Name"></asp:CheckBox>
<asp:CheckBox id="chkAuth" style="Z-INDEX: 105; LEFT: 240px; POSITION: absolute; TOP: 88px" runat="server"
Width="56px" Height="24px" Text="Authors"></asp:CheckBox>
<asp:CheckBox id="chkSum" style="Z-INDEX: 104; LEFT: 144px; POSITION: absolute; TOP: 88px" runat="server"
Width="56px" Height="24px" Text="Summaries" Checked="True"></asp:CheckBox>
<asp:Label id="Label1" style="Z-INDEX: 102; LEFT: 16px; POSITION: absolute; TOP: 8px" runat="server"
Width="88px" Height="24px" Font-Bold="True">Search Text</asp:Label>
<asp:CheckBox id="chkTitles" style="Z-INDEX: 103; LEFT: 72px; POSITION: absolute; TOP: 88px" runat="server"
Width="56px" Height="24px" Text="Titles" Checked="True"></asp:CheckBox>
<asp:Table id="tblDisplay" style="Z-INDEX: 108; LEFT: 16px; POSITION: absolute; TOP: 128px"
runat="server" Width="712px" Height="232px"></asp:Table>
<asp:Button id="btnSearch" style="Z-INDEX: 111; LEFT: 536px; POSITION: absolute; TOP: 80px"
runat="server" Width="137px" Text="Search"></asp:Button>
<asp:Label id="lblTest" style="Z-INDEX: 112; LEFT: 16px; POSITION: absolute; TOP: 376px" runat="server"
Width="240px" Height="24px">Label</asp:Label></form>
</body>
</HTML>
ASP.NET MSDE Connection string problems
