Problem in DataGrid + ADODC

[pchai]

I donno why, but I cannot add record in the DataGrid with the following code.


Cnn.Execute ("create table [Table1] " & _ "
(Field1 INT,Field2 DateTime,Field3 VarChar(8000))")

Adodc1.ConnectionString = Cnn.ConnectionString
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = "select * from [Table1]"
Adodc1.Refresh

DataGrid1.DataSoruce = Adodc1

When I try to add a row in it
it will have a not fatal error message
"The current row is not available"

What is this mean?
Thanks

[vbuser1976]

Hmm, have you tried using the adodb instead of the data control(adodc1)? Try it by creating a recordset and then assigning the datagrid to the recordset. If you need a sample code on how to do that, let me know.

Good luck!

[pchai]

I have tried ADODB but don't know how to use ADODB
I Think I need some source code for a example, Thanks

[vbuser1976]

Private Sub PopulateLists() 'This procedure populates all of the lists in the database
    Dim rs As ADODB.Recordset
    Dim cm As ADODB.Command
    
    Set cm = New ADODB.Command
    Set cm.ActiveConnection = cn
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "sp_get_tech_names"
    Set rs = cm.Execute
    
    lstSvcTech.Clear
    lstSCrew1.Clear
    lstSCrew2.Clear
    lstSCrew3.Clear
    
    Do Until rs.EOF
        lstSvcTech.AddItem rs.Fields("Tech")
        lstSvcTech.ItemData(lstSvcTech.NewIndex) = rs.Fields("TechId")
        
        lstSCrew1.AddItem rs.Fields("Tech")
        lstSCrew1.ItemData(lstSCrew1.NewIndex) = rs.Fields("TechId")
        
        lstSCrew2.AddItem rs.Fields("Tech")
        lstSCrew2.ItemData(lstSCrew2.NewIndex) = rs.Fields("TechId")
        
        lstSCrew3.AddItem rs.Fields("Tech")
        lstSCrew3.ItemData(lstSCrew3.NewIndex) = rs.Fields("TechId")
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
End Sub

Sample 2

Private Sub ProcessData() 'This procedure processes and saves the inputted data
                            'to the database
    Dim response As Integer
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    
    If txtRepId.Text = "" Then
    txtRepId.Text = "0"
    End If
    
    strSQL = "Exec sp_get_report_id " & txtRepId.Text
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = cn
    rs.Source = strSQL
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open , , , , adCmdText
        
    If (txtRepId.Text = 0 Or txtRepId.Text = "") Then
        rs.AddNew
    End If
    
    With rs
        .Fields("AtmId") = txtAtmId.Text
        .Fields("ContactName") = txtContName.Text
        .Fields("ContactPhone") = txtContPhone.Text
        .Fields("DOccurence") = txtDoccur.Text
        .Fields("NOccurence") = txtNature.Text
        .Fields("ServiceTech") = lstSvcTech.Text
        .Fields("STDate") = txtSvcDate.Text
        .Fields("STTime") = txtSvcTime.Text
        .Fields("SCrew1") = lstSCrew1.Text
        .Fields("SCrew2") = lstSCrew2.Text
        .Fields("SCrew3") = lstSCrew3.Text
        .Fields("SCDate") = txtSCDate.Text
        .Fields("SCTime") = txtSCTime.Text
        .Fields("Resolution") = txtResolution.Text
        .Fields("Result") = txtResult.Text
        .Fields("DateReported") = txtTodaysDate.Text
        .Fields("ReportedBy") = txtUserName.Text
        .Fields("EmailTo") = lstEmailTo.Text
   End With
   
   rs.Update
   txtRepId.Text = rs.Fields("ReportId")
   response = MsgBox("Your Report Id is " & rs.Fields("ReportId") & _
   ".", vbOKOnly, Title:="Your Report Id")
   
   SendEmail txtRepId.Text, txtAtmId.Text
   rs.Close
   Set rs = Nothing
End Sub

Remember, these are just samples. You would have to change them to fit your needs. Good luck.

[vbuser1976]

Private Sub cmdFind_Click() 'This procdedure is for the dynamic search of the database
    Dim strSQL As String
    Dim strSQLAdd As String
    Dim rs As ADODB.Recordset
        
    Set rs = New ADODB.Recordset
    
    If cn.State = adStateClosed Then
        cn.CursorLocation = adUseClient
        cn.Open
    End If
    
    strSQL = "select AtmId, ReportId, ServiceTech, DOccurence from tblReport "
    
    If optAtm = True Then
        strSQLAdd = "where AtmId = '" & txtSearch.Text & "' order by AtmId"
    ElseIf OptReportID = True Then
        strSQLAdd = "where ReportId = " & CInt(txtSearch.Text) & " order by ReportId"
    ElseIf OptTech = True Then
        strSQLAdd = "where ServiceTech LIKE '" & UCase(txtSearch.Text) & "%' order by ServiceTech"
    End If
        
    strSQL = strSQL & strSQLAdd
    
    rs.Open strSQL, cn, adOpenStatic, adLockBatchOptimistic, adCmdText
    
    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
    End If
    
    Set MSHFlexGrid1.DataSource = rs
    rs.Close
    rs.ActiveConnection = Nothing
End Sub

Good Luck!

[pchai]

I have followed your last example.
And I find out Nothing will be displayed when I am using DataGrid.
When I change to use MHFlexGird.
I can display the item but still, I cannot edit or add new.

[vbuser1976]

What you will have to do is create separate subs to delete and to add. You have to do this because the recordset has to be closed and reopened for every separate thing you do on the flexgrid.

I hope this doesn't sound confusing.

[pchai]

Actually I just want to open a gird and let user edit the data directly and freely.
just like access or visual data manager.
by the way, thanks for your kindy help

[vbuser1976]

Okay, since you put it that way, MSHFlexGrid is only for viewing/browsing data (from what I have read). In order for it to work the way you want it to, you will need to use dbgrid or revert back to datagrid.

I'm sorry I mislead you. If you use the dbgrid or datagrid control, all you have to do is go under its properties and set the AllowAddNew, AllowUpdate, and AllowDelete properties to true.

If you still want to use the MSHFlexGrid you will have to do it like I stated before.

BTW, your welcome.