• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Update and Retrieve Data

Update and Retrieve Data

Update and Retrieve Data

Rate Update and Retrieve Data

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Preparation:

Design a form using .NET environment and place two textbox controls on a form. 
Design and create a table using SQL Server 2000. 
 
Name the Database as FinAccounting.
Name the Table as AccountsTable.
Name the form as Form1
Name the controls on the form as Textbox1 and Textbox2.

Tasks:

1.	Establish the connection with the Database using Connection object.
2.	Execute the command.
3.	The data will be read by the Datareader object and the contents of the first record is displayed in the textboxes.

Imports System.Data.SqlClient
	Public Class Form1
		Inherits System.Windows.Forms.Form
		Dim str_sql_user_select As String = “SELECT * FROM AccountsTable”
		Dim str_connection As String = “Data Source=VSDOTNET;Integrated 													Security=SSPI;Initial Catalog=FinAccounting”
		Dim mycon As SqlConnection
		Dim comUserSelect As SqlCommand
		Dim myreader As SqlDataReader

Private Sub Form1_Load(ByVal sender As Object, ByVal e As system.EventArgs) Handles 	
		MyBase.Load
		mycon = New SqlConnection(str_connection)
		‘Instantiate the commands
		comUserSelect = New SqlCommand(str_sql_user_select, mycon)
		TextBox1.Text = “ “
		TextBox2.Text = “ “
			mycon.Open()
			myreader = comUserSelect.ExecuteReader
			If (myreader.Read = True) Then
				TextBox1.Text = myreader(0)
				TextBox2.Text = myreader(1)
			Else
				MsgBox(“You have reached eof”)
			End If
		End Sub
End Class



2.Purpose :To retrieve the data using Dataset and Data Adapter

Preparation

Design a form using .NET environment and  place  two textbox controls on a form. 
Design and create a table using SQL Server 2000. 
 
Name the Database as FinAccounting.
Name the Table as AccountsTable.
Name the form as Form1
Name the controls on the form as Textbox1 and Textbox2.


Tasks:

1.	Establish the connection with the database using Connection object.
2.	Instantiate the Command object.
3.	Instantiate Data Adapter
4.	Set Data Adapter command properties
5.	Instantiate DataSet
6.	Populate the DataSet using the DataAdapter and the data will be displayed in the textboxes.

Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection = New SqlConnection(“Data 							Source=VSDOTNET;Integrated Security=SSPI;Initial Catalog=FinAccounting”)
Dim str_sql_account_select As String = “SELECT * FROM AccountsTable”
Dim comAccountSelect As SqlCommand       ‘command for Account select
Dim myAccountAdapter As SqlDataAdapter
Dim myAccountDataset As DataSet

Private Sub frmAccounts_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles 					MyBase.Load
	Dim i, j As Integer
	Dim vbn As Boolean
	vbn = True
	myConnection.Open()
	
	‘Instantiate the commands
	comAccountSelect = New SqlCommand(str_sql_account_select, myConnection)
	
	‘Instantiate  data adapter
	myAccountAdapter = New SqlDataAdapter(str_sql_account_select, myConnection)
	‘Set data adapter command properties
	myAccountAdapter.SelectCommand = comAccountSelect

	‘Instantiate the datasets
	myAccountDataset = New DataSet()

	‘Populate the dataset
	myAccountAdapter.Fill(myAccountDataset, “AccountsTable”)
	j = myAccountDataset.Tables(“AccountsTable”).Rows.Count()
	For i = 0 To (j - 1)
		TextBox1.Text = myAccountDataset.Tables(“AccountsTable”).Rows(i)(0)
		TextBox2.Text = myAccountDataset.Tables(“AccountsTable”).Rows(i)(1)
	Next
	End Sub
	End Class

3.Purpose :To accept multiple rows of data using datagrid.

This code is part of an application which has one data entry form. The form contains three controls, one is combobox and two textbox controls and a datagrid. The user will enter the data in the three controls. When user press the acceptbutton, the data will be moved from controls to the datagrid after the validations. When the following code is executed, the new row is being added to the grid. 

Preparations:

Design a form using .NET environment and place two Text boxes, one Combobox and one DataGrid. Name the controls as given below:

Combobox:MulAccountsCombo
Textbox: cr , db 
Datagrid: DatGrid1

Place the Combobox and the two textboxes in a single line and place the Datagrid below this line.

Private Sub Aceptbutton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Aceptbutton.Click
	Dm crtot As Double
	Dim toggle As Object
	Dim i As Integer
	toggle = System.DBNull.Value
	If cr.Text > 0 Then
		db.Text = 0
	End If
	If ValidControls() Then
		If Not (appendmode(toggle)) Then
			i = DataGrid1.CurrentRowIndex
		Else
			i = 0
		End If
		If appendmode(toggle) Then
			MsgBox(“adding new row”)
			obj.drwUser = obj.dtbuser.NewRow()
			obj.drwUser(0) = MulAccountsCombo.Text
			obj.drwUser(1) = db.Text
			bj.drwUser(2) = cr.Text
  			Try
				obj.dtbuser.Rows.Add(obj.drwUser)
				obj.dtbuser.AcceptChanges()
				Catch
				 	Msgbox(“your account is duplicated”)
					MulAccountsCombo.Focus()
				Finally
					MsgBox(“Accounts data is proper”)
			End Try
		Else
			Try
				obj.dtbuser.Rows.Item(i)(0) = MulAccountsCombo.Text
				obj.dtbuser.Rows.Item(i)(1) = db.Text
				obj.dtbuser.Rows.Item(i)(2) = cr.Text
			Catch
				MsgBox(“your account is duplicated”)
				MulAccountsCombo.Focus()
			Finally
				MsgBox(“all well”)
			End Try
		End If
		Call appendmode(False)
		db.Text = 0
		cr.Text = 0
		Call enablemultiple(False)
		Call CheckSaveButton()
	End If
End Sub



4.Purpose:  To retrieve the data from the database using stored procedures and ADO.NET.

When we are working with large DataSets, it is advisable that we limit the data that we send back to the Client. So, it is better to apply the specific filters and searches on the back-end database server. We use ADO.NET to call the Stored procedure.

Preparations:

Create a StoredProcedure using speditor window.
Design a form using .NET environment.
Design and create a table using SQL Server 2000. 
 
Name the Database as FinAccounting.
Name the Table as AccountsTable.
Name the form as Form1
Controls on the form: Textbox1 and Textbox2.
Name of the Stored Procedure:AllAccounts.   

Tasks:
	
1.Establish the connection with the Database using Connection object.
2.Execute the command
3.The data will be read by the Data reader object and displays the contents of first record in the textboxes.

Place the Stored Procedure AllAccounts code……

ALTER PROCEDURE Allaccounts
/*


(		@parameter1 datatype = default value, 
		@parameter2 datatype OUTPUT
)

*/

AS

SELECT *  FROM AccountsTable 

Imports System.Data.SqlClient
Public Class Form1
		Inherits System.Windows.Forms.Form
Dim mycon As SqlConnection
Dim str_connection As String = “Data Source=VSDOTNET;Integrated 
Security=SSPI;Initial Catalog=FinAccounting”
Dim comUserSelect As SqlCommand
Dim myreader As SqlDataReader

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
		mycon = New SqlConnection(str_connection)
		‘Instantiate the commands
		comUserSelect = New SqlCommand(“Allaccounts”, mycon)
		comUserSelect.CommandType = CommandType.StoredProcedure
		TextBox1.Text = “ “
		TextBox2.Text = “ “
		mycon.Open()
		myreader = comUserSelect.ExecuteReader()
		If (myreader.Read = True) Then
			TextBox1.Text = myreader(0)
			TextBox2.Text = myreader(1)
		Else
			MsgBox(“You have reached eof”)
		End If
		End Sub
		End Class


5.Purpose: To navigate through the DataSet.

In ADO.NET, we use array-like navigation. We use For.. Each  Loop to iterate through the DataSet. If we want to iterate through the rows and columns within an existing DataTable named AccountsTable, stored in a ‘myAccountDataset’ DataSet, we use the following loop.This will print out the values in second column of the ‘MyAccount DataSet’ DataSet created in listing. 
	
Preparations:

Design and create a table using SQL Server 2000. 
 
Name the Database as FinAccounting.
Name the Table as AccountsTable.

Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim str_connection As String = “Data Source=VSDOTNET;Integrated 	
	Security=SSPI;Initial Catalog=FinAccounting”
Dim mycon As SqlConnection
Dim myadapter As SqlDataAdapter
Dim myaccountdataset As DataSet
Dim str_sql_user_select As String = “SELECT * FROM AccountsTable”
Dim mycommand As SqlCommand
    
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles 
	MyBase.Load
Dim i, j As Integer
mycon = New SqlConnection(str_connection)
mycon.Open()
myadapter = New SqlDataAdapter(str_sql_user_select, mycon)
myaccountdataset = New DataSet()
myadapter.Fill(myaccountdataset, “AccountsTable”)
j = myaccountdataset.Tables(“AccountsTable”).Rows.Count()
For i = 0 To (j - 1)
MsgBox(myaccountdataset.Tables(“AccountsTable”).Rows(i)(1))
Next
End Sub


6.	Purpose: Moving a particular row in the dataset using the Binding Context object.

Preparations:

Design a form using .NET environment.
Design and create a table using SQL Server 2000. 
 
Name the Table as AccountsTable.
Name the form as Accounts form
Controls on the form:
Text box controls : mcode,mname etc.
List box :AccountList

The Accounts form is a main form and displays the names of all Accounts in the Listbox control ‘AccountList’. If  we run the application now, the ListBox will be populated, but nothing will happen as we click on the items. We must add a few lines of code to set the current row in the DataSet every time the user selects an account in the list box control. 

The Binding Context object

To change the current location in a DataTable, use the BindingContext object. This object is a property of the form and keeps track of the current position in each DataTable of each DataSet.

There is a currency manager object for each DataTable, and the BindingContext object keeps track of all the currencymanager objects.

To specify the appropriate bindingcontext object, pass the name of the DataSet and the name of a table in the DataSet as the arguments.  The most important property of the BindingContext object is the position property, which is the current position in the table. The current position in the Accounts DataTable of the myAccountDataset dataset object is:

Me.BindingContext(myAccountDataset, “AccountsTable”).Position

To move to any row, set this property to any value between 0 (the first row) and Rows.Count() - 1.   
Since the order of the items on the list box control is the same as the order of rows in the DataSet, we can set the position property to the index of the selected item. This is done with a single statement:

Me.BindingContext(myAccountDataset, “AccountsTable”).Position = AccountList.SelectedIndex   

The listing below shows event handler of the listbox control’s AccountList_SelectedIndexChanged event handler.

Users will select Account in the listbox to view the details of particular account or edit the account.  This event handler is written to accomplish this particular task. This event handler display the details of selected account to the respective fields in the form. 


Private Sub AccountList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) 
Handles AccountList.SelectedIndexChanged

Me.BindingContext(myAccountDataset, “AccountsTable”).Position = AccountList.SelectedIndex
If (myAccountDataset.Tables(“AccountsTable”).Rows.Count() - 1) > 0 Then
mCode.Text= (myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(0))
mName.Text=(myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(1))
mDescription.Text=(myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(2))
mAddress1.Text=(myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(7))
mAddress2.Text=(myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(8))
mCity.Text= (myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(11))
mFax.Text= (myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(10))
mPhone.Text=(myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(9))
mAmount.Text=(myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(4))
mSalesTaxNo.Text=(myAccountDataset.Tables(“AccountsTable”).Rows(AccountList.SelectedIndex)(12))
End If









If you find these examples interesting, I will send you the code for the following. Email to [email protected].

A.	Linking a report to the menu and the code
B.	Steps in programming the save method
C.	How to call stored procedure with arguments from ADO.NET



Contact:

Ms. Bharathi Krishna
http://vkinfotek.com
Author:
1.	Database Prorgrammin using VB.Net and SQL Server 2000
	(Secrets of Developing an Accounting Package revealed)
2.	Develop an Inventory Management Application using Visual Basic.
3.	Develop an Accounting Package using Visual Basic.