Using ADO.NET Adding Editor and Deleting data in Web Forms inVB.NET

In this article I will explain Adding Editor, and Deleting data in Web Forms using ADO.NET.
  • 2764
In this example, I'll show you various database operations such as adding; updating and deletingdata form a database using ADO.NET. I've used a Microsoft Access 2000 database in this application to add, edit, and delete data; however, working with other data sources such as SQL server is similar to Microsoft Access. The only difference is creating data adapters objects.
 
Creating the Application
 
This application is web applicat6ion developed in Visual C# (see Figure 7-52). It displays the contents of the Employees table of the Northwind database. You can set the color, fonts, headers, and footers of a DataGrid by using its properties window at design-time as well as at run time.
 
Besides the Grid Control, the page has three buttons, three text boxes, and three labels. The Add button adds a record to the database, the Edit button updates a record, and the Delete button deletes a record from the database.
 
Figure-7.52.jpg
 
Figure 7-52. Adding, editing, and deleting in an ASP.NET application
 
Creating the Data Source
 
Similar to the previous samples, I'll use the same Northwind database in this application. Listing 7-17 shows the code for the FillDataGrid method. As you can see, you create a data adapter and dataset and them fill the dataset using the Fill method of the data adapter. After that you set the DataSource property of the data grid and call the DataGrid.DataBind method.
 
Listing 7-17. The FillDataGrid method to fill datagrid
 

   Private Sub FillDataGrid()
        
' Creating a connection

       Dim conn As New OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & 
" Data Source = C:/Northwind.mdb"
       Dim sql As String = "SELECT EmployeeID,FirstName,LastName,Title FROM Employees"
        conn.Open()


        
' Creating a data adapter
       Dim da As New OleDbDataAdapter(sql, conn)


        
' Creating a DataSet Object
       Dim ds As New DataSet()

        ' Fill DataSet with the data
        da.Fill(ds,"Employees")
        DataGrid1.DataSource = ds.Tables("Employees").DefaultView
        DataGrid1.DataBind()
        conn.Close()
    End 
Sub

Executing SQL Queries
 
You can use the OleDbCommand or SqlCommand object's Execute and ExecuteNonQuery methods to execute SQL queries. If you want to add, update, or delete data from a database, executing SQL queries is one of the easiest ways to do so. It's fast and requires a minimum of code to write.
 
You use the OleDbCommand object to execute a SQL command. The Execute and ExecuteNonQuery methods of OleDbCommand execute a SQL query. The OleDbCommand.Execute method executes the CommandText property and returns data in the OleDbDataReader object. As you can see from listing 7-18, the CreateMyOleDbComamnd method executes a SQL statement.
 
Listing 7-18. Calling the Execute method to execute a SQL statement
 

   Public Sub CreateMyOleDbComamnd(ByVal sqlQueryAs String,ByVal myCon As String)
       Dim cmd As New OleDbCommand(sqlQuery, AdoCon)
       Dim myCom As OleDbCommand = DirectCast(cmd.Clone(), oleDbcommand)
       Dim reader As OleDbDataAdapter
        myCom.ActiveConnection.open()
        myCom.Execute(reader)
    End 
Sub

Listing 7-19 shows how to construct OleDbCommand and set the OleDbCommand.Command text property for performing a SELECT from the database's Employees table.
 

Listing 7-19.The CommandText property of the command object

        Dim cmd As New OleDbCommand()
        cmd.CommandText = 
"SELECT * FROM Employees"

The OleDbCommand.ExecuteNonQuery method executes CommandText and doesn't return any data. The logical time to use this method is when you're writing to the database or executing SQL statements that don't return any data (See Listing 7-20).
 
Listing 7-20. Calling the ExecuteNonQuery method

   Public Sub CreateMyOleDbCommand(ByVal sqlQueryAs String,ByVal AdoCon As String)
       Dim cmd As New OleDbCommand(sqlQuery, AdoCon)
        cmd.ActiveConnection.Open()
        cmd.ExecuteNonQuery()
    End 
Sub

Note: Listing 7-21 shows the complete code for using the ExecuteNonQuery method to execute SQL statements.
 
In this example, I used the OleDbCommand.ExecuteNonQuery() method to execute the INSERT, UPDATE and DELETE SQL queries because I don't need to return any data. My ExecuteSQL method the execution of a SQL query (see Listing 7-21).
 
Listing 7-21. The ExecuteSQL method executes a SQL statement using the ExecuteNonQuery method
 

   Public Function ExecuteSQL(ByVal strSQLAs String)As Boolean
        ' Creating a connection
       Dim conn As New OleDbConnection()
        conn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & 
"Data Source =C:/Northwind.mdb"
       Dim myCmd As New OleDbCommand(strSQL, conn)

        
Try
            conn.Open()
            myCmd.ExecuteNonQuery()
       Catch exp As Exception

            Console.WriteLine("SQL Query Failed! {0}", exp.Message)
           Return 
False
        Finally


            
' clean up here
            conn.Close()
       End 
Try
       Return True
    End Function

Adding Data
 

The Add button adds a new record to the database and calls the FillDataGrid method, which rebinds the data source and fills the data grid control with the updated data. Because the ID column of the database table is auto Number, you don't have to enter it. You only need to enter the first name and last name.
 
Listing 7-22 shows the Add button click event handler. As you can see, you create an INSERT SQL statement, call ExecuteSQL, and refill the data using the FillDataGrid method.
 
Listing 7-22. Add button click event handler
 

        'Add button click event handler
   Private Sub Button1_Click(ByVal senderAs Object,ByVal e As System.EventArgs)

 
        
' Build a SQL statement
       Dim SQL As String = (("INSERT INTO Employee(FirstName,LastName)" & "VALUES ('") + TextBox2.Text.ToString() & "', '") + TextBox3.Text.ToString() & " ') "


        
'Execute SQL and refresh the data grid
        ExecuteSQL(Sql)
        FillDataGrid()
    End 
Sub

In figure 7-53, I add a new record with the first name as "Amy" and the last name as "Sue" 
 
Figure-7.53.jpg
 
Figure 7-53. Adding a new record in the Web application
 
EditingData 
 
The editing button updates a record corresponding to an ID. This is where you build an UPDATE, SET SQL statement and execute it by calling the ExecuteSQL method, as shown in Listing 7-23.
 
Listing 7-23 updating data on the edit button click
 

        ' edit Button Click event handler

   Private Sub Button2_Click(ByVal senderAs Object,ByVal e As System.EventArgs)
        
'Build a SQL statement
       Dim SQL As String = (("UPDATE Employees SET FirstName = '" & TextBox2.Text &"',LastName='") + TextBox3.Text &"' WHERE EmployeeID=") + TextBox1.Text

        'execute SQL and refresh the data grid
        ExecuteSQL(Sql)
        FillDataGrid()
   End 
Sub

Now to test the code, I type Mel in the First Name box, Tel in the Last Name box, and 10 in the ID box. Then I click the edit button. The result updates the row with ID = 10 and the output looks like Figure 7-54. As you can see, that record is updated as Mel Tel.
 

Figure-7.54.jpg
 
Figure 7-54. Editing records in a Web application
 
Deleting data
 
The delete button deletes a record corresponding with the ID from the database. I then build a DELETE SQL statement and execute it by calling the ExecuteSQL method, as shown in Listing 7-24.
 
Listing 7-24. Deleting data on the Delete button click          

'delete button click event handler

 

   Private Sub Button3_Click(ByVal senderAs Object,ByVal e As System.EventArgs)
        
' Build a SQL statement
       Dim SQL As String = ("DELETE * FROM Employees" & "WHERE EmployeeID= ") + TextBox1.Text


        
'execute SQL and refresh the data grid
        ExecuteSQL(Sql)
        FillDataGrid()
    End 
Sub

To avoid the newly added record, just enter 10 in the ID field or the ID of the record you want to delete and click the Delete button.
 

Conclusion
 
Hope this article would have helped you in understanding Adding Editor, and Deleting data in Web Forms using ADO.NET. See other articles on the website also for further reference

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.