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