none
Simple Ado.net connectivity question RRS feed

  • Question

  • Hi All,

     

    I'm a beginning asp.net programmer, and have been working through the asp.net quickstart. Now I am on to the database connectivity stuff and decided to check out the ado.net quickstart found at

     

    http://quickstarts.asp.net/QuickStartv20/howto/doc/adoplus/overviewcontents.aspx

     

    On my PC (windows XP home) I have installed a standard version of Microsoft Visual Studio 2005 along with the SQL Server Express that was installed at the same time. The samples in the ado.net quickstart use the Northwind database so I downloaded the Northwind database from Microsoft, the installer came with both a script and pre-built database files which were placed in the default install location

    C:\SQL Server 2000 Sample Databases

     

    I can attach these pre-built files in Visual Studio 2005, although I had to enable named pipes in the separate SQL Server Configuration Manager. Now I can see the tables etc from within Visual Studio 2005. I understand this is running the database as a user instance but I'm not entirely sure.

     

    Anyway my problem arises when I try to connect to the database and display the data in a webpage. Let's say I want to run the ado.net example "C# AdoOverview3.aspx"

    http://quickstarts.asp.net/QuickStartv20/util/srcview.aspx?path=~/howto/samples/adoplus/adooverview3/adooverview3.src

     

    which does this

     

    Code Snippet
    public class AdoOverview3 : System.Web.UI.Page
        {
            StringBuilder builder = new StringBuilder();
            protected System.Web.UI.HtmlControls.HtmlForm output;


            private void Page_Load(object sender, System.EventArgs e)
            {
                Run();
                this.output.InnerHtml = builder.ToString();

            }

            public void Run()
            {
                SqlDataReader myReader = null;


                SqlConnection mySqlConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind");
                SqlCommand mySqlCommand = new SqlCommand("select * from customers", mySqlConnection);

                try
                {
                    mySqlConnection.Open();
                    myReader = mySqlCommand.ExecuteReader();
                    builder.Append("<TABLE>");

                    builder.Append("<TR><TD>Customer ID</TD>");
                    builder.Append("<TD>Company Name</TD></TR>");

                    while (myReader.Read())
                    {
                        builder.Append("<TR><TD>" + myReader["CustomerID"].ToString() + "</TD>");
                        builder.Append("<TD>" + myReader["CompanyName"].ToString() + "</TD></TR>");
                    }
                }
                catch (Exception e)
                {
                    builder.Append(e.ToString() + "</BR>");
                }
                finally
                {
                    if (myReader != null)
                        myReader.Close();

                    if (mySqlConnection.State == ConnectionState.Open)
                        mySqlConnection.Close();
                }
            }

     

    I cannot connect to the database using this connection string. I have tried in vain various combinations and still can't get it to work. Please help me before I go insane, it seems the most difficult thing to do with some of these technologies is to actually get it all setup correctly as opposed to the actual coding.

     

    I guess my questions are these

    1. What is the correct connection string in this circumstance?

    2. Is there some issue using the pre-built databases?

    3. Is it better to run a separate SQL server express in these circumstances? ie not a user instance.

    4. If I want to create a database in visual studio, and execute a script like the ones that set up Northwind how do I do it?

    5. I tried to use command line tools like OSQL but they couldn't seem to connect to the database. What is the problem here?

     

    I know these questions seem somewhat basic, and I know the problem is probably something to do with permissions etc, but I just don't have the knowledge at this stage in ado.net, visual studio etc to resolve them. Most of my experience is only with database programming. Thanks for any help you can offer.

     

     

     

     

    Sunday, July 15, 2007 9:45 AM