Problem in DataGrid + ADODC

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

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!

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

Code:

---

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

Code:

---

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.

Code:

---

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!

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.

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.

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

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. :)