Results 1 to 8 of 8

Thread: db.OpenRecordset ADO Counterpart [RESOLVED]

  1. #1

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,725

    Resolved db.OpenRecordset ADO Counterpart [RESOLVED]

    Hello i would like to doubleclick any record in my listview and the record will display in texboxes.

    I have this code using DAO. What is its counterpart in ADODB?

    VB Code:
    1. Private Sub lstProducts_DblClick()
    2.   If rsProducts.State = adStateOpen Then rsProducts.Close
    3.   rsProducts.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    4.  
    5.   If lstProducts.ListItems.Count <> 0 Then
    6. Set rsProducts = db.OpenRecordset("SELECT * FROM Products WHERE ProdCode = '" & lstProducts.ListItems.Item(lstProducts.SelectedItem.Index).Text & "'")
    7.   'Call RetFields
    8.   Frame1.Enabled = True
    9.   txtDescription.SetFocus
    10.   SendKeys "{home}+{end}"
    11.   cmdAdd.Enabled = False
    12. End If
    13. End Sub
    Last edited by Simply Me; Apr 25th, 2006 at 12:50 AM.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  2. #2
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,044

    Re: db.OpenRecordset ADO Counterpart

    Your ...

    VB Code:
    1. If rsProducts.State = adStateOpen Then rsProducts.Close
    2.   rsProducts.Open sSQL, oConn, adOpenStatic, adLockOptimistic

    is ADO...
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  3. #3

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,725

    Re: db.OpenRecordset ADO Counterpart

    yes its ADO.

    I dont know the counterpart of this line in ADO
    VB Code:
    1. Set rsProducts = db.OpenRecordset("SELECT * FROM Products WHERE ProdCode = '" & lstProducts.ListItems.Item(lstProducts.SelectedItem.Index).Text & "'")

    OR if there's a better way (code) to do double clicking in a listview and display its contents in textboxes for editing/updating purposes
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  4. #4
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,044

    Re: db.OpenRecordset ADO Counterpart

    That would be...

    VB Code:
    1. rsProducts.Open "SELECT * FROM Products WHERE ProdCode = '" & lstProducts.ListItems.Item(lstProducts.SelectedItem.Index).Text & "'", oConn, adOpenStatic, adLockOptimistic
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  5. #5

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,725

    Re: db.OpenRecordset ADO Counterpart

    Quote Originally Posted by dee-u
    That would be...

    VB Code:
    1. rsProducts.Open "SELECT * FROM Products WHERE ProdCode = '" & lstProducts.ListItems.Item(lstProducts.SelectedItem.Index).Text & "'", oConn, adOpenStatic, adLockOptimistic
    I getting error message "operation is not allowed when the object is open"

    here's my whole code im sure it will help:
    VB Code:
    1. Option Explicit
    2. Dim rsProducts As ADODB.Recordset
    3. Dim rstempProducts As ADODB.Recordset
    4. Dim sSQL$, TempCode%
    5.  
    6. Private Sub cmdAdd_Click()
    7.   Frame1.Enabled = True
    8.   txtProdCode.SetFocus
    9.  
    10.   cmdDelete.Enabled = False
    11.   cmdUpdate.Enabled = False
    12.   Call ClearFunction(frmProducts, "TextBox")
    13.   cmdAdd.Caption = "&Save"
    14.  
    15.   txtEntryDate.Text = Format(Date, "mmmm dd, yyyy")
    16.  
    17. End Sub
    18.  
    19. Private Sub cmdClose_Click()
    20.   Unload Me
    21. End Sub
    22.  
    23. Private Sub cmdDelete_Click()
    24.   If CheckNullValue1 = False Then Exit Sub
    25.  
    26. On Error GoTo errHandler
    27.   'If (MsgBox("Do you really want to delete this item: " & lstProducts.SelectedItem.SubItems(1) & "?", vbCritical + vbYesNo + vbDefaultButton2, "FERGEM Sales and Inventory System") = vbNo) Then
    28.  
    29.   If (MsgBox("Product " & lstProducts.SelectedItem.SubItems(1) & " will be permanently be deleted! Delete anyway?", vbCritical + vbYesNo + vbDefaultButton2, "FERGEM Sales and Inventory System") = vbNo) Then
    30.     'Call FillcboAuthorIDAndGrid
    31.     Call ClearFunction(frmProducts, "TextBox")
    32.     Exit Sub
    33.   Else
    34.     With oConn
    35.       .BeginTrans
    36.       ' - Delete records from tblProducts - Note Only the PK is used in deleting _
    37.           records but all the related field will be erased too.
    38.       '--------------------------------------------------------------
    39.       .Execute "DELETE FROM tblProducts " & _
    40.                   "WHERE code='" & txtProdCode.Text & "'"
    41.       '==============================================================
    42.       .CommitTrans
    43.     End With
    44.   End If
    45.  
    46.     Call ClearFunction(frmProducts, "TextBox")
    47.     rsProducts.Requery
    48.     Call FillListView(lstProducts, rsProducts)
    49.  
    50.   Exit Sub
    51. errHandler:
    52.   oConn.RollbackTrans
    53.   Call msgError(Err)
    54.  
    55. End Sub
    56.  
    57. Private Sub Form_Load()
    58.   Call openConnection
    59.   Me.Left = LeftPos
    60.   Me.Top = TopPos
    61.  
    62.   Set rsProducts = New ADODB.Recordset
    63.   Set rstempProducts = New ADODB.Recordset
    64.   rsProducts.CursorLocation = adUseClient
    65.  
    66.  
    67.   sSQL = "SELECT * FROM tblProducts"
    68.  
    69.   If rsProducts.State = adStateOpen Then rsProducts.Close
    70.   rsProducts.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    71.  
    72.   Call FillListView(lstProducts, rsProducts)
    73.   Call LockFunction(frmProducts, "TextBox")
    74. End Sub
    75.  
    76. Private Sub Form_Unload(Cancel As Integer)
    77.   If rsProducts.State = adStateOpen Then rsProducts.Close
    78.   Set rsProducts = Nothing
    79. End Sub
    80.  
    81. Private Sub lstProducts_DblClick()
    82.   'If rsProducts.State = adStateOpen Then rsProducts.Close
    83.   'rsProducts.Open sSQL, oConn, adOpenStatic, adLockOptimistic
    84.  
    85.   If lstProducts.ListItems.Count <> 0 Then
    86.   rsProducts.Open "SELECT * FROM Products WHERE ProdCode = '" & lstProducts.ListItems.Item(lstProducts.SelectedItem.Index).Text & "'", oConn, adOpenStatic, adLockOptimistic
    87.   'rsProducts.Open ("SELECT * FROM Products WHERE ProdCode = '" & lstProducts.ListItems.Item(lstProducts.SelectedItem.Index).Text & "'")
    88.   'Call RetFields
    89.   Frame1.Enabled = True
    90.   txtDescription.SetFocus
    91.   SendKeys "{home}+{end}"
    92.   cmdAdd.Enabled = False
    93. End If
    94. End Sub
    95.  
    96. 'TODO : return true if all required field has been filled
    97. Private Function CheckNullValue1() As Boolean
    98.   CheckNullValue1 = False
    99.  
    100.   If Len(Trim$(txtProdCode.Text)) = 0 Then
    101.     Call msgSelectRec
    102.     Exit Function
    103.   ElseIf Len(Trim$(txtDescription.Text)) = 0 Then
    104.     Call msgSelectRec
    105.     Exit Function
    106.   ElseIf Len(Trim$(txtQuantity.Text)) = 0 Then
    107.     Call msgSelectRec
    108.     Exit Function
    109.   ElseIf Len(Trim$(txtUnit.Text)) = 0 Then
    110.     Call msgSelectRec
    111.     Exit Function
    112.   ElseIf Len(Trim$(txtSPrice.Text)) = 0 Then
    113.     Call msgSelectRec
    114.     Exit Function
    115.   ElseIf Len(Trim$(txtPPrice.Text)) = 0 Then
    116.     Call msgSelectRec
    117.     Exit Function
    118.   End If
    119.  
    120.   'If cmdAddCategory.Enabled = True Then Call ReplaceQuotation(txtCategoryName)
    121.   CheckNullValue1 = True
    122. End Function
    123.  
    124. 'TODO: Populate the ListView control
    125. Sub FillListView(lstProducts As ListView, rsProducts As ADODB.Recordset)
    126.     lstProducts.ListItems.Clear
    127.     If Not rsProducts.BOF Then
    128.         rsProducts.MoveFirst
    129.         Dim a As Long
    130.         Dim lst As ListItem
    131.         While Not rsProducts.EOF
    132.             Set lst = lstProducts.ListItems.Add(, , rsProducts.Fields(0).Value)
    133.             lst.Ghosted = True
    134.             For a = 1 To lstProducts.ColumnHeaders.Count - 1
    135.                 lst.SubItems(a) = rsProducts.Fields(a).Value
    136.             Next
    137.             rsProducts.MoveNext
    138.         Wend
    139.     End If
    140. End Sub
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  6. #6
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,044

    Re: db.OpenRecordset ADO Counterpart

    As you have done earlier you should also check if the recordset is already open before opening it again...

    VB Code:
    1. If rsProducts.State = adStateOpen Then rsProducts.Close
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  7. #7

    Thread Starter
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,725

    Re: db.OpenRecordset ADO Counterpart

    Thanks Dee-u
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  8. #8
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,044

    Re: db.OpenRecordset ADO Counterpart

    No problem. You can now mark this thread as resolved.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width