views:

217

answers:

2

Hello I'm trying to update a row in datagrid but the problem is that i can't even change its cell values I had set my datagrid AllowUpdate property to true , but i can't still change any cell values

Option Explicit
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim recordset As New ADODB.recordset
Public Action As String
Public Person_Id As Integer
Public Selected_Person_Id As Integer
Public Phone_Type As String

Public Sub InitializeConnection()
Dim str As String

str = _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" + App.Path + "\phonebook.mdb;" & _
   "Persist Security Info=False"

    conn.CursorLocation = adUseClient

    If conn.state = 0 Then
    conn.ConnectionString = str



    conn.Open (conn.ConnectionString)
    End If

End Sub
Public Sub AbandonConnection()
    If conn.state <> 0 Then
        conn.Close
    End If
End Sub
Public Sub Persons_Read()

    Dim qry_all As String

   ' qry_all = "select * from person,web,phone Where web.personid = person.id And phone.personid = person.id"
    qry_all = "SELECT * FROM person order by id"

    Call InitializeConnection

    cmd.CommandText = qry_all
    cmd.CommandType = adCmdText

    Set cmd.ActiveConnection = conn


    If conn.state = 1 Then

       Set recordset = cmd.Execute()

    End If

        BindDatagrid


End Sub

Private Function Person_Delete(id As Integer)
   Dim qry_all As String
    qry_all = "Delete * from person where person.id= " & id & " "

    Call InitializeConnection

    cmd.CommandText = qry_all
    cmd.CommandType = adCmdText

    Set cmd.ActiveConnection = conn


    If conn.state = 1 Then

       Set recordset = cmd.Execute()

    End If

        dg_Persons.Refresh



End Function
Private Function Person_Update()

End Function
Public Sub BindDatagrid()



   Set Me.dg_Persons.DataSource = recordset


    Me.dg_Persons.Refresh
    dg_Persons.Columns(0).Visible = False
    dg_Persons.Columns(4).Visible = False

    dg_Persons.Columns(1).Caption = "Name"


    dg_Persons.Columns(2).Caption = "Family"
    dg_Persons.Columns(3).Caption = "Nickname"
    dg_Persons.Columns(5).Caption = "Title"
    dg_Persons.Columns(6).Caption = "Job"

End Sub
Public Function DatagridReferesh()

    Call Me.Persons_Read

End Function

Private Sub cmd_Add_Click()
    frm_Person_Add.Caption = "Add a new person"
    frm_Person_Add.Show



End Sub

Private Sub cmd_Business_Click()


  '  frm_Phone.Caption = "Business Phones"
    frm_Phone.Phone_Type = "Business"
    frm_Phone.Person_Id = Selected_Person_Id

    frm_Phone.Tag = Selected_Person_Id

    frm_Phone.Show

End Sub

Private Sub cmd_Delete_Click()
  Dim msg_input As Integer

  msg_input = MsgBox("Are you sure you want to delete this person ?", vbYesNo)

    If msg_input = vbYes Then
        Person_Delete Selected_Person_Id
        MsgBox ("The person is deleted")
        frm_Phone.DatagridReferesh

    End If

End Sub

Private Sub cmd_Home_Click()

    'frm_Phone.Caption = "Home Phones"
    frm_Phone.Phone_Type = "Home"
    frm_Phone.Person_Id = Selected_Person_Id
    frm_Phone.Tag = Selected_Person_Id

    frm_Phone.Show

End Sub

Private Sub cmd_Update_Click()
   If Not Selected_Person_Id = 0 Then
     frm_Person_Edit.Person_Id = Selected_Person_Id
     frm_Person_Edit.Show
    Else
    MsgBox "No person is selected"

   End If


End Sub


Public Function AddParam(name As String, param As Variant, paramType As DataTypeEnum) As ADODB.Parameter
    If param = "" Or param = Null Then
        param = " "
    End If
    Dim objParam As New ADODB.Parameter
    Set objParam = cmd.CreateParameter(name, paramType, adParamInput, Len(param), param)
    objParam.Value = Trim(param)
    Set AddParam = objParam

End Function


Private Sub Command1_Click()
    DatagridReferesh
End Sub

Private Sub Command2_Click()
    frm_Internet.Person_Id = Selected_Person_Id
    frm_Internet.Show

End Sub

Private Sub dg_Persons_BeforeColEdit(ByVal ColIndex As Integer, ByVal KeyAscii As Integer, Cancel As Integer)


   ' MsgBox ColIndex

'    dg_Persons.Columns(ColIndex).Text = "S"
 '   dg_Persons.Columns(ColIndex).Locked = False
'    dg_Persons.Columns(ColIndex).Text = ""
 'dg_Persons.Columns(ColIndex).Value = ""

       'Person_Edit dg_Persons.Columns(0).Value, dg_Persons.Columns(1).Value, dg_Persons.Columns(2).Value,dg_Persons.Columns(3).Value,dg_Persons.Columns(4).Value, dg_Persons.Columns(5).Value

End Sub

Private Sub dg_Persons_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
    MsgBox ColIndex
End Sub



Private Sub dg_Persons_Click()


If dg_Persons.Row <> -1 Then
    dg_Persons.SelBookmarks.Add Me.dg_Persons.RowBookmark(dg_Persons.Row)

    Selected_Person_Id = Val(dg_Persons.Columns(0).Value)
End If

End Sub


Private Sub Form_Load()

   ' dg_Persons.AllowUpdate = True
  '  dg_Persons.EditActive = True
   Call Persons_Read

   dg_Persons.AllowAddNew = True
 dg_Persons.Columns(2).Locked = False

End Sub
Private Function Person_Edit(id As Integer, name As String, family As String, nickname As String, title As String, job As String)


 InitializeConnection




cmd.CommandText = "Update  person set name=@name , family=@family , nickname=@nickname , title =@title , job=@job where id= " & id & ""
    cmd.Parameters.Append AddParam("name", name, adVarChar)
    cmd.Parameters.Append AddParam("family", family, adVarChar)
    cmd.Parameters.Append AddParam("nickname", nickname, adVarChar)
    cmd.Parameters.Append AddParam("title", title, adVarChar)
    cmd.Parameters.Append AddParam("job", job, adVarChar)


    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText


    cmd.Execute

End Function
Private Function Person_Search(q As String)
     Dim qry_all As String


    qry_all = "SELECT * FROM person where person.name like '%" & q & "%' or person.family like '%" & q & "%' or person.nickname like '%" & q & "%'"


    Call InitializeConnection

    cmd.CommandText = qry_all
    cmd.CommandType = adCmdText

    Set cmd.ActiveConnection = conn


    If conn.state = 1 Then

       Set recordset = cmd.Execute()

    End If

        BindDatagrid

End Function

Private Sub mnu_About_Click()
    frm_About.Show
End Sub

Private Sub submnu_exit_Click()
    End
End Sub


Private Sub txt_Search_Change()
    Person_Search txt_Search.Text
End Sub

Thanks in advance

+2  A: 

I think you can accomplish this by adding an ADODC control as your source for the Grid control. Here is a great example of this:

http://www.vbtutor.net/Lesson26.html

Jamie
A: 

You can also use the floating text box control trick where you place the a text box on top of the desired cell on a grid in order to allow a user to edit the data in a grid.

http://www.thescarms.com/vbasic/editgrid.aspx

dretzlaff17