connect datagrid to access db using ado code in vb6

[wegdan]

i need to connect the data grid to access db using ado code and vb6
(without using adodc control)and diplay the result of a select statemet on it
and here is the code that i am using
(it give me a run time errorr 3705 : the operation is not allowed when the object is open)

/in the module

Global con As New Connection               
Global tel_idx As New Recordset

/in the form load event

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Telephone_Index\Telephone_Index.mdb"

tel_idx.Open "tel_index", con, adOpenDynamic, adLockOptimistic

/in the command button click event

Set tel_idx = con.Execute("SELECT * FROM tel_index where name= '" + Text17.Text + "';")
tel_idx.CursorLocation = adUseClient
Set DataGrid2.DataSource = tel_idx

[aktharshaik]

Dont open the recordset in form load event.

'in the module

Global con As New Connection
Global tel_idx As New Recordset

'in the form load event

'Also dont hardcode the database path
'con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Telephone_Index\Telephone_Index.mdb"
'Write something like this
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Telephone_Index.mdb"


'Remove this line
tel_idx.Open "tel_index", con, adOpenDynamic, adLockOptimistic



'in the command button click event
'Rest All is ok
'If opening the recordset in Form_Load() Event is required for u for some other logic then
'u must close the record set first before opening it again.

    If Not tel_idx is Nothing Then
        If tel_idx.State Then tel_idx.Close
    End If
    tel_idx.CursorLocation = adUseClient
    Set tel_idx = con.Execute("SELECT * FROM tel_index where name= '" + Text17.Text + "';")
    Set DataGrid2.DataSource = tel_idx

Hope this solves ur problem

Regards
Shaik Akthar

[wegdan]

when i did that i had the error: the rowset is not bookmarkable
in the line:Set DataGrid2.DataSource = tel_idx
the code i wrote in the command click event is

If Combo2.Text = "name" Then

If Not tel_idx Is Nothing Then
        If tel_idx.State Then tel_idx.Close
    End If
    tel_idx.CursorLocation = adUseClient
    Set tel_idx = con.Execute("SELECT * FROM tel_index where name= '" + Text17.Text "';")
  ' tel_idx.CursorLocation = adUseClient
    Set DataGrid2.DataSource = tel_idx

End If

[K.Vanlalliana]

Write this code in Module

Public con As New ADODB.Connection

Dim ttel_idxRs As New ADODB.Recordset 
Dim tsql As String

The follwoing connection code can be written both in the module or Form

Set con = New ADODB.Connection
        con.Provider = "Microsoft.Jet.OLEDB.4.0; Data Source = " & App.Path & "Telephone_IndexTelephone_Index.mdb"
        con.Open

The following code should be written to get the table content, you can write both in the module and form

Set ttel_idxRs = New ADODB.Recordset
    tsql = "SELECT * FROM tel_index"
    ttel_idxRs.Open tsql, Con, adOpenKeyset, adLockOptimistic

To be able to use ADO Connection, You have to select the following from the References:

Microsoft ActiveX Data Object 2.1 Library
Microsoft Connection Designer V6.0

To select the above, Go to Project and then select reference, otherwise, your code will not go well.

Other problem may happen due to your SQL statement, especially in the Select Statement.

Your Statement is:

("SELECT * FROM tel_index where name= '" + Text17.Text + "';")

This SQL may be better to write like this

("SELECT * FROM tel_index")

OR

for selecting specific name/number

("SELECT * FROM tel_index where name= '" + Text17.Text + "'")

Look Carefully and see the difference.

If the 'name' column is text format, the above code will be correct, but if it is integer, you have to write like this

("SELECT * FROM tel_index where name= " & Cint(Text17.Text))

Hope this solves your problem

KVL Liana

[dspnhn]

I often use dynamic grid posting in my applications try this :

Set con = New ADODB.Connection
con.cursorlocation=aduseclient
con.open= "Microsoft.Jet.OLEDB.4.0; Data Source = " & App.Path & "\Telephone_Index\Telephone_Index.mdb"

Now to populate or fill the grid with your record do this :

Set ttel_idxRs = New ADODB.Recordset
ttel_idxRs.cusorlocation=aduseclient
ttel_idxRs.open ( "SELECT * FROM tel_index "),con,adOpenKeyset, adLockOptimistic
set datagrid1.datasource=ttel_idxRs
ttel_idxRs.requery

IF YOU WANT TO SPECIFY COLUMNS WIDTH U CAN DO SO LIKE THIS

DataGrid1.Columns.Item(0).Width = 700
DataGrid1.Columns.Item(1).Width = 3000
DataGrid1.Columns.Item(2).Width = 1000
DataGrid1.Columns.Item(3).Width = 2000

EVERYTIME U CHANGE OR APPEND SOME THING ALL U HAVE TO DO IS USE

ttel_idxRs.requery

Regards
DSP

[wegdan]

thanks for help and please i have another problem
after showing the search result on the datagrid i want to modify it and send it back
to the msaccess db and validate each value (make sure that the phone number does not contain letters) before sending it to db ...how can i do this

[wegdan]

how can i use the getbookmark method and bookmark property to update the datagrid

[Chirayu@1988]

i tried your code bt it gives me error ..
"Member or Data method not found" ...nw wat sud i do

[debasisdas]

What about starting a new thread of your own and not digging a dead thread .

[aktharshaik]

Please send your complete code once, so that i may identify the problem.

Regards
Shaik Akthar

[thesatisfaction]

Private Sub Form_Load()
Dim con As New Connection
Dim tel_idx As New Recordset
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\dbma.mdb"
tel_idx.Open "Usermaster", con, adOpenDynamic, adLockOptimistic

  ' tel_idx.CursorLocation = adUseClient
    Set tel_idx = con.Execute("SELECT * FROM Usermaster where sn <>'+""+' ")
Set DataGrid1.DataSource = tel_idx
End Sub

[thesatisfaction]

Please send your complete code once, so that i may identify the problem.

Regards
Shaik Akthar

'Dim con As New Connection
'Dim tel_idx As New Recordset
'con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\dbma.mdb"
'tel_idx.Open "Usermaster", con, adOpenDynamic, adLockOptimistic
'
' ' tel_idx.CursorLocation = adUseClient
' Set tel_idx = con.Execute("SELECT * FROM Usermaster where sn <>'+""+' ")
'Set DataGrid1.DataSource = tel_idx

[subalakh]

hello sir
i tried the same code what previously discussed by you in early comments.that is the problem of searching a data in database and if found the search record have to display in datagrid.but i have a problem that in the reference - i dont found
1.Microsoft ActiveX Data Object 2.1 Library
2.Microsoft Connection Designer V6.0
in the reference option
what i have to do?
please send how i have to try
thanks in advance

[jhaiyz]

Dim Conn As ADODB.Connection
Dim Rs1 As ADODB.Recordset
Dim ConnString As String
Dim SqlString As String

ConnString = "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" & App.Path & "\Database\DB.accdb;Persist Security Info=False;Jet OLEDB:Database Password=DBpassword"
SqlString = "select *from table"

Set Conn = New ADODB.Connection
Conn.Open ConnString
Set Rs1 = New ADODB.Recordset
Rs1.CursorLocation = adUseClient
Rs1.Open SqlString, Conn, adOpenStatic, adLockOptimistic
set DataGrid = Rs1