Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 27th, 2003, 08:02 PM
Registered User
 
Join Date: Aug 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default ASP.NET MSDE Connection string problems

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>


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connection string for MSDE srkarthik_82 General .NET 1 January 25th, 2007 01:35 AM
VS.NET 2003 and MSDE 2000 connection problem ponuman SQL Server 2000 4 October 28th, 2005 04:02 PM
Connect to MSDE in with ASP.NET hoeknu Classic ASP Databases 1 February 4th, 2004 09:17 AM
connection string to MSDE(SQL) database from ASP johngilbart BOOK: Beginning ASP 3.0 9 June 13th, 2003 06:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.