by Bharathi Krishna (1 Submission)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sun 4th January 2004
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Saving Data, Retrieving data, Using Datasets, Data Adapters, Data Reader, Data Command Object, Using Stored Procedures, Using Triggers, Using
API Declarations
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
VBcoders
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.
No comments have been posted about Saving Data, Retrieving data, Using Datasets, Data Adapters, Data Reader, Data Command Object, Usin. Why not be the first to post a comment about Saving Data, Retrieving data, Using Datasets, Data Adapters, Data Reader, Data Command Object, Usin.