Member Avatar for jallan2009

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

  • 4 Contributors
  • 5 Replies
  • 218 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by pritesh2010
Member Avatar for c0deFr3aK

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:

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

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

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

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

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

So i guess you have now an idea of what will you add to this codes
I hope it helps

Member Avatar for pritesh2010

can any one help me for inserting a record in access table me code gives an error of overflw

dim vbstr as string
vbstr = "insert into EmpMaster Values ('" & txteid.Text & "','" & UCase(txtname.Text) & " " _
                    & "','" & UCase(txtadd.Text) & "','" & txttel.Text & "','" & txtmob.Text & " " _
                    & "','" & txtemail.Text & "','" & cmbrht.SelectedItem & " " _
                    & "','" & txtpst.Text & "','" & CDate(txtdate.Text.ToString) & "','" & txtsal.Text & " " _
                    & "','" & txtcom.Text & "','" & txtpwd.Text & "')"
                  str="Connection string"
  ocn = New OleDbConnection(str)
        ocn.Open()
        conter = False
        Dim i As Integer
        Dim trans As OleDbTransaction
        trans = ocn.BeginTransaction()
        Try
            cmd = New OleDbCommand(vbstr, ocn, trans)
            i = cmd.ExecuteNonQuery()
            trans.Commit()
            conter = True
        Catch ex As Exception
            trans.Rollback()
            conter = False
        End Try

please help me it gives overflow error
while saving the content

Member Avatar for pritesh2010

can any one help me for inserting a record in access table me code gives an error of overflw

dim vbstr as string
vbstr = "insert into EmpMaster Values ('" & txteid.Text & "','" & UCase(txtname.Text) & " " _
                    & "','" & UCase(txtadd.Text) & "','" & txttel.Text & "','" & txtmob.Text & " " _
                    & "','" & txtemail.Text & "','" & cmbrht.SelectedItem & " " _
                    & "','" & txtpst.Text & "','" & CDate(txtdate.Text.ToString) & "','" & txtsal.Text & " " _
                    & "','" & txtcom.Text & "','" & txtpwd.Text & "')"
                  str="Connection string"
  ocn = New OleDbConnection(str)
        ocn.Open()
        conter = False
        Dim i As Integer
        Dim trans As OleDbTransaction
        trans = ocn.BeginTransaction()
        Try
            cmd = New OleDbCommand(vbstr, ocn, trans)
            i = cmd.ExecuteNonQuery()
            trans.Commit()
            conter = True
        Catch ex As Exception
            trans.Rollback()
            conter = False
        End Try

please help me it gives overflow error
while saving the content

Member Avatar for pritesh2010

send me eamil on [email snipped] please
gelp me

Edited by Ezzaral because: Snipped email. Please keep it on site.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.