DataGrid vs ADODB datacontrol programming - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

DataGrid vs ADODB datacontrol programming

eatc7402eatc7402 Posts: 15Member
I have a VB6 app with a DataGrid Control 'connected' to an ADODB DataContol. The DataControl provides the connection to Access DB,
and attaches to the desired underlying query. The proper data gets
displayed in the data grid. However, this whole affair is STATIC, in that
it can only display what was set into the ADODB datacontrol as design time.

So, I figured I should be able to make this all dynamic by adding some programming.

My first attempt was to programm the datagrid .RecordSource field with
my ADODB recordset. The recordset was produced via an EXECUTE command
because I desire run 'run' predefind Access queries. The result however
was a runtime 'error 7004, the recordset is not bookmarkable". My
investigation of this error seems to indicate that you CANNOT (?)
obtain a 'bookmarkable' dataset via an EXECUTE.

So question #1... Is there a way to set the recordset property
of a datagrid with the result of an EXECUTE staement such as...

'-------------------------------------------
With cmdSQL
.ActiveConnection = DbaseConn ' connection to db
.CommandType = adCmdStoredProc ' this is for a stored proc
.CommandText = QueryString 'Query name
'rs.CursorType = adOpenStatic
Set rs = .Execute ' do it!
End With

Set frmData1.DataGrid1.datasource = rs
'----------------------------------------

I tried adding the .CursorType = adOpenStatic but that did not fix
anything.

The Set ...DataSource = rs is what results in the runtime
'not bookmarkable' error.

So I thought why not program an ADODB datacontrol and leave it attached
to the datagrid with code like this...

'-----------------------------------------
With frmData1.DataForm1Adodc1
.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"pwd=;" & _
"UID=;" & _
"DBQ=" & App.Path & "/Fires_all.mdb"
.CommandType = adCmdStoredProc
.RecordSource = "SORTED_Pics_Query"
End With
'---------------------------------------

This code mymics the static values added to the ADODB DataControl at
design time (which works), but when run 'dynamically' it presents no
errors, BUT also show NO data.

It would seem that directly programming the data grid is a better choice
but results in the 'bookmark' issuses. Programming the datacontrol
I can't to work. What am I doing wrong?

eatc7402
DaveF.

Comments

  • HackmanCHackmanC Posts: 441Member
    [b][red]This message was edited by HackmanC at 2006-6-14 13:45:4[/red][/b][hr]
    Dynamic cursor allows you to view additions, changes, and deletions by other users; allows all types of movement through the Recordset that doesn't rely on bookmarks; and allows bookmarks if the provider supports them.

    Keyset cursor behaves like a dynamic cursor, except that it prevents you from seeing records that other users add, and prevents access to records that other users delete. Data changes by other users will still be visible. It always supports bookmarks and therefore allows all types of movement through the Recordset.


    CursorLocation Property

    This property allows you to choose between various cursor libraries accessible to the provider. Usually, you can choose between using a client-side cursor library or one that is located on the server.

    Cursors returned by the Execute method inherit this setting. Recordset objects will automatically inherit this setting from their associated connections.

    [code]
    Recordset.Properties("Bookmarkable") = true
    [/code]
    should (or should not, cause the provider) provide a bookmark.

    Bookmarkable property depends of the Data Provider Library, Microsoft Access provides Bookmarks in the client and the the server, (cause is not a real server), but SQL Server do no provide bookmars in the server side.

    Try to "Locate" your recordset in a Client Library, with something like this, (before you get the data)... Recorset.CursorLocation = adUseClient.

    Also remeber that you can use Prepared Statements SQL Syntax in you Recordset RecordSource.
    "{call sp_MyStoredProc_001()}" with params ... "{call sp_MyStoredProc_001(?, ?)}"

    ----
    : I have a VB6 app with a DataGrid Control 'connected' to an ADODB DataContol. The DataControl provides the connection to Access DB,
    : and attaches to the desired underlying query. The proper data gets
    : displayed in the data grid. However, this whole affair is STATIC, in that
    : it can only display what was set into the ADODB datacontrol as design time.
    :
    : So, I figured I should be able to make this all dynamic by adding some programming.
    :
    : My first attempt was to programm the datagrid .RecordSource field with
    : my ADODB recordset. The recordset was produced via an EXECUTE command
    : because I desire run 'run' predefind Access queries. The result however
    : was a runtime 'error 7004, the recordset is not bookmarkable". My
    : investigation of this error seems to indicate that you CANNOT (?)
    : obtain a 'bookmarkable' dataset via an EXECUTE.
    :
    : So question #1... Is there a way to set the recordset property
    : of a datagrid with the result of an EXECUTE staement such as...
    :
    : '-------------------------------------------
    : With cmdSQL
    : .ActiveConnection = DbaseConn ' connection to db
    : .CommandType = adCmdStoredProc ' this is for a stored proc
    : .CommandText = QueryString 'Query name
    : 'rs.CursorType = adOpenStatic
    : Set rs = .Execute ' do it!
    : End With
    :
    : Set frmData1.DataGrid1.datasource = rs
    : '----------------------------------------
    :
    : I tried adding the .CursorType = adOpenStatic but that did not fix
    : anything.
    :
    : The Set ...DataSource = rs is what results in the runtime
    : 'not bookmarkable' error.
    :
    : So I thought why not program an ADODB datacontrol and leave it attached
    : to the datagrid with code like this...
    :
    : '-----------------------------------------
    : With frmData1.DataForm1Adodc1
    : .ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
    : "pwd=;" & _
    : "UID=;" & _
    : "DBQ=" & App.Path & "/Fires_all.mdb"
    : .CommandType = adCmdStoredProc
    : .RecordSource = "SORTED_Pics_Query"
    : End With
    : '---------------------------------------
    :
    : This code mymics the static values added to the ADODB DataControl at
    : design time (which works), but when run 'dynamically' it presents no
    : errors, BUT also show NO data.
    :
    : It would seem that directly programming the data grid is a better choice
    : but results in the 'bookmark' issuses. Programming the datacontrol
    : I can't to work. What am I doing wrong?
    :
    : eatc7402
    : DaveF.
    :

    [red]Good luck![/red]
    [blue]Hackman[/blue]
  • eatc7402eatc7402 Posts: 15Member
    Well, thanks for the suggestions, but after trying them the PROBLEM
    REMAINS. If I set the recordset property of a data grid to the recordset
    returned by my Access query I still get...

    Recordset is not bookmarkable.... hummmmm!

    Is there a way to set up a 'proper' dataset, and then somehow
    COPY(?) my Access returned recordset to it, and use the copied
    recordset with the data control. All I am trying to do is VIEW
    the returned data. I have no intention of adding, subtracting,
    or changing the data... just view it. The ADODC attatched to a dataset
    is easy, but I can't seem to get it to be PROGRAMMABLE. If I could
    fix THAT In could solve my whole problem.

    Thsnks,
    eatc7402
    Dave

  • HackmanCHackmanC Posts: 441Member
    [b][red]This message was edited by HackmanC at 2006-6-15 12:32:15[/red][/b][hr]
    [code]
    Private Sub Form_Load()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim p As ADODB.Parameter
    Dim r As ADODB.Recordset

    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsAdministratorMy documents estdata.mdb;Persist Security Info=False"
    conn.Open
    [red]
    conn.CursorLocation = adUseClient
    REM To use the client library of MSAccess
    [/red]

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    [blue]
    REM cmd.Properties("Bookmarkable") = True
    REM cmd.Prepared = True
    REM No need to prepare or change the bookmarkable
    REM MSAccess just ignore it
    [/blue]

    Set p = New ADODB.Parameter
    p.Name = "PARAM1"
    p.Type = adSmallInt
    p.Direction = adParamInput
    p.Value = 10
    cmd.Parameters.Append p

    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "Query1"
    Set r = cmd.Execute
    [red]
    Debug.Print r.Supports(adBookmark)
    REM See that is bookmarkable now
    [/red]

    Set Me.Adodc1.Recordset = r
    [green]
    REM Me.DataGrid1.DataSource = Me.Adodc1
    REM Cant assign the datasource in run time. do it in design time.
    [/green]
    End Sub
    [/code]

    I used VB 6.0 with ADO 2.5 and MSAccess XP.
    It runs fine.
    I suppose your query has PARAMETERS.
    If have not parameters, you dont have to EXECUTE.
    Use the query of MSAccess LIKE A TABLE. "r.Open "Query""

    --------
    : Well, thanks for the suggestions, but after trying them the PROBLEM
    : REMAINS. If I set the recordset property of a data grid to the recordset
    : returned by my Access query I still get...
    :
    : Recordset is not bookmarkable.... hummmmm!
    :
    : Is there a way to set up a 'proper' dataset, and then somehow
    : COPY(?) my Access returned recordset to it, and use the copied
    : recordset with the data control. All I am trying to do is VIEW
    : the returned data. I have no intention of adding, subtracting,
    : or changing the data... just view it. The ADODC attatched to a dataset
    : is easy, but I can't seem to get it to be PROGRAMMABLE. If I could
    : fix THAT In could solve my whole problem.
    :
    : Thsnks,
    : eatc7402
    : Dave
    :
    :

    [red]Good luck![/red]
    [blue]Hackman[/blue]
  • eatc7402eatc7402 Posts: 15Member
    :
    : Set conn = New ADODB.Connection
    : conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsAdministratorMy documents estdata.mdb;Persist Security Info=False"
    : conn.Open
    : [red]
    : conn.CursorLocation = adUseClient
    : REM To use the client library of MSAccess
    : [/red]
    :

    FIXED!. thanks.

    The problem was the consistant NON-BOOKMARKABLE recordset
    error message. I had a similar line of code to YOUR
    conn.CursorLocation = adUseClient line, EXCEPT
    that I was attempting to do this
    recordset.CursorLocation = adUseClient
    which DOES NOT appear to make the recordset bookmarkable.

    Yours works, mine did not. Now it does
    Thanks.
Sign In or Register to comment.