Modify Data table: add a row and update a cell
Imports System
Imports System.Data
Imports System.Data.SqlClient
public class MainClass
Shared Sub Main()
Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
"integrated security=sspi;database=MyDatabase")
' Sql Query
Dim sql As String = "SELECT * FROM Employee"
Try
' Create Data Adapter
Dim da As New SqlDataAdapter
da.SelectCommand = New SqlCommand(sql, thisConnection)
' Create and fill Dataset
Dim ds As New DataSet
da.Fill(ds, "Employee")
' Get the Data Table
Dim dt As DataTable = ds.Tables("Employee")
' Display Rows Before Changed
Console.WriteLine("Before altering the dataset")
For Each row As DataRow In dt.Rows
Console.WriteLine("{0} | {1} | {2}", _
row("ID").ToString().PadRight(10), _
row("FirstName").ToString().PadRight(10), _
row("LastName"))
Next
' FirstName column should be nullable
dt.Columns("FirstName").AllowDBNull = True
' Modify city in first row
dt.Rows(0)("FirstName") = "Birm"
' Add A Row
Dim newRow As DataRow = dt.NewRow()
newRow("ID") = "99"
newRow("FirstName") = "Ever"
newRow("LastName") = "Dame"
dt.Rows.Add(newRow)
' Display Rows After Alteration
Console.WriteLine("=========")
Console.WriteLine("After altering the dataset")
For Each row As DataRow In dt.Rows
Console.WriteLine("{0} | {1} | {2}", _
row("ID").ToString().PadRight(10), _
row("FirstName").ToString().PadRight(10), _
row("LastName"))
Next
Catch ex As SqlException
' Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
' Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")
End Try
End Sub
End Class
Related examples in the same category
