Jsp, Tomcat, J2Ee, Linux Web Hosting, Hibernate Programming Blog

Each record in the view s results represents a single transaction; the view always shows one record for every transaction made, up to the moment you see the results. In other words, if you ignored all fields except UserId and ItemId, you d see that the view contains exactly the same records as the Transactions table. However, the view offers a big advantage over the Transactions table alone. Each record in the view includes the user name, date of the transaction, paid status, OurItemId, ItemName, and ItemPrice fields. In other words, the view contains a lot more useful information that the Transactions table provides and offers greater control of your data in these ways: The way the data looks in the query results is of no importance. Users never see query results like those you re seeing in Web pages. They see the information in whatever format you present it to them and you have near-infinite choices there. Any time you call upon a view for information, it returns all the fields shown in the current query results including current data from the tables but the view doesn t contain any data at all. Each time it s executed, it has no choice but to get the specified information from the tables that are currently in the database. A view can only be used to retrieve data from a table not to edit data in the underlying table. That s an advantage because in a Web site, nine times out of ten you want a Web page to show data to the user but not to allow the user to edit the data. Why? Well (for instance), imagine an unscrupulous user editing the page to change the price of an item in your Items table to a penny and then ordering 100 of them. . . . Using a view to access data from the tables provides the added security of knowing that a user cannot gain any access whatsoever to data in the tables from which the view retrieves its records. Also, the fact that the Web server doesn t need to fuss with editing improves overall performance; data moves faster to the user s page from the server. To save a view, click the Close button in its upper-right corner and choose Yes when asked about saving your changes. Name the view (I ll call this one UsersAndItemsView1) and click OK. The view name is added to the list of views in Database Explorer. A more detailed view One weakness of the previous view is that it didn t include the user s e-mail address. It can t; the linked aspnet_Users table doesn t contain the user s e-mail address. That bit of information is stored in the aspnet_Membership table. Chapter 11: SQL Server Crash Course 251
Note: In case you are looking for affordable and reliable webhost to host and run your j2ee application check Vision J2ee Web Hosting services.

While you re designing a query, you can test it out at any time using any of these methods: Right-click some empty space in the Design surface and choose Execute SQL. Press Ctrl+R. Click the Execute Query button (red exclamation mark) in the toolbar. Choose Query Designer.Execute Query from the menu bar. The results of executing the query appear in the Results pane at the bottom of everything else. Figure 11-29 shows the results of executing the query from Figure 11-28. Although it might not look like much, at first glance, it could be the solution to many of your data processing problems. Figure 11-29: Results of executing the query. Figure 11-28: Fields that the query should retrieve are selected. 250 Part III: Personalization and Databases
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

any other object: Right-click the Views folder in Database Explorer and choose Add New View. A dialog box named Add Table opens. The first step in building the query is to choose the tables from which the query will get data. If there s a many-to-many relationship among selected tables, the view must also include the Transactions table that provides the link between tables. To add a table to the query, click its name in the Add Table dialog box, and then click OK. (For this example, I add the aspnet_Users, Transactions, and Items tables to the view.) Click the Close button in the Add Tables dialog box after choosing your tables. If you forget to add a table before clicking the Close button, choose Query Designer.Add Table from the menu bar, or click the Add Table button in the toolbar. Each table you add appears as a field list in the Diagram pane at the top of the Query Builder. Each field list shows the names of fields within the table. You can move and size the field list s little windows by using standard techniques (drag the title bar to move, drag any corner or edge to size). In most cases, the lines connecting tables by their key fields are added to the field lists, as in Figure 11-27. With the three items linked together, the next step is to choose which fields you want to see from these tables. You choose which fields you want the view to display by clicking the check box next to each desired field name. As you do so, each field name you check appears in the Criterion pane, just below the Diagram pane. Below the Criterion pane is the Show SQL pane, which shows the actual SQL statement that the Query Builder creates as you go. You can size those panes by dragging their borders up and down. You can choose which panes you want to hide by using buttons in the toolbar shown near the top of Figure 11-28; the figure also shows how the query looks after you choose some field names from tables in the Diagram view. Figure 11-27: Three tables joined in the Query Builder. Chapter 11: SQL Server Crash Course 249
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

pick and choose options, the Query Builder then writes the appropriate SQL statement for you. The Query Builder appears automatically whenever you perform some action that requires getting data from a database. You ll see examples in upcoming chapters; in this chapter, as a general example, we use the Query Builder to link tables together into a view. Creating a view In SQL Server, a view is a saved query. When you set up your site for Membership, VWD automatically created several views. They are listed under Views in Database Explorer. The name of each automatically-created view starts with vw_aspnet_ as shown in Figure 11-26. Never delete, change, or rename any view whose name starts with vw_aspnet_. Those views are created by the membership system, for the membership system, and they really don t like being monkeyed around with. To illustrate using the Query Builder to link users, transactions, and items, I ll create a view named UsersAndItemsView. You create a view as you would Figure 11-26: Views created by the membership system. 248 Part III: Personalization and Databases
You want to have a cheap webhost for your apache application, then check apache web hosting services.

Linking Tables Although the tables in a database store data, the way you get exactly the data you need, and only the data you need, when you need it, is through Structured Query Language, abbreviated SQL and often pronounced like sequel. You use SQL to create queries (also called SQL statements or SELECT statements) commands that describe exactly what data you want to retrieve from the database, and where to find that data. Here is an example of a simple SQL statement: SELECT * FROM Items This SQL statement, when executed, retrieves all columns and all records from the table named Items. (The * is short for all columns. ) If you want to retrieve only certain columns of data from a table, replace the * with the names of the columns the SQL statement should retrieve. For example, the following SQL statement retrieves data from the UserId and UserName (only) columns in the aspnet_Users table: SELECT UserId, UserName FROM aspnet_Users Both sample SQL statements just given retrieve all records from the table. The only time such an extraction doesn t happen is when that SQL statement includes a WHERE clause, which specifies which records to retrieve. The WHERE clause is often called a filter because it filters out any records you don t need. (Or, more accurately, it filters out the records that the current Web page doesn t need to show.) A WHERE clause limits the records retrieved from the data to those records that meet some criterion. For example, this SQL statement retrieves exactly one record (not all records) from the Items table: The record that has 10002 in its ItemId field: SELECT * FROM Items WHERE ItemId = 10002 If you wanted just the ItemName and ItemDescription fields from the table, and only the records for item 10002, then you replace the * with the names of columns you want, as follows: SELECT ItemName, ItemDescription FROM ItemsTable WHERE ItemId = 10002 Not to worry: You don t actually have to write the SQL statements yourself. And that s a good thing SQL statements can be far more complex than the examples shown here. To avoid all that tedious and error-prone typing, you can use the Query Builder to create your complex SQL statements. The Query Builder lets you pick and choose what you want to extract from your tables with ease and with a more intuitive graphical user interface. You Chapter 11: SQL Server Crash Course 247
Check Tomcat Web Hosting services for best quality webspace to host your web application.

To widen or narrow a column, click and hold on the bar to the right of that column s name, and then drag left or right. I ll need some sample data in my Transactions table to illustrate upcoming concepts. So I opened that table and added the records below. It s imperative that the value in each record s UserId field exactly matches an actual user s GUID in the aspnet_Users table. So, to enter GUIDs into records, I copied and pasted actual GUIDs from the aspnet_Users table. The TransactionId values are entered automatically. So when you get to that field, just press the Tab key to move to the next record without typing a TransactionId number. Dummies beware! Typing the GUIDs shown in Figure 11-25 into your own table will not work. Every GUID you manually enter into your own Transactions table must exactly match an actual GUID from your own aspnet_Users table. At this point, I have all the tables that the membership system created, plus my Items and Transactions tables. There is a natural many-to-many link between users in the Membership table and items in my Items table. The Transactions table contains the fields necessary to link many users to many items, and also provides a record of who purchased what and when. At this moment, SQL Server is not aware that there are relationships among these tables. You have to describe the relationships by linking tables together in queries. Figure 11-25: Sample data in the Transactions table. Figure 11-24: Adding some records to my Items table. 246 Part III: Personalization and Databases
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.

But, if you need some sample data to play around with in the tables you created yourself, you can manually type that data directly into the tables. It s not the most intuitive way to do it; there are some rules you have to play by. Never use the procedure described here to add data to any table whose name starts with aspNet_. Always use the Web Site Administration Tool to add, edit, and delete data in those tables. Use the method described only to enter some sample data into a table you created yourself. Here are some quick tips for entering data into fields: When you enter data into a record that contains an automaticallygenerated primary key (for example, the ItemId field in the Items table), you must leave that field empty (NULL). The field gets filled with a value automatically, after you ve filled in other fields in the record. When you re typing data into table fields, do not press Enter when you complete your entry. Instead, when you ve finished filling one field and want to move on to the next, press the Tab key. Or just click the next field in which you want to enter or edit data. The little pencil that appears after you fill in a field isn t part of the data in the field. It s only there to tell you the field has been edited since the table was first opened. When typing dollar amounts, don t type the dollar sign. There s no need to bother typing commas, either; they ll just be removed. For example, if you want to enter $1,234.56 into a money field, type 1234.56. In the Design surface, all dollar amounts have four digits to the right of the decimal point and no dollar sign. Don t let that bother you. As long as you re working in Database Explorer, how the data looks isn t important. If you don t have data to fill a field yet, you can leave the field empty (NULL), provided you allowed nulls in the design of the table. If you didn t, and you find that the table really needs to allow nulls in a field, go back to the table s definition and select the Allow Nulls check box for that field. After you ve filled in all the fields for a record, you can press Tab or Enter to complete that record. SQL Server validates the data and saves the record. If there are any problems, the record won t be saved. Instead, you ll see an error message (stating No row was updated) with a description of the problem. You have to click OK in that message box and fix the problem. This fix often involves clicking the faulty value and then pressing Escape to undo the entry currently in the column. The example in Figure 11-24 shows where I ve typed some sample (and fake) data into the Items table. The ItemDescription field contains much more text than can be displayed in the narrow column. But that text isn t important here. Chapter 11: SQL Server Crash Course 245
Visit our web design programs services for an affordable and reliable webhost to suit all your needs.

Populating Tables When you first create a table, it will be empty. So when you right-click the table name and choose Show Table Data, you ll see an empty record with the word NULL in each field. For example, right-clicking an empty Items table and choosing Show Table Data displays that table s contents. But because the table is brand new and empty, its contents consist of a single empty record that has the word NULL (which means, in effect, blank) in each field, as shown in Figure 11-23. You can add data to the table just by replacing the NULLs with the data you want to store. That s not the only way to do it, or even the best way. For example, you d never want to type the data manually to populate the aspnet_ tables in the database (trust me on that one). You must use the Web Site Administration Tool, CreateUserWizard, and so forth to manage that data. Figure 11-23: Here s the Empty Items table, open and really empty. Figure 11-22: Two new tables (Items and Transactions), listed with other tables. 244 Part III: Personalization and Databases
In case you need affordable webhost to host your website, our recommendation is ecommerce web host services.

A Primary Key for Transactions As mentioned, if a table is on the one side of a one-to-many relationship, then that table must have a primary key to uniquely identify each record. In that regard, the Transactions table does not need a primary key, because it s on the many side of its relationships with users and items. However, there is another rule in Visual Web Developer that states that if you want to be able to edit a table through a Web page, then that table must have a primary key. So if you want to be able to edit the Transactions table through Web pages, you ll need to give that table a primary key. As always, the primary key need not have any special meaning it can just be a number that s automatically assigned to each new record, sort of like an order number on a paper order form. An automatically incremented int (integer) field will do nicely. Figure 11-21 shows an example where I ve added a field named TransactionId to the Transactions table. As you can see in its Column Properties, I set the fields Is Identity to Yes, set the Identity Increment to 1, and the Identity Seed to 20000. You can use any starting number you like. I just opted to number transactions 20000, 20001, 20002, and so forth arbitrarily. I also right-clicked the field name and chose Set Primary Key to make the field the table s primary key. When you re done defining fields for the Transactions table, close and save the design as you would any other object. In my example, I named the table Transactions as shown in Figure 11-21 and it will be added to the list of other tables already in the database. For example, Figure 11-22 shows my list of tables after creating the new tables named Items and Transactions. Figure 11-21: Final structure of the Transactions table. Chapter 11: SQL Server Crash Course 243
Please visit Domain Name Hosting services for high quality webhost to host and run your jsp applications.

must not be auto-numbered and must not allow nulls. So its Identity Specification property must be set to No, as shown in Figure 11-19. At this point in time, the Transactions table has all the fields it needs to record transactions and provide the links between users and items. Even though the table must contain the two fields just created here, there s no rule that says it can t contain other fields as well. For example, if you wanted to record the date of each transaction, add a smalldatetime field. If you want to keep track of whether a transaction has been paid, add a bit field. If items are such that a user might buy several of them at a time (say, pencils or kumquats), your Transactions table could include a Qty field to indicate how many items were purchased. If prices of items change over time, and you want to know exactly what the user paid in each transaction, you could include a SellingPrice field that records exactly what the user paid for the item. For the example in this book, I added two fields to my Transactions table: TransactionDate and its data type smalldatetime. The idea is to record the date and time of each transaction. I also added an IsPaid field and gave it the bit data type. In records, that field contains True for transactions that have been paid, and False for unpaid transactions. Figure 11-20 shows that sample Transactions table with the two additional fields defined. Figure 11-20: This sample Transactions table contains four fields. Figure 11-19: Second field in the Transactions table. 242 Part III: Personalization and Databases
If you are in need for chaep and reliable webhost to host your website, our recommendation is http web server services.