Dear All,
Please give me sample code which should for add edit delete data from db using flex grid.
Thanks in advance.
I'm really in good mood right now. i write a simple code about how to add data to flexgrid and save it, also load it to flexgrid.
For edit and delete? do it by yourself. since 265 posts you should know about daniweb rules :)
Add references : Microsoft ActiveX Data Object 2.5 Library
Add 2 flexgrid : FlxGrdDemo for input data and FlxGrdDemo2 for load data.
Add 2 button for save and load.
After from load you can see default data in database. Just add new Au_Id and Author in first flexgrid. Use Keyboard Arrow to move and Enter to make new row.
Public Conn As New ADODB.Connection
Private Sub Access_Connector()
Dim db_file As String
' Get the data.'
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "Authors.mdb"
Set Conn = New ADODB.Connection
Conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
Conn.Open
End Sub
Private Sub LoadData()
Dim c As Integer
Dim r As Integer
Dim col_wid() As Single
Dim field_wid As Single
Access_Connector ' Connect to access'
Set rs = New ADODB.Recordset
rs.Open "SELECT * from Authors", Conn, adOpenDynamic, adLockBatchOptimistic
' Use one fixed row and no fixed columns.'
FlxGrdDemo2.Rows = 2
FlxGrdDemo2.FixedRows = 1
FlxGrdDemo2.FixedCols = 0
' Display column headers.'
FlxGrdDemo2.Rows = 1
FlxGrdDemo2.Cols = rs.Fields.Count
ReDim col_wid(0 To rs.Fields.Count - 1)
For c = 0 To rs.Fields.Count - 1
FlxGrdDemo2.TextMatrix(0, c) = rs.Fields(c).Name
col_wid(c) = TextWidth(rs.Fields(c).Name)
Next c
' Display the values for each row.'
r = 1
Do While Not rs.EOF
FlxGrdDemo2.Rows = FlxGrdDemo2.Rows + 1
For c = 0 To rs.Fields.Count - 1
FlxGrdDemo2.TextMatrix(r, c) = rs.Fields(c).Value
' See how big the value is.'
field_wid = TextWidth(rs.Fields(c).Value)
If col_wid(c) < field_wid Then col_wid(c) = field_wid
Next c
rs.MoveNext
r = r + 1
Loop
' Close the recordset and connection.'
rs.Close
Conn.Close
' Set the column widths.'
For c = 0 To FlxGrdDemo2.Cols - 1
FlxGrdDemo2.ColWidth(c) = col_wid(c) + 450
Next c
End Sub
Private Sub Command1_Click()
Access_Connector ' Connect to access'
With FlxGrdDemo
For i = 1 To .Rows - 1
Set rs = Nothing
Set rs = New ADODB.Recordset
rs.Open "SELECT * from Authors", Conn, adOpenStatic, adLockOptimistic
rs.AddNew
rs!Au_id = .TextMatrix(i, 0)
rs!Author = .TextMatrix(i, 1)
rs.Update
Next i
MsgBox "Data added", vbInformation, "Add Data"
rs.Close
Conn.Close
End With
End Sub
Private Sub Command2_Click()
Access_Connector
LoadData
End Sub
Private Sub FlxGrdDemo_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case vbKeyReturn
' When the user hits the return key'
' this code will move the next cell or row.'
With FlxGrdDemo
If .Col + 1 <= .Cols - 1 Then
.Col = .Col + 1
ElseIf .Row + 1 <= .Rows - 1 Then
.Row = .Row + 1
.Col = 0
Else
FlxGrdDemo.AddItem "" 'add new row'
.Row = .Rows - 1 'set position in new row
.Col = 0 'set position in new row
End If
End With
Case vbKeyBack
' Delete the previous character when the'
' backspace key is used.'
With FlxGrdDemo
If Trim(.Text) <> "" Then _
.Text = Mid(.Text, 1, Len(.Text) - 1)
End With
Case Is < 32
' Avoid unprintable characters.'
Case Else 'Else print everything'
With FlxGrdDemo
.Text = .Text & Chr(KeyAscii)
End With
End Select
End Sub
Private Sub FlxGrdDemo_KeyUp(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
' Copy'
Case vbKeyC And Shift = 2 ' Control + C'
Clipboard.Clear
Clipboard.SetText FlxGrdDemo.Text
KeyCode = 0
' Paste'
Case vbKeyV And Shift = 2 'Control + V'
FlxGrdDemo.Text = Clipboard.GetText
KeyCode = 0
' Cut'
Case vbKeyX And Shift = 2 'Control + X'
Clipboard.Clear
Clipboard.SetText FlxGrdDemo.Text
FlxGrdDemo.Text = ""
KeyCode = 0
' Delete'
Case vbKeyDelete
FlxGrdDemo.Text = ""
End Select
End Sub
Private Sub Form_Load()
With FlxGrdDemo
.Cols = 2
.Rows = 2
.FixedCols = 0
.TextMatrix(0, 0) = "Au_Id"
.TextMatrix(0, 1) = "Author"
End With
End Sub
In vb we put comments in codes using single quote. But something wrong if i don't close it with single quote too.
'This is a comments
Dim i as Integer ' declare i as integer type
Dim j as String
For i = 0 to 5 ' This comments too
Msgbox i
Next i
In vb we put comments in codes using single quote. But something wrong if i don't close it with single quote too.
'This is a comments
Dim i as Integer ' declare i as integer type
Dim j as String
For i = 0 to 5 ' This comments too
Msgbox i
Next i
Please just ignoring the last statement. It just for a test.
You have too much time on your hands Jx_Man
First of all thanks Jx_Man fantastic code example
Now let me ask some questions
i want to write column name as Employee ID insetad Emp_id
Please view the attached image file thanks.
- I want to check if record already exists in db before inserting new record?
- Can i have column name with out under score e.g
Just do small modification in saving codes. Binding all data in flexgrid and compare it with data in db.
Private Sub Command1_Click()
Access_Connector ' Connect to access'
With FlxGrdDemo
For i = 1 To .Rows - 1
Set rs = Nothing
Set rscheck = Nothing
Set rs = New ADODB.Recordset
Set rscheck = New ADODB.Recordset
rs.Open "SELECT * from Authors", Conn, adOpenStatic, adLockOptimistic
rscheck.Open "SELECT * from Authors where Au_id = " & FlxGrdDemo.TextMatrix(i, 0), Conn, adOpenStatic, adLockOptimistic
If rscheck.RecordCount > 0 Then
MsgBox "AU_ID '" & .TextMatrix(i, 0) & "' already in database"
Else
rs.AddNew
rs!au_id = .TextMatrix(i, 0)
rs!Author = .TextMatrix(i, 1)
rs.Update
MsgBox "Data added", vbInformation, "Add Data"
End If
Next i
rs.Close
rsCheck.Close
Conn.Close
End With
End Sub
I think there are codes about Autowidth of column size in last of LoadData procedure. Read the code! Learning it! don't just copy and paste without understanding it!!.
Do small modification (again!!) in your form load event :
Private Sub Form_Load()
With FlxGrdDemo
.Cols = 2
.Rows = 2
.FixedCols = 0
.TextMatrix(0, 0) = "Au_Id"
.TextMatrix(0, 1) = "Author"
'This following codes for autowidth of column size!!!'
For i = 0 To .Cols - 1
.ColWidth(i) = TextWidth(.TextMatrix(0, i)) + 300
Next i
End With
LoadData
End Sub
Sorry i was ill, some quries as still pending.
How to cahnge column name in MSFlexgrid?
How to delete a Selected Row?
Suppose i have a text box and i enter then click on search button to show Record in MSFlexGrid?
How to udpade a selected record?
How to clear the Contents of Flex Grid, i mean when i click on save button it should clear the Grid.
Sorry i was ill, some quries as still pending.
1. How to cahnge column name in MSFlexgrid? 2. How to delete a Selected Row? 3. Suppose i have a text box and i enter then click on search button to show Record in MSFlexGrid? 5. How to udpade a selected record? 6. How to clear the Contents of Flex Grid, i mean when i click on save button it should clear the Grid.
I think, i give you more than enough..
How about make some effort for your each questions and post your own code.
Until now, i didn't see any single codes from you..
You always ask more and more questions without showing some effort. You just drive me to writing all the codes for you.
to clear data from flexgrid
flexgrid.clear
note-- flexgrid is the name of your msflexgrid..you can change it into any name you prefer in the properties
hope this help.. :P
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.