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