OOhh Man :) the stupidity is Triumphs.
Ok. This one was Funny!
I had spend about 20 minutes, before i found this stupid problem :)
I can't believe that I missed this crappy GridView with the gvCustomers ID :)
So if you handType the code from the book, don't forget to create a simple GridView Control after the main code: Between the <form><asp:GridView
ID = gvCustomers runat="server"/></form> open/closing tags
Problem number 2. the
ConnectionString.
Now, I really don't understand why the authors never discuss this VERY Simple issue.
..Of course many of us will type the connection string as is presented in this Example.
But, MAAAN! As always, this connectionstring simply will not work with the SqlExpress2005 server, Why?
First: Simply because you will need to provide the unique connection string of your SQLServer Installed on your PC.
Second:Seems like you must provide a connection string before runtime.
Which means the connectionstring must appear in the webconfig before you gonna run this code. and that means that, the only way to make this work, is somehow to connect your project to the DataBase before running the code.
So where the hell you can find this Connectionstring?
This is very simply. If the connection string of this example will not work,and it probably will not work :)
You will probably receive some kind of NULLREFERENCE EXCEPTION error, that says:
I can't find your server man. So if you have this problem, do the following:
* Switch to design-mode.
* Drag the
SqlDataSource Control to the white-Page(This will create a SqlDataSource Control on the screen)
* Open theSmartTagMenu of the SqlDataSource Control
* Choose the:
ConfigureDataSource Link and click it.
* Then click the
New Connection... Button.(Then click the Next Button)
* Notice, when the Add Connection window pops up, the (Server name: field) probably will be
empty.
*
What server name to provide?
First you must provide the
machine key,and right after the machine key, you will provide the sqlexpress name.
Example: K-N3TI8E2ZJ61H2\SQLEXPRESS
***Notice that the Full SqlServer2005 will probably need only the machine key:
Example: K-N3TI8E2ZJ61H2
* After this, simply select your
prefered DataBase from the DropDownList, and click Test Connection button, to verify your connection. Then click OK button, and follow
these steps.
*
Do not change the Default name in the Step[2]The default name will probably be:NorthwindConnectionString
After finishing the wizard simply click the finish button.
The above steps will connect/bind your SqlDataSource Control to the SQL2005Server.
* Ok, now switch back to the source View, and check the connection string of your SqlDataSource Control.
You will probably see something like this:
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
* Simply Copy the name after the COLON: NorthwindConnectionString
* So now instead typing:
MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
Type this:
MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
The magic behind the scene
So why I suggested you to create this SqlDataSource Control?
Simply because, by creating this control and bind it to the SqlExpress2005 Server using the
wizard.
This creates an internal connection string, in the
WEB.CONFIG file.
What it does, it gives you a chance to see the real connection that SqlDataSource had created for you automatically,and it's Undoubtedly connects to the database before a runtime!
So now your project will connect to the database, and you'll get a chance to discover
how your connection string is looks.No need to guess it :)!!!
In a case you already connected to your database and your connection string doesn't work, simply open the webConfig file and check the NAME of your connection string that connects to your data base. Then simply provide the same exact name to your connection string in the aspx page.
WebConfig------------------
<connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data Source=K-N3TI8E2ZJ61H2\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
So now, once you connected with the SqlServerExpress you can easily figure out what the real name of your connection string will be. it will probably be the same connection string that you provided in the Add connection
wizard STEP2 remember? NorthwindConnectionString
is a default NAME!
As you see from the
Image, you can easily change here the name of the connectionstring to any name you want. So, if you want to change the name, simply change it in the web.config file, and then change it to exact same name in the
VB/c# code in aspx Page
Here is the code:
//---------------------------------------------------------------------------
<%@ Page Language="C#" %>
<%@ Import Namespace ="System.Data" %>
<%@ Import Namespace= "System.Data.SqlClient" %>
<%@ Import Namespace ="System.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack)
{
SqlConnection MyConnection;
SqlCommand MyCommand;
SqlDataReader MyReader;
MyConnection = new SqlConnection();
MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
MyCommand = new SqlCommand();
MyCommand.CommandText = "SELECT TOP 3 * FROM CUSTOMERS";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConne ction);
gvCustomers.DataSource = MyReader;
gvCustomers.DataBind();
MyCommand.Dispose();
MyConnection.Dispose();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Listing12-1 The SqlConnection, SqlCommand, and SqlDataReader objects</title>
</head>
<body>
<h2>Listing12-1: The SqlConnection, SqlCommand, and SqlDataReader objects<br />
<span style ="font-family:Verdana; font-size:10pt; color:Red; font-weight:normal">Listing 12-1 shows the Connection, Command, and DataReader objects in action.<br />
It shows how to connect with the Northwind database, read the Customers table and show the results in a GridView control.</span></h2>
<form id="form1" runat="server">
<div>
<asp:GridView ID ="gvCustomers" runat ="server"></asp:GridView>
<br />
<br />
<br />
</div>
</form>
</body>
</html>
12-1 ConfigurationManager
