where to write which code

[kehar]

Hi, pl write me the simple example to connect student table of school database
of Access through VB.net and code to
AddNewRecord,DeleteRecord, UpdateRecord, SearchRecord.
the fields in the table are say- Name, Desig and Age
and textbox in form are- TxtName, TxtDesig, TxtAge

the event under which each code has to be written may also
be mentioned please.

Note: all connection either to dataset or data adapters
are to be done through code window.

Thanks

(K. S.)

[Naveed_786]

Please use this code to add new record and establish connection with access.

Imports System.Data.OleDb

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Or TextBox5.Text = "" Then
            MessageBox.Show("Please enter the required field", "Project Name", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        Dim cnString As String

        cnString = ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\DB.mdb;")


        Dim sqlQRY As String = "SELECT * FROM CustomerInformation"

        'Create connection
        Dim conn As OleDbConnection = New OleDbConnection(cnString)

        Try
            ' Open connection
            conn.Open()

            'create data adapter
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)

            'create command builder
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)

            'create dataset
            Dim ds As DataSet = New DataSet

            'fill dataset
            da.Fill(ds, "CustomerInformation")

            'get data table
            Dim dt As DataTable = ds.Tables("CustomerInformation")

            Dim newRow As DataRow = dt.NewRow()
            newRow("CustomerName") = TextBox1.Text
            newRow("FName") = TextBox2.Text
            newRow("IdCardNo") = TextBox3.Text
            newRow("Address") = TextBox4.Text
            dt.Rows.Add(newRow)

            'update customers table
            da.Update(ds, "CustomerInformation")

            MsgBox("Record successfully saved...", MsgBoxStyle.Information)
        Catch ex As OleDbException
            MsgBox("Error: " & ex.ToString & vbCrLf)
        Finally
            ' Close connection
            conn.Close()
        End Try
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
    End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim a As Integer
        a = MessageBox.Show("Are you sure you want to exit????", "Project Name", MessageBoxButtons.YesNo, MessageBoxIcon.Information)

        If a = vbYes Then
            Me.Close()
        Else
            Exit Sub
        End If
    End Sub

[kehar]

Thanks for the reply
but I could not understand what is try and end try.
Further should I open connection and dataset under every
click event of botton like save, update, search, or
it can be written once under form load event only ?
Pl suggest in detail

(k.S)

[lolafuertes]

First question:
Try means exactly that. Try the code following the Try sentence. If any error occurs then skip the conde until the catch sentence and execute this code.
You may find also a Finally clause that is always executed.
The End Try sentence closes all the try execution block.

See http://msdn.microsoft.com/en-en/library/fk6t46tz(VS.80).aspx

This is useful when some unexpected error can happens IE: The underlying database is not acessible anymore because a change of permissions or a disk error, both situations out of the control of the program.

Second question:
Opening/closing the connection each time is more secure than doing once, but is slower. You must decide between both options.

If the program should run in a multiuser environment, and several users can access the database at same time, then you must choose security. If the application is to be run for a single user alone, an you can assure that no other user or process will access the database, then you can chooese the speedy way.