Display Record Values from Two Tables in ASP.net Visual Web Developer Express Edition

To display data from two tables in ASP.net, we’ll need two tables with one or more identical fields. One field should act as a primary key on one table and the other should act as a foreign key or normal key on the other table. For the sake of example, let’s make two tables tblStudents and tblBooks using the following structures:

a. tblStudents

Column Name	Data Type
chrstudid	nchar(10)
chrstudfname	nchar(20
chrstudlnam	nchar(20)

b. tblBooks

Column Name	Data Type
chrbookid	nchar(10)
chrtitle	nchar(30
chrauthor	nchar(30)
chrstudid	nchar(10)

1. Start Visual Web Developer Express Edition.

2. Click File>New Website>Select Asp.net website from Visual Studio Installed Templates options>Click Ok.

3. Click View>Solution Explorer>Right-click App_Data from the solution Explorer>Add New Item>Select SQL Server Database from Visual Studio Installed templates options>Accept the default database.mdf name>Click Add.



4. The Database Explorer panel will then come into view. To create a new table>Right-click Tables from the database explorer panel>Select Add new table.



Enter the following column names, use chrstudid as a primary key:



5. Click the close(x) button when done. You will be prompted if you wanted to saves changes to the table, just Click the Yes button>Enter “tblStudents” in the Enter a name for the table textbox>Click Ok.

6. Click the + icon beside the Tables node in the database Explorer>Right-click tblStudents>Click Show Table Data.



Enter the following values:




7. Right-click Tables from the Database Explorer again then Select Add New Table.



Enter the following Column names using chrbookid as a primary key:





8. Click the close(x) button when done. You will be prompted if you wanted to saves changes to the table, just Click the Yes button>Enter “tblBooks” in the Enter a name for the table textbox>Click Ok.

9. Click the + icon beside the Tables node in the database Explorer>Right-click tblBooks>Click Show Table Data.



Enter the following values:



10. Click View Solution Explorer > Double-Click Default.aspx> Double-click the design button>Expand the Data category of the toolbox>Click and drag a SqlDataSource control to the Outline Designer window.



11. Click the configure data source link>Select Database.Mdf from the “Which data connection should your application use to connect to the database?” listbox>Next.


12. Click the Specify a custom SQL statement or stored procedure radio button>Next.

13. Click the SELECT tab then enter the following SQL statements in the SQL Statement textarea:



14. This statement retrieves the values of our specified column names from both tables. If you noticed, we’ve added tblStudents dot chrstudid to specify that the values should be retrieve from the tblStudents table since tblBooks table also has chrstudid.Moreover, we’ve added INNER JOIN to join our tables using a common field
due to the fact that chrbookid, chrtitle, and chrauthor exists on the other table.


15. Click next. You can click the test query button to preview the result then click the finish button.

16. Click a grid view control from the data category of the toolbox then drag it onto the Outline Designer. Select the name of your SQLDataSource from the gridview’s Choose Data Source List Box.



16. Press CTRL + F5 to test your application.

17. You should now see an output similar to the following screenshot:

Example 18: Race to Ten Text-Based Game

Problem: Make a simple Race to Ten text-based game. The details of the game is shown in the following screenshot:



Code:

// Learn more about F# at http://fsharp.net
open System
//change the console title
System.Console.Title<-"Race to Ten"
//adds the foreground and background color
System.Console.ForegroundColor<-ConsoleColor.DarkBlue
System.Console.BackgroundColor<-ConsoleColor.Gray
//clears the screen. This is to apply the background color once the
//console application is loaded
System.Console.Clear()
//display the game title screen
printfn "\t\t\t\tRace to Ten"
printfn "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\t\tPress any key to continue..."
System.Console.ReadLine()|>ignore
System.Console.Clear()
//display the game description screen
printfn "Instructions:"
printfn "In this game, each player(you vs. the computer) enters a number between 1 to 3"
printfn "The previously inputted number will be added to the present number"
printfn "The first player to enter a number that adds up to 10 wins the game"
printfn "Press Esc to quit the game"
printfn "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\t\tPress any key to continue..."

let mutable userkey=System.Console.ReadLine()
System.Console.Clear()
//declares our variables
let rndnum=new Random()
let mutable intsum=0
let mutable intusernum=0
let mutable intremain=0
//loop while sum is not equal to 10 and 
//the spacebar key has been pressed
while (intsum < 10 ) do
//computer generates a number
        printfn "\n\nAI's turn..."
        let intainum=rndnum.Next(1,3)
        printfn "AI num: %d" intainum
//accumulates the number to the
//value of sum
        intsum<-intsum + intainum
        printfn "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\t\tPress any key to continue...Esc to quit"
        System.Console.ReadLine()|>ignore
System.Console.Clear()
//display how many numbers more to go
//before 10
intremain<-intsum-10  
        printfn "%d more to go!" intremain
 //if the sum is equal to 10 
 //display "computer wins"
        if intsum>=10 then  
System.Console.Clear()
//reset the value of sum so that 
//the game will loop again
//remove intsum<-0 if you want the
 //game to end after one game
            printfn "Computer Wins!" 
            intsum<-0
            printfn "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\t\tPress any key to continue...Esc to quit"
            System.Console.ReadLine()|>ignore
System.Console.Clear()          
//otherwise ask for a number         
printfn "\n\nYour turn:" 
intusernum<-(int)(System.Console.ReadLine())
 //if the number exceeds 3 then
 //ask for a number again
        if intusernum>3 then
printfn "Number must be between 1 to 3"
printfn "You turn:"
intusernum<-(int)(System.Console.ReadLine())
            intsum<-intsum + intusernum
            System.Console.Clear()
//accumulates the inputted number to the value of sum
        intsum<-intsum + intusernum 
        intremain<-intsum-10  
        printfn "%d more to go!" intremain
        if intsum>=10 then
System.Console.Clear()
printfn "You Win!"
intsum<-0
            printfn "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\t\tPress any key to continue...Esc to quit"
            System.Console.ReadLine()|>ignore
System.Console.Clear()