to save the data in flexgrid

[ishikha]

hi,


i am having one problem.i want to save the data in the database which i enter in the flexgrid.i am using ado

i want that as soon as the flexgrid_leavecell event is invoked it saves the data in the database

the coding of flexgrid_leavecell which i am using is::


Private Sub flxgrid_LeaveCell()
If Not IsNull(txtedit.Text) Then
'user typed an entry in the text box
flxgrid.TextMatrix(Active_FlxCell.row, Active_FlxCell.col) = txtedit.Text
End If
End Sub


regards
ishikha

[honeybee]

Why would you want to do it in the LeaveCell event? A better approach could be to use a Save button outside the grid, which saves the entire grid's contents into the database.

If you still want to do it in the LeaveCell() event, you already have the value to be saved using the TextMatrix method. Simply assign/insert it into the appropriate row in the appropriate column.

Assuming each row of the grid corresponds to each row of the recordset, and each column of the grid corresponds to each column of the recordset, and all data are text type, you can write something like this:

rs.Move grd.Row
rs.Fields(grd.Col) = grd.TextMatrix(grd.Row, grd.Col)
rs.Update

If the data is not text, you can add appropriate formatting in the assignment statement above. Also you can add validation for the correct row value, so if your grid accepts new rows entered by the user, you can use AddNew.

There could be many variations here, depending upon what you like.

.

[mendhak]

You could also do it in the textbox validate event (which is fired when you leave the textbox)

I've noticed this is a cross post.

would you like help, or have you already gotten help?

[ishikha]

hi,

it's not neccesary to save the data after the leavecell event .
can u tell me what code i have to use in order to save all the changes in the database by just clicking on save button.

also one thing more when i add the new row and enter the data then how i will save it.

i am also adding the new row in between the flexgrid.in that case i want that it saves that row in between the records in which i enter and not in the end.

ishikha

[honeybee]

First you need to use a primary key for each row of your recordset. This will help in identifying rows later when you are trying to save.

When populating the flexgrid, at least in the beginning, use all the fields in the database, maintain a one-to-one correspondence, and remember that you have to manipulate the data types.

The primary key, which is usually the first column, should ideally go to the first column of the grid, which you can set to be invisible, so the cryptic IDs won't be displayed to the user.

While saving, you can use this ID, locate the record in the recordset. If found, edit it, else add a new one and save it. The code would then look like:

Dim I As Integer, J As Integer
For I = 0 To grd.Rows - 1 Step 1
   rs.FindFirst "ID='" & grd.TextMatrix(I, 0) & "'"
   If rs.NoMatch Then
      rs.AddNew
   End If
   For J = 0 To grd.Cols - 1 Step 1
      rs(J) = grd.TextMatrix(I, J)  'Beware of data types
   Next
Next

Also, this is DAO code, so please make appropriate changes in ADO.

.

[ishikha]

hi,

u want to say that i enter one more column in my table and made it a primary key.then what type of data i enter in that column

presently i am having only three fields in the table .the datatypes of all the three fields are text.and none of the fields is primary.

and also in ado there is no such property of findfirst

ishikha

[ishikha]

hi,

plzz somebody tell me the solution of this .

ishikha

[mendhak]

Honeyb is telling you to create a primary field in your database. without a primary key, you'll be facing problems.

so go to your database, and make a primary key, such as an autonumber. Call that field ID.

then try this.

Dim I As Integer, J As Integer
For I = 0 To grd.Rows - 1 Step 1
   rs.Find "ID='" & grd.TextMatrix(I, 0) & "'"
   If rs.NoMatch Then
      rs.AddNew
   End If
   For J = 0 To grd.Cols - 1 Step 1
      rs(J) = grd.TextMatrix(I, J)  'Beware of data types
   Next
Next

[ishikha]

hi,

ok i will add one more column in my table and made a primary key now i dinn' want to show on the form .how i will make it invisible

and honeybee give the coding in dao can u plzz tell me what's the coding in ado

regards
ishikha

[mendhak]

Originally posted by ishikha
hi,

ok i will add one more column in my table and made a primary key now i dinn' want to show on the form .how i will make it invisible

and honeybee give the coding in dao can u plzz tell me what's the coding in ado

regards
ishikha

You will make it invisible, by not showing it. so instead of
objrs.fields(0).value you will just put objrs.fields(1).value so the field 0 gets skipped (field 0 is your primary key, usually)

Make sure you add the primary key column as the very first column in your table.


I have given you the ADO for it. It's just a bit different from DAO.

[ishikha]

yes i make that field as the first column.

i am asking u that how i will be able to hide the column first.when on the grid i click on retrieve structure it shows all the four fields then yhow i am able to hide at run time .

and secondly i want that in field id it automatically generates the numbers as the new row is added at the end or if i add the new row in between then it will automatically adjusts the serial no

ishikha

[mendhak]

i am asking u that how i will be able to hide the column first.when on the grid i click on retrieve structure it shows all the four fields then yhow i am able to hide at run time .

That's what I have answered. In your loop in which you are filling up your flexgrid, simply ignore the first field of your table. You MIGHT be having something like

flexgrid.textmatris(1,1) = objrs.fields(0).value
....

well, just make sure you never use the 0 (zero) there. It should start with 1 and go up to 3, or whatever you have. I hope you have understood this, you are simply to ignore the first field in your loop itself.

and secondly i want that in field id it automatically generates the numbers as the new row is added at the end or if i add the new row in between then it will automatically adjusts the serial no

Are you using SQL Server? if so, then in the design table mode, you click on identity seed. If you're using access, you simply make it an autonumber.

However, it will not automatically adjust the serial number.

If it's very very very important that you allow this functionality, then you will have to handcode the entire thing yourself.

[ishikha]

u are not understanding what i am saying
i dinn' want to display field id on the form at run time
how i can do it

and also the code u sent to me is not working
it gives the error in adors.find statement saying that "type mismatch"
and also in adors there is no such property of adors.nomatch



Private Sub flxgrid_leavecell()
Dim strtable1 As String
Dim b As Integer
Dim I As Integer, J As Integer
flxgrid.ScrollBars = flexScrollBarBoth
'Place scrollbars back onto the Flexgrid control.

If Not txtedit.Text = "" Then
'user typed an entry in the text box
flxgrid.TextMatrix(Active_FlxCell.row, Active_FlxCell.col) = txtedit.Text

For I = 0 To flxgrid.Rows - 1 Step 1
adors.find "id='" & flxgrid.TextMatrix(I, 2) & "'"
'If adors.NoMatch Then
' adors.AddNew
'End If
For J = 2 To flxgrid.Cols - 1 Step 1
adors(J) = flxgrid.TextMatrix(I, J) 'Beware of data types
Next
Next
end if
end sub


ishikha

[honeybee]

Your grid must have ALL the columns from the database. To hide a column in the grid, set its width to zero.

.

[ishikha]

hi honeybee,

the code which the mendhak gives for saving the data is not working it is giving the error can u see that and tell me what the error in that

ishikha

[ishikha]

hi honeybee,

the code which the mendhak gives for saving the data is not working it is giving the error can u see that and tell me what the error in that

ishikha

[mendhak]

I think we're having communication problems here. I will stop confusing you, ishikha.

//gracefully bows out of post... for a while...

[vaibhav12]

pl check the post in general vb questions
hope may help u

[vbud]

First of all when you are displaying your data set the first column width (The one where u r displaying your primary field) to 0. e.g :

MSFlexGrid1.ColWidth(0)

Then use this piece of code to save your data (better with a save command button):-

Private Sub SaveRecords()
 
  Dim lcLngi        As Long
  Dim lcStrSQL      As String
  Dim lcFlgUpdate   As Boolean
  
  On Error GoTo ErrHandler
  
  With MSFlexGrid1
    
    For lcLngi = 1 To .Rows '* Or maybe its (Rows - 1)...
          
      lcStrSQL = "INSERT INTO [TableName goes here]([Your FieldNames goes here...]) " _
                & "VALUES('" & .TextMatrix(lcLngi, 1) & "','" _
                & .TextMatrix(lcLngi, 2) & "','" _
                & .TextMatrix(lcLngi, 3) & "')"
                
      '* Execute SQL statement using an ADO Connection
      '* E.g: adoConnection.Execute lcStrSQL
      If lcFlgUpdate Then
        lcStrSQL = "UPDATE [TableName goes here]" _
                 & "SET [FieldName1] = '" & .TextMatrix(lcLngi, 1) & "','" _
                 & "[FieldName2] = '" & .TextMatrix(lcLngi, 2) & "','" _
                 & "[FieldName3] = '" & .TextMatrix(lcLngi, 3) & "' " _
                 & "WHERE [PrimaryKey] = " & CLng(.TextMatrix(lcLngi, 0))
        '* Execute SQL statement using an ADO Connection
        '* E.g: adoConnection.Execute lcStrSQL
        
      End If
      
    Next
    
  End With
  
  Exit Sub
  
ErrHandler:
  '* Detect duplication error here - i don`t remember it...
  If Err.Number = ["Duplication error"] Then
    lcFlgUpdate = True
    Resume Next
  Else
    MsgBox Err.Number & ": " & Err.Description
  End If
  
End Sub

[ishikha]

hi,

i dinn' able to understand ur code can u simplify it
or u write the code acc to my table .

the structure of my table is:

my table name is document and it is having three fields

1.folder 2.fileno 3.name

all the three fields are of text type
and the database i am using is access 97

ishikha

[vaibhav12]

Problem solved ?

[vbud]

Ok here`s an amended version but you still have 2 use a primary key...

Private Sub SaveRecords()
 
  Dim lcLngi        As Long
  Dim lcStrSQL      As String
  Dim lcFlgUpdate   As Boolean
  Dim lcLngErrLevel As Long
  Dim lcObjConn     As ADODB.Connection
  Dim lcStrConnString As String
  
  On Error GoTo ErrHandler
  
  Set lcObjConn = New ADODB.Connection
  lcStrConnString = "Provider=Micorsoft.Jet.OLEDB.4.0 ...etc.."
  lcObjConn.Open lcStrConnString
  
  With MSFlexGrid1
    
    lcLngErrLevel = 1000
    
    For lcLngi = 1 To .Rows '* Or maybe its (Rows - 1)...
          
      lcStrSQL = "INSERT INTO Document(Folder, FileNo, Name) " _
                & "VALUES('" & .TextMatrix(lcLngi, 1) & "','" _
                & .TextMatrix(lcLngi, 2) & "','" _
                & .TextMatrix(lcLngi, 3) & "')"
                      
      lcLngErrLevel = 1100
      '* Execute SQL statement using an ADO Connection
      lcObjConn.Execute lcStrSQL
      lcLngErrLevel = 1200
      
      If lcFlgUpdate Then
        lcLngErrLevel = 1300
        lcStrSQL = "UPDATE Document " _
                 & "SET Folder = '" & .TextMatrix(lcLngi, 2) & "', '" _
                 & "Name = '" & .TextMatrix(lcLngi, 3) & "' " _
                 & "WHERE FileNo = '" & .TextMatrix(lcLngi, 0) & "'" '* Replace with your primary key here!
                 
        '* Execute SQL statement using an ADO Connection
        lcObjConn.Execute lcStrSQL
        lcLngErrLevel = 1400
      End If
      
    Next
    
  End With
  
  Set lcObjConn = Nothing
  
  Exit Sub
  
ErrHandler:
  
  If lcLngErrLevel = 1100 Then
    lcFlgUpdate = True
    Resume Next
  Else
    Set lcObjConn = Nothing
    MsgBox Err.Number & ": " & Err.Description
  End If
  
End Sub

[ishikha]

hi vbud,

can u tell me certain things

1. lcLngErrLevel indicates what?why u have used this field

2. after these statements it is giving the error

On Error GoTo ErrHandler
Set lcObjConn = New ADODB.Connection
lcStrConnString = "Provider=Micorsoft.Jet.OLEDB.3.51;Data Source=c:\winnt\inventory.mdb;Persist Security Info=False"

lcObjConn.Open lcStrConnString


the error is "ado could not find the specific provider"
can u tell me what is wrong in that

3. earlier i am having only three fields folder,fileno,name

but now i add one more field in the starting and gave it the datatype "autonumber" as u have said it is neccesary to have a primary key.but on the form i am not showing this field .

i set its colwidth property to 0

now what are the changes i have to made in the code u have given it to me

thanx in advance

ishikha

[vbud]

1. lcLngErrLevel indicates what?why u have used this field

Well this is just a variable that will tell me exactly where did the
error occur.(In our case, its the duplication error although there`s
a wiser way to do that...).

2. after these statements it is giving the error

On Error GoTo ErrHandler
Set lcObjConn = New ADODB.Connection
lcStrConnString = "Provider=Micorsoft.Jet.OLEDB.3.51;Data Source=c:\winnt\inventory.mdb;Persist Security Info=False"

lcObjConn.Open lcStrConnString


the error is "ado could not find the specific provider"
can u tell me what is wrong in that

use this one:

lcStrConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                  & "Data Source=c:\winnt\inventory.mdb;" _
                  & "User Id=Admin;Password=;"

its specific for ADO and although you are using Access 97, I
guess, you can use OLEDB 4.0 instead of 3.51

try this one:

Private Sub SaveRecords()
 
  Dim lcLngi        As Long
  Dim lcStrSQL      As String
  Dim lcFlgUpdate   As Boolean
  Dim lcLngErrLevel As Long
  Dim lcObjConn     As ADODB.Connection
  Dim lcStrConnString As String
  
  On Error GoTo ErrHandler
  
  Set lcObjConn = New ADODB.Connection
  lcStrConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                  & "Data Source=c:\winnt\inventory.mdb;" _
                  & "User Id=Admin;Password=;"
 
  lcObjConn.Open lcStrConnString
  
  With MSFlexGrid1
    
    lcLngErrLevel = 1000
    
    For lcLngi = 1 To .Rows '* Or maybe its (Rows - 1)...
          
      lcStrSQL = "INSERT INTO Document(Folder, FileNo, Name) " _
                & "VALUES('" & .TextMatrix(lcLngi, 1) & "','" _
                & .TextMatrix(lcLngi, 2) & "','" _
                & .TextMatrix(lcLngi, 3) & "')"
                      
      lcLngErrLevel = 1100
      '* Execute SQL statement using an ADO Connection
      lcObjConn.Execute lcStrSQL
      lcLngErrLevel = 1200
      
      If lcFlgUpdate Then
        lcLngErrLevel = 1300
        lcStrSQL = "UPDATE Document " _
                 & "SET Folder = '" & .TextMatrix(lcLngi, 1) & "', '" _
                 & "Name = '" & .TextMatrix(lcLngi, 2) & "' " _
         & "FileNo = '" & .TextMatrix(lcLngi, 3) & "'"
                 & "WHERE [insert yout Primary Key Fieldname here] = '" & .TextMatrix(lcLngi, 0) & "'" '* Replace with your primary key here!
                 
        '* Execute SQL statement using an ADO Connection
        lcObjConn.Execute lcStrSQL
        lcLngErrLevel = 1400
      End If
      
    Next
    
  End With
  
  Set lcObjConn = Nothing
  
  Exit Sub
  
ErrHandler:
  
  If lcLngErrLevel = 1100 Then
    lcFlgUpdate = True
    Resume Next
  Else
    Set lcObjConn = Nothing
    MsgBox Err.Number & ": " & Err.Description
  End If
  
End Sub

Your are most welcomed...let me know how it goes

[ishikha]

hi vbud,

it is giving the syntax error in update statement

ishikha

[ishikha]

hi,

the code u have given is not working well .it is also adding new record if it is already existing

ishikha

[vbud]

Sorry for the previous code, i wrote it on the fly and did not even test run it.

Here it goes:

Private Sub SaveRecords()
  
  '* 1. When you are inserting new rows inside your grid,
  '*    Make sure that you are moving the primary key too:
  '*    That is move the value in Column 0 just like you
  '*    would do for the other visible columns.
  '* 2. In the Update SQL Statement below, Replace [Key Fieldname] with
  '*    the name of the primary key you are using in your Document table.
  '* 3. As soon as you finish saving your records, IMMEDIATELY reload
  '*    the data from your table back to the grid, to make sure that
  '*    the primary key for the newly inserted records are loaded.
  
  Dim lcLngi          As Long
  Dim lcStrSQL        As String
  Dim lcLngErrLevel   As Long
  Dim lcObjConn       As ADODB.Connection
  Dim lcStrConnString As String
  
  On Error GoTo ErrHandler
  
  Set lcObjConn = New ADODB.Connection
  
  lcStrConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                  & "Data Source=c:\winnt\inventory.mdb;" _
                  & "User Id=Admin;Password=;"
 
  lcObjConn.Open lcStrConnString
  
  With MSFlexGrid1
    
    lcLngErrLevel = 1000
    
    For lcLngi = 1 To .Rows - 1
      
      '* We check if there is a primary key in the first column of the grid
      If Len(Trim$(MSFlexGrid1.TextMatrix(lcLngi, 0))) > 0 Then
        '* if so then Record already exist in database, we update it here
        lcStrSQL = "UPDATE Document " _
                 & "SET Folder = '" & .TextMatrix(lcLngi, 1) & "', " _
                 & "Name = '" & .TextMatrix(lcLngi, 2) & "', " _
                 & "FileNo = '" & .TextMatrix(lcLngi, 3) & "' " _
                 & "WHERE [Key Fieldname] = " & CLng(.TextMatrix(lcLngi, 0)) '* Replace with your primary key here!
      
      Else
        '* Else Insert new record
        lcStrSQL = "INSERT INTO Document(Folder, FileNo, Name) " _
                 & "VALUES('" & .TextMatrix(lcLngi, 1) & "','" _
                 & .TextMatrix(lcLngi, 2) & "','" _
                 & .TextMatrix(lcLngi, 3) & "')"
      End If
 
      '* Execute SQL statement using an ADO Connection
      lcObjConn.Execute lcStrSQL
     
    Next
    
  End With
  
  Set lcObjConn = Nothing
  
  Exit Sub
  
ErrHandler:
 
  Set lcObjConn = Nothing
  MsgBox Err.Number & ": " & Err.Description
  
End Sub