I have started to blog on Adobe Flex: http://flex.tekyt.info

If you have any comments / questions about any post related to this blog, feel free to e-mail me at: shreyastonse@gmail.com

ShareThis

The DataGridView Control Control can be used to display the contents of a table from the database as per an SQL query of user’s choice.

Let us see an example, add a DataGridView from the ToolBox to a new Windows Form.

When the Form Loads, we will look to fill the DGV with the following code-

Dim cmd As New Odbc.OdbcCommand("Select number as 'Book Number',title as 'Book Name', author as 'Author', publisher as 'Publisher', no_of_copies as 'No. of Copies', number_issued as 'No Issued', sub_category as 'Sub Category',edition as 'Edition' from book_details", cn)

'cn is the ODBC Connection Object defined in earlier posts

        Dim adp As Odbc.OdbcDataAdapter
        adp = New Odbc.OdbcDataAdapter(cmd)
        Dim ds As New DataSet
        adp.Fill(ds, "book_details")
        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "book_details"
        Me.DataGridView1.AutoResizeColumns()

We are using a DataSet-the core of the ADO.NET disconnected architecture and is used to store data in a disconnected state. In accordance to the SQL query, the DataSet is filled using the DataAdapter (adp).

The DataGridView comes with several options- enabling the user to edit records, add records, delete records.

In order to retrive data displayed in the DataGridView we can use the following code- this piece of code is very useful and I have used it on several occasions:

Dim i As Integer
        i = DataGridView1.CurrentRow.Index
        Dim book_number as Integer  = DataGridView1.Item(0, i).Value
        

What we are doing in this piece of code is that we are are holding the current row’s index number in the variable i and thereafter the item in the first column in the variable book_number

ShareThis

We will see how we can use XML files in a VB.NET application. XML is used in many aspects of web development, often to simplify data storage.

In a Windows form, add a DataGridView and a Button. Change the text of this Button as ‘Save’.

The XML file that I am interested in-

    


  
    John Smith
    Computers
  

  
    Matthew Max
    Electrical
  

  
    Jay Yan
    Mechanical
  

The XML file that I have holds freshman records studying in an engineering school. We will look to display the fields ‘name’ and ‘branch’ in the DataGridView.

The ‘Save’ button should be able to save the changes made to the records in the DataGridView in the XML file.

We shall use a DataSet for reading the XML file. The Save Button will involve the Write function to the XML file invoked using the dataset.

The code-

Public Class Form1

    Dim ds As DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ds = New DataSet
        ds.ReadXml("C:\myxml\file1.xml") 'Path where your XML file exists on the system
        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "freshman" 'Records that you wish to display. Look at the XML file for reference
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ds.WriteXml("C:\myxml\file1.xml") 'Save the changes made in the DataGridView to be reflected in the XML file
    End Sub
End Class

This is an illustration of the usage of XML in VB.NET. You may use this in ASP.NET too. In order to specify the source for the file on the server, you may use ‘Server.MapPath()’ to specify the path for your XML file.

ShareThis

Visual Studio lets you develop Console Applications as well. Console Applications are command-line applications similar to C or Java programs. These applications allow you to take input, display characters on a DOS window.

In order to develop a Console Application, start Visual Studio, point to New Project and select ‘Console Application’.

The System.Console namespace supports the creation of Console Applications.

As with C and Java, the program execution begins with the Sub Main() - the entry point of the program.

Module Module1
Sub Main()
Dim nm As String
System.Console.Write("Enter your name: ")
nm = System.Console.ReadLine()
System.Console.WriteLine("Your name is: " & nm)
System.Console.ReadLine()
End Sub
End Module

This simple program accepts a string from the user and displays it on the console. As you can see, the code is quite similar to a program written in Java. In order to execute this program, press F5/ Debug->Start.

A DOS window pops up asking you to enter your name. This is due to the ‘System.Console.Write()’‘ method.

Similarly, ‘System.Console.ReadLine()’ is used to read a line from the console. The final ‘System.Console.ReadLine()’ is like the getch() used in C programs so that the console ‘waits’ for a keypress before termination.

ShareThis

In many applications we need tailor-made controls to suit our programming needs. Such controls can be created in VB.NET.

1. To create a user-defined control you have to use Windows Control Libray.
   Go to file –> New Project–> Windows Forms Contol Library –> (Give any desired name to your project) –> Ok.
2. You will find a Canvas. Now using the controls present in the toolbox create the required component (control).
3. Code the Control as you want it to work.
4. You can add user defined properties by using property keyword.
5. The size of the canvas is the size of the user defined control you are creating. So change the canvas size as required at the end.
6. After you have written the code check the control by debugging the project.
7. Now go to Build –> Build <component name>.
8. To add the Component (control) in the toolbox, right click in the empty area of the Toolbox –> Choose Items –> Click on browse. (This should be in a new VB.NET Windows Application)
   Go to the Project Folder (This time, its the Control Library Project you created) –> Bin –> Debug –> Select <componentname>.dll file
   Check the Component name in .NET Framework Components Tab –> OK.
9. The new Component (control) is added to your toolbox.

Ex: Creating a text-flipping banner control.

1. Create a windows library form and name it Banner.
2. In the Canvas drag two Textboxes and add a Timer.
3. Set the Timer’s enabled property true,Set the interval to say 1000 (unit:milliseconds).
4. Set some default Text in both the Textboxes.
5. Double click on the timer and write the below code.
6. Then write two properties named first and second which are used to change the text of the banner.
7. Drag one textbox on top of other and then reduce the canvas size such that it covers only the textboxes.
8. Debug the program, The Text of the banner oscillates between the two values of the textboxes.You can change the text of the first and second text box using property first and second respectively.
9. If it is working properly stop debugging and click on build –> build banner.

Public Class Banner

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Static f As Boolean
        If f = True Then
            Me.TextBox1.Visible = True
            Me.TextBox2.Visible = False
        Else
            Me.TextBox1.Visible = False
            Me.TextBox2.Visible = True
        End If
        f = Not f
    End Sub

    Public Property first() As String
        Get
            Return Me.TextBox1.Text
        End Get
        Set(ByVal value As String)
            Me.TextBox1.Text = value
        End Set
    End Property

    Public Property second() As String
        Get
            Return Me.TextBox2.Text
        End Get
        Set(ByVal value As String)
            Me.TextBox2.Text = value
        End Set
    End Property

End Class

In the above code within a property we see two more keywords
get:It returns a value to the caller.
set:It sets the value to the given property.

ShareThis

This is a project we made in ASP.NET. Its an examination system made in Visual Studio 2008 using a MySQL database. Please do take a look at it and suggest improvements. Also, if the project is vulnerable to any security threats, please do ping us back.

I hope you will like our attempt at coding this application.

Features-

User first needs to supply valid credentials to begin the test.

Randomly chooses 5 questions from a database of 10 questions.

The test is a timed test, admin can change the duration of the test before it begins in the code.

Test-taker can leave a question un-answered and come back to the question (if time permits) using the ‘Previous’ and ‘Next’ Buttons.

Code Details-

1. Login is authenticated against a MySQL database.

2. AJAX is used for the timer, allowing only the timer label to postback to the server each second.

3. Use of the Random class to generate 5 questions out of the 10 in the database.

4. Session Management using Cookies and Session Variables.

5. Use of javascript in the master-page to prevent the ‘Back’ navigation from a page (clearing the cache)  [Would love your suggestions on an alternative to this]

6. Once the 5 questions are chosen randomly, they are held in a DataTable. This is then held in a  Session Variable to hold the values during postbacks.

Get the project here

Get the database here

ShareThis

Today, I came across another way to connect to a MySQL database in .NET

This is done through MySQL Connector/Net (We are using v 5.1.6)

MySQL Connector provides a namespace ‘MySql.Data.MySqlClient’ which gives you objects such as-

MySqlConnection - To establish a connection

MySqlCommand- Specify the MySQL Command

MySqlDataAdapter- Form the link between the dataset and the database

In order to be able to use these objects, you must first download the Connector from the MySQL website here

Therafter, in order to use this in your VB.NET Project, goto Project Menu -> Add Reference -> Under the .NET tab, Select MySQL.Data

You will need an import statement too as shown in the snippet that follows.
The connection string in this case would change to-

Server=127.0.0.1;Uid=<username>;Pwd=<password>;Database=<database name>;

Read the Documentation here

One difference that is worthy of mention is the manner in which a parameterized query is constructed. It differs from the type we used earlier for ODBC. Here is a small snippet to illustrate the same-

Imports MySql.Data.MySqlClient

Dim cn As New MySqlConnection(" Server=127.0.0.1;Uid=root;Pwd=;Database=logindb;")
        Dim cmd As New MySqlCommand("select * from login where username=?user and password=?pass", cn)
        cmd.Parameters.Add("?username", MySqlDbType.VarChar, 80).Value = Me.TextBox1.Text
        cmd.Parameters.Add("?pass", MySqlDbType.VarChar, 80).Value = Me.TextBox2.Text
        Dim ds As New DataSet
        Dim adp As New MySqlDataAdapter(cmd)
        adp.Fill(ds, "login")
        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "login"

ShareThis

Today we will see how to create a simple login page for your ASP.NET website. Add a new WebForm to your Website. For readabilty, name this as login.aspx

Drag a Login control from the toolbox (Yes, ASP.NET comes with a built-in Login control)

This Login control is really smart and is able to do the validations itself (i.e. checking if username and password are not entered etc..)

What needs to be configured for this control is the manner in which the user authentication will occur.

We will authenticate a user using the data stored in a MySQL database in the ‘login’ table which holds the username and password (We created this table and so should you before trying this out).

Write the following code for the ‘Login1_Authenticate’ method. Double click on the Login control to goto the code window.

' Import the ODBC namespace for MySQL Connection
Imports System.Data.Odbc
Partial Class login
    Inherits System.Web.UI.Page

    Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate
        Dim cn As New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=mydb; User=root;Password=;")
        cn.Open()
        Dim cmd As New OdbcCommand("Select * from login where username=? and password=?", cn)

        'Add parameters to get the username and password

        cmd.Parameters.Add("@username", OdbcType.VarChar)
        cmd.Parameters("@username").Value = Me.Login1.UserName

        cmd.Parameters.Add("@password", OdbcType.VarChar)
        cmd.Parameters("@password").Value = Me.Login1.Password

        Dim dr As OdbcDataReader
        ' Initialise a reader to read the rows from the login table.
        ' If row exists, the login is successful

        dr = cmd.ExecuteReader

        If dr.HasRows Then
            e.Authenticated = True
            ' Event Authenticate is true
        End If

    End Sub
End Class

Now suppose that you have other webpages in your websites and you wish to grant access to these only if a user has authenticated himself. This essentially means that the login page should appear to any anonymous users trying to access the webpage. To do this, we need to change the web.config XML file associated with the website (It is a configuration file which is added by default to all ASP.NET websites). This file can be found from the Solution Explorer (Keyboard Shortcut - Ctrl+Alt+L).

Make the following changes to the authentication tag that already exists in the file to make it look like the following-

Add a authorization tag just after the authentication to make sure anonymous users are denied access to your webpages (Anonymous users are identified by the question mark)

Now when you try to request any page in your website, it shows the login.aspx to authenticate you before allowing you to view its contents.

ShareThis

Today we are going to introduce you to the concept of PostBack in ASP.NET.

PostBack is the name given to the process of submitting an ASP.NET page to the server for processing . PostBack is done if certain credentials of the page are to be checked against a database (such as verification of username and password). This is something that a client machine is not able to accomplish and thus these details have to be ‘posted back’ to the server.

A simple example to illustrate the usage of PostBack is a login page. After the user has typed his username and password, he clicks on the ‘Login’ button. Upon the click, the page is sent to the server to check against the database/XML file to check if the user with supplied details is an authenticated user.

Then there arise certain events ( Listbox Index Changed,RadioButton Checked etc..) in an ASP.NET page upon which a PostBack might be needed. Consider the case in which you have 2 ComboBoxes. Let us say the first ComboBox asks you for the Country you reside in and the second one asks you for the State/Province in that country. Based upon the Country you select, the list of States/Provinces must be shown. Thus, in order to fill the values in the second ComboBox, the selection in the first ComboBox must be known to the server. Thus, as and when an item is selected in the first ComboBox, a PostBack must be done and the appropriate list of items must be filled in the second ComboBox.

To handle these situations, you need to enable the ‘Auto PostBack’ property for those controls whose events are going to trigger some kind of server processing (the case of the first ComboBox in the previous example).

Usage of IsPostBack in ASP.NWT-

IsPostBack is a Boolean property of a page when is set (=true) when a page is first loaded. Thus, the first time that the page loads the IsPostBack flag is false and for subsequent PostBacks, it is true. An important point to be noted here is that each time a PostBack occurs, the entire page including the Page_Load is ‘posted back‘ and executed.

Thus a very common programming practice is that whenever some part of the code is to be executed just the first time a page loads, it is checked against the IsPostBack flag.

If you have not understood the concept clearly, do not worry. Programming examples in subsequent posts will help making this concept clear.

ShareThis

In practical applications it is not always convenient to retrieve data in DataGridView (as done in a previous example). Data can also be retrieved in a more elegant manner and bound to Controls like TextBoxes or Labels. For this purpose, we come across a new object type known as BindingSource.

Binding Source binds the data from a column in a table to a control.

For this example, we will be using the same table as we did for the DataGridView Example (my previous post)

Example:

1. Drag three Labels, three Textboxes and two buttons to a new Windows form.
2. Change the text property of the three Labels to Name, City and Date of Birth.
3. Change the text property of the buttons to Next and Previous.
4. Double click on the form and within the form_load method type the code as shown below.
5. Double click on the Next button and within the Click type the code-
   bs.MoveNext()
6. Double click on the previous button and within the Click event type the code-
   bs.MovePrevious()

Public Class Form3
    Dim cn As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=mydb; User=root;Password=;")
    Dim cmd As New Odbc.OdbcCommand
    Dim adp As Odbc.OdbcDataAdapter
    Dim ds As New DataSet
    Dim bs As BindingSource

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cn.Open()
        cmd.Connection = cn
        cmd.CommandText = "Select * from mytable"
        adp = New Odbc.OdbcDataAdapter(cmd)
        adp.Fill(ds, "mytable")
        'Binding Statements Follow
        bs = New BindingSource(ds, "mytable")
        'Bind the name field to first textbox, type of data as text
        Me.TextBox1.DataBindings.Add("text", bs, "name")
        Me.TextBox2.DataBindings.Add("text", bs, "city")
        Me.TextBox3.DataBindings.Add("text", bs, "dob")

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        bs.MoveNext()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        bs.MovePrevious()
    End Sub
End Class

In the above code:

1. The name,city and the dob (Date of Birth) columns of the table named ‘mytable’ (this is the same table I used in an earlier post) is bound to three textboxes namely textbox1,textbox2 and textbox3 respectively using statement- Me.TextBox1.DataBindings.Add(”text”, bs, “name”) and so on.
2. bs.MoveNext() binds the next record in the table to the controls (as long as a ‘next’ exists).
3. bs.MovePrevious() binds the previous record in the table to the controls (as long as a ‘previous’ exists).

ShareThis