VB.NET

Problem while Inserting and Updating data into MS access

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: Mar 2009

Posts: 2

Reputation: jallan2009 is an unknown quantity at this point

Solved Threads: 0

jallan2009 jallan2009 is offline

Offline

Newbie Poster

Problem while Inserting and Updating data into MS access

Mar 16th, 2009

Sir,
I am using front end as a VB.net 2005 and I am a beginner in this language. I am creating Desktop application and using controls TAB controls 1st tab control is Contact in contact I am using one text box, two button and one datagrid while I am using the oledb database i am inserting the data through text box and correspondingly that data will display on datagrid and there is my following coding to add contact.

In the contactdb file i have created contact table and the fields are ContactID and ContactName

Private Sub AddContact()
Try
Dim myDataSet As DataSet = New DataSet()
Dim cmd As OleDbCommand
Dim con As OleDbConnection
Dim str As String
'Dim da As New OleDbDataAdapter

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Contactdb.mdb"

con = New OleDbConnection(connectionString)
cmd = New OleDbCommand("insert into Contact(ContactName) values(ContactName)", con)
With cmd.Parameters
.Add("@ContactName", OleDbType.VarChar).Value = connametxtbox.Text
End With
con.Open()
cmd.ExecuteNonQuery()
con.Close()
' Close and Clean up objects

DataGridView1.DataSource = myDataSet.Tables("Contact").DefaultView
con.Dispose()
cmd.Dispose()

Catch ex As OleDbException
MsgBox(ex.Message.ToString)
insertOk = False
End Try
If insertOk Then
MsgBox("Contact Submitted Successfully!")
Else : MsgBox("There was an error saving your data!")
End If
End Sub

1) The problem is that while inserting data through text box it will insert but it will not display on my MS access file i.e. Contactdb what is the reason behind that please guide me for that please.

2) That the data in datagrid i.e. contact name by selecting the contact name it will display in the text box by datagrid cell click event by using this i want to update the contact name only. How I can update the Contact Name please guide me.

where is my mistake is going on.

Thanks in advance

•

Join Date: May 2008

Posts: 51

Reputation: martonx is an unknown quantity at this point

Solved Threads: 8

martonx

Offline

Junior Poster in Training

Re: Problem while Inserting and Updating data into MS access

Mar 16th, 2009

You are using MS access database, so why are you writing ugly code? The framework can do all your job.
There are tons of video tutorials, for example here:

http://msdn.microsoft.com/en-us/vstudio/aa700732.aspx

•

Join Date: Mar 2009

Posts: 66

Reputation: c0deFr3aK is an unknown quantity at this point

Solved Threads: 10

c0deFr3aK

Offline

Junior Poster in Training

Re: Problem while Inserting and Updating data into MS access

Mar 17th, 2009

I will show you another arrangement of coding which is pretty simple to read and understand

First Create a Module where you store your Connection to your Database

Module:

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Option Explicit On
Imports System
Imports System.Data
Imports System.Data.OLEDB
 
Module DBCONN 'DBCONN is the name of your module
  Public conn As New System.Data.OLEDB.Connection 'call this connection anywhere anytime in your application
  Sub DBLOAD() 
    Dim connstring as String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\DATA\MyDatabase.MDB; Persist Security Info = False;"
 
     If conn.State = ConnectionState.Open Then conn.Close
With conn
  Try
      .ConnectionString = connString
      .Open
Catch ex As Exception
     Messagebox.Show(ex.Message,"Error Connection")
End Try
  End Sub
 
End Module
Option Explicit On
Imports System
Imports System.Data
Imports System.Data.OLEDB

Module DBCONN 'DBCONN is the name of your module
  Public conn As New System.Data.OLEDB.Connection 'call this connection anywhere anytime in your application
  Sub DBLOAD() 
    Dim connstring as String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\DATA\MyDatabase.MDB; Persist Security Info = False;"

     If conn.State = ConnectionState.Open Then conn.Close
With conn
  Try
      .ConnectionString = connString
      .Open
Catch ex As Exception
     Messagebox.Show(ex.Message,"Error Connection")
End Try
  End Sub

End Module

Note: change the path of the Data Source to your own
So we will no insert some data into the database

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click 
Dim sql As String ="SELECT * FROM ContactDB WHERE ContactName = '" & Me.TextboxName.Text &"'" 'assuming that a Primary key is Autonumber
Dim da As New OLEDB.OledbAdapter(sql, conn) 'conn is called from the module
Dim ds As New DataSet
Dim cb As New OLEDB.OledbCommandBuilder(da)
Dim dt As DataTable, row As DataRow
 
da.Fill(ds)
dt = ds.Tables(0)
row = dt.NewRow
 
row("CondtactName") = Me.TextboxName.Text
'put your other table fields here 
 
'we will check if the Contact Name exists or not in your DB
If ds.Tables(0).Rows.Count = 0 Then
       dt.Rows.Add(row)
       da.Update(ds)
       MsgBox ("Insert Successful")
      ContactShow() ' we will show our newly inserted data into our datagrid
     Exit Sub
Else
      MsgBox (""& Me.TextboxName.Text &"" & " Already in your Database")
End If
 
End Sub
Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click 
Dim sql As String ="SELECT * FROM ContactDB WHERE ContactName = '" & Me.TextboxName.Text &"'" 'assuming that a Primary key is Autonumber
Dim da As New OLEDB.OledbAdapter(sql, conn) 'conn is called from the module
Dim ds As New DataSet
Dim cb As New OLEDB.OledbCommandBuilder(da)
Dim dt As DataTable, row As DataRow

da.Fill(ds)
dt = ds.Tables(0)
row = dt.NewRow

row("CondtactName") = Me.TextboxName.Text
'put your other table fields here 

'we will check if the Contact Name exists or not in your DB
If ds.Tables(0).Rows.Count = 0 Then
       dt.Rows.Add(row)
       da.Update(ds)
       MsgBox ("Insert Successful")
      ContactShow() ' we will show our newly inserted data into our datagrid
     Exit Sub
Else
      MsgBox (""& Me.TextboxName.Text &"" & " Already in your Database")
End If

End Sub

So we will Display our inserted data in the Datagrid

We will make a custom datagrid so that it will look professional
Assuming that the Name of our Datagrid is ContactDG

We will make a sub

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Private sub ContactShow()
Me.ContactDG.TableStyle.Clear
Dim sql As String ="SELECT * FROM ContactDB ORDER BY ContactName"
Dim da As New OLEDB.OledbAdapter(sql, conn)
Dim ds As New DataSet
Dim dt As DataTable
 
da.Fill(ds)
dt = ds.Tables(0)
 
'We will customize our Datagrid Tablestyle
Dim ts As New DatagridTableStle
ts.MappingName = dt.TableName
ts.RowHeaderVisible = False 'we will not display datagrid row header
'you can customize gridline color etc.
 
Dim col0 As New DataGridTextboxColumn 
col0.Alignment = HorizontalAlignment.Left
col0.HeadeText = "Contact Name"
col0.MappingName ="ContactName" 'this is our database field
col0.Width = 250
ts.GridColumnStyle.Add(col0)
 
With ContactDG
        .TableStyles.Add(ts)
        .DataSource = dt
        .CaptionVissible = False
        .ReadOnly = True
End With
End Sub
Private sub ContactShow()
Me.ContactDG.TableStyle.Clear
Dim sql As String ="SELECT * FROM ContactDB ORDER BY ContactName"
Dim da As New OLEDB.OledbAdapter(sql, conn)
Dim ds As New DataSet
Dim dt As DataTable

da.Fill(ds)
dt = ds.Tables(0)

'We will customize our Datagrid Tablestyle
Dim ts As New DatagridTableStle
ts.MappingName = dt.TableName
ts.RowHeaderVisible = False 'we will not display datagrid row header
'you can customize gridline color etc.

Dim col0 As New DataGridTextboxColumn 
col0.Alignment = HorizontalAlignment.Left
col0.HeadeText = "Contact Name"
col0.MappingName ="ContactName" 'this is our database field
col0.Width = 250
ts.GridColumnStyle.Add(col0)

With ContactDG
        .TableStyles.Add(ts)
        .DataSource = dt
        .CaptionVissible = False
        .ReadOnly = True
End With
End Sub

On mouseclick event as you said you want to display back your data in the textbox for editing.

Here is a Simple code
This also Select the entire row of your Datagrid as you click your mouse

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Private Sub ContactDG_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ContactDG.MouseUp
 
Dim pt As New Point(e.X,e.Y)
Dim hit As DataGrid.HitTestInfo = Me.ContactDG.HitTest(pt)
If hit.Type = DataGrid.HitTestType.Cell Then
            Me.ContactDG.CurrentCell = New DataGridCell(hit.Row, hit.Column)
            Me.ContactDG.Select(hit.Row)
            'we will throw back Contact Name to the TextboxName
            Me.TextboxName.Text = Me.ContactDG.Items(ContactDG.CurrentRowIndex, 0)
End If
 
End Sub
Private Sub ContactDG_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ContactDG.MouseUp

Dim pt As New Point(e.X,e.Y)
Dim hit As DataGrid.HitTestInfo = Me.ContactDG.HitTest(pt)
If hit.Type = DataGrid.HitTestType.Cell Then
            Me.ContactDG.CurrentCell = New DataGridCell(hit.Row, hit.Column)
            Me.ContactDG.Select(hit.Row)
            'we will throw back Contact Name to the TextboxName
            Me.TextboxName.Text = Me.ContactDG.Items(ContactDG.CurrentRowIndex, 0)
End If

End Sub

Now we will Update our TextboxName assumin there were error in the Spelling

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click 
 
Dim sql As String ="SELECT * FROM ContactDB WHERE ContactName = '" & Me.TextboxName.Text & "'"
Dim da As New OLEDB.OledbAdapter(sql, conn)
Dim ds As New DataSet
Dim cb As New OLEDB.OledbCommandBuilder(da)
da.Fill(ds)
ds.Tables(0).Rows(0).Item("ContactName") = Me.TextboxName.Text
da.Update(ds)
MsgBox ("Update Successful")
 
End Sub
Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click 

Dim sql As String ="SELECT * FROM ContactDB WHERE ContactName = '" & Me.TextboxName.Text & "'"
Dim da As New OLEDB.OledbAdapter(sql, conn)
Dim ds As New DataSet
Dim cb As New OLEDB.OledbCommandBuilder(da)
da.Fill(ds)
ds.Tables(0).Rows(0).Item("ContactName") = Me.TextboxName.Text
da.Update(ds)
MsgBox ("Update Successful")

End Sub