ARTICLE

WPF Database communication Adding new record to the database

Posted by Munir Shaikh in Articles | Visual Basic 2010 on April 16, 2008

Tags: C#, Database Connectivity using XAML, WPF, XAML

WPF Database communication Adding new record to the database (XAML-MS-Access), I have a XAML form with FirstName, LastName, Email & Contact fields in it on click of submit button it will first check if the same email address is exists in the table else it will add new record I have made use of OleDbTransaction to perform add functionality.

Tweet
7272
0

Reader Level: Advanced

Introduction

In the first WPF tutorial I have spoken much about Data Binding.

http://www.vbdotnetheaven.com/UploadFile/munnamax/Databinding04112008003515AM/Databinding.aspx?ArticleID=de367efe-eeb6-4c73-adaf-9c9b740aba10

In the second tutorial I have spoken little about how we can file menu.

http://www.c-sharpcorner.com/UploadFile/munnamax/WPFMenuItem09032007085818
AM/WPFMenuItem.aspx?ArticleID=4307094e-45dc-4eae-8e2f-aebc0d10bc6d

Here I am going to show how you can create new records using XAML & MS-Access.

I am having database table as below in ms-access.

"emp" table

ID	Autonumber
FirstName	Text
LastName	Text
Email	Text
Contact	Text

I have taken file called as "Contact.xaml" code as below

Contact.xaml:

<Page x:Class="AddressBook.Contact" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

Title="Contact">

<Grid.ColumnDefinitions>

</Grid.ColumnDefinitions>

<Grid.RowDefinitions>

</Grid.RowDefinitions>

<TextBlock Width="200" Height="30"Grid.Column="0" Grid.Row="1">First Name

</TextBlock>

<TextBlock Grid.ColumnSpan="2" Margin="2,3,198,10" Grid.Row="2">Last Name

</TextBlock>

<TextBlock Grid.ColumnSpan="2" Margin="1,8,199,17" Grid.Row="3">Email

</TextBlock>

<TextBlock Grid.ColumnSpan="2" Margin="3,5,197,0" Grid.Row="4">Contact

</TextBlock>

<Button Click="AddNewContact" Margin="69.5,6,55.5,0" Grid.Row="5" Height="24" VerticalAlignment="Top">Add New

</Button>

<Button Grid.Row="5" Margin="13,9,0,31" Name="btnClear" Grid.Column="1" Click="ClearAll" HorizontalAlignment="Left" Width="75" >Clear All

</Button>

</Grid>

</Page>

On Submit button click I am calling backend event as "AddNewContact" so code behind is as follows:

"Contact.xaml.cs":

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports System.Windows

Imports System.Windows.Controls

Imports System.Windows.Data

Imports System.Windows.Documents

Imports System.Windows.Input

Imports System.Windows.Media

Imports System.Windows.Media.Imaging

Imports System.Windows.Navigation

Imports System.Windows.Shapes

Imports System.Data

Imports System.Data.OleDb

Namespace AddressBook

''' <summary>

''' Interaction logic for Contact.xaml

''' </summary>

Partial Public Class Contact

Inherits System.Windows.Controls.Page

Public Sub New()

InitializeComponent()

End Sub

Public Sub AddNewContact(ByVal sender As Object, ByVal e As RoutedEventArgs)

If txtFirstName.Text.Trim() = "" Then

txtFirstName.Focus()

MessageBox.Show("First name is required!", "Validation")

ElseIf txtLastName.Text.Trim() = "" Then

txtLastName.Focus()

MessageBox.Show("Last name is required!", "Validation")

ElseIf txtEmailAddress.Text.Trim() = "" Then

txtEmailAddress.Focus()

MessageBox.Show("Email address is required!", "Validation")

ElseIf txtContact.Text = "" Then

txtContact.Focus()

MessageBox.Show("Contact is required!", "Validation")

Else

' if similar entry already exists else add new entry

DimoleConAsNewOleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=E://AddressBook//AddressBook//EmployeeDb.mdb")

Dim strSql As String = "SELECT Email FROM emp WHERE Email='" + txtEmailAddress.Text + "'"

Dim oleComd As New OleDbCommand(strSql, oleCon)

Dim dtstIns As New DataSet()

Dim adpt As New OleDbDataAdapter()

Try

oleCon.Open()

adpt.SelectCommand = oleComd

adpt.Fill(dtstIns, "emp")

If dtstIns.Tables(0).Rows.Count > 0 Then

'MessageBox.Show("Similar Contact already available", "Notification");

MessageBox.Show(dtstIns.Tables(0).Rows(0)(0).ToString())

Else

'call to insert record

Dim inval As Integer = doInsert()

If inval > 0 Then

MessageBox.Show("Done", "Notification")

End If

Catch er As Exception

MessageBox.Show(er.Message, "Error")

Finally

oleCon.Close()

End Try

End If

End Sub

Public Sub ClearAll(ByVal sender As Object, ByVal e As RoutedEventArgs)

txtFirstName.Text = ""

txtLastName.Text = ""

txtEmailAddress.Text = ""

txtContact.Text = ""

End Sub

Public Function doInsert() As Integer

Dim retVal As Integer = 0

DimmyConnectionAsNewOleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=E://AddressBook//AddressBook//EmployeeDb.mdb")

myConnection.Open()

Dim myCommand As OleDbCommand = myConnection.CreateCommand()

Dim myTrans As OleDbTransaction

Dim retval As Integer = 0

' Start a local transaction

myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted)

' Assign transaction object for a pending local transaction

myCommand.Connection = myConnection

myCommand.Transaction = myTrans

Try

myCommand.CommandText = "INSERT INTO emp(FirstName,LastName,Email,Contact) VALUES('" + txtFirstName.Text + "','" + txtLastName.Text + "','" + txtEmailAddress.Text + "','" + txtContact.Text + "')"

retval = myCommand.ExecuteNonQuery()

myTrans.Commit()

Console.WriteLine("Records added to database.")

Catch e As Exception

MessageBox.Show(e.Message)

myTrans.Rollback()

Finally

myConnection.Close()

End Try

Return retval

End Function

End Class

End Namespace

Note: Please mention "E://AddressBook//AddressBook//EmployeeDb.mdb" datasource as per your machine else you will not able to insert record to the database.

NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# Corner (http://www.c-sharpcorner.com/).