[RESOLVED] Help on Run-Time Error '-2147217904(80040e10)' in ADOdatabase-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] Help on Run-Time Error '-2147217904(80040e10)' in ADOdatabase

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2006
    Posts
    31

    Resolved [RESOLVED] Help on Run-Time Error '-2147217904(80040e10)' in ADOdatabase

    I cant seem to correct this error im on my wits end please help me...
    I tried to key in a few words to compare to the database but it gives Run-Time Error '-2147217904(80040e10)' no value given for one or more parameters ,in other words after in runtime i add in a record to the database and i want to search it by typing a few letters to compare its error occurs, can you help me see whats wrong??

    VB Code:
    1. Private Sub Form_Load()
    2. 'Open the file of DVDRecord.mdb
    3. myDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    4. App.Path & "\DVDRecord.mdb;Persist Security Info=False"
    5.  
    6. 'Declare myRS as the ADO Recordset
    7. Dim myRS As New ADODB.Recordset
    8. 'Start with one empty record in our array
    9. NumberOfRecords = 0
    10.  
    11. 'Error would occur if first time to append new record
    12. On Error GoTo Empty1
    13. While Not myRS.EOF
    14.     NumberOfRecords = NumberOfRecords + 1
    15.     'Preserve the record in our array and append a new array
    16.     ReDim Preserve myDatabase(NumberOfRecords) As DVD
    17.     'Create physical array
    18.     myDatabase(NumberOfRecords - 1).Name = txtName.Text
    19.     myDatabase(NumberOfRecords - 1).Record = txtRecord.Text
    20.     myDatabase(NumberOfRecords - 1).Price = txtPrice.Text
    21.     'Move to the next point of recordset
    22.     myRS.MoveNext
    23.    
    24. Wend
    25. Empty1:
    26. End Sub
    27.  
    28. Private Sub cmdNew_Click()
    29.  
    30. 'Clear the fields
    31. txtName.Text = ""
    32. txtRecord.Text = ""
    33. txtPrice.Text = ""
    34.  
    35. 'Declare myRS As ADo Recordset
    36. Dim myRS As New ADODB.Recordset
    37.  
    38. myRS.Open "Project_Database", myDB, adOpenKeyset, adLockOptimistic
    39.  
    40. myRS.AddNew
    41.  
    42. 'Set fields
    43. myRS!Name = txtName.Text
    44. myRS!Record = txtRecord.Text
    45. myRS!Price = txtPrice.Text
    46. 'Update the recordset to include new entry
    47. myRS.Update
    48. myRS.Requery
    49. 'Go to the record we just created
    50. myRS.MoveLast
    51.  
    52. 'grab the record's ID value so we can edit it
    53. currentID = myRS.Fields("ID").Value
    54. 'Close the recordset
    55. myRS.Close
    56. End Sub
    57.  
    58. Private Sub cmdSave_Click()
    59. 'edit record in database
    60.  
    61.     Dim myRS As New ADODB.Recordset
    62.     'Open the recordset in the database
    63.     myRS.Open "Project_Database", myDB, adOpenKeyset, adLockOptimistic
    64.     'find the record ID to update
    65.     myRS.Find "ID=" & currentID
    66.        
    67.     'change fields to update them
    68.     myRS.Fields("DVD_Name").Value = txtName.Text
    69.     myRS.Fields("Record_ID").Value = txtRecord.Text
    70.     myRS.Fields("Price").Value = txtPrice.Text
    71.     'Update the recordset and close it
    72.     myRS.Update
    73.     myRS.Close
    74.    
    75. End Sub
    76. Private Sub cmdDelete_Click()
    77. 'Delete record in database
    78.  
    79. Dim answer As String
    80. 'confirm the user really want to delete the record
    81. answer = MsgBox("Are You Sure u want to delete " & txtName.Text & "?", vbYesNo, "Delete")
    82.  
    83. If answer = vbYes Then
    84. Dim myRS As New ADODB.Recordset
    85.     'create a string variable that will hold SQL Statement
    86.     Dim mySQL As String
    87.    
    88.     ' Example: mySQL = "Delete * from address where name='Joe Blow' "
    89.         mySQL = "Delete * from Project_Database where name='" & txtName.Text & "'"
    90.        
    91.         'Execute SQL Command
    92.         myDB.Execute (mySQL)
    93.         'feedback for user
    94.         MsgBox txtName.Text & " was deleted from Database. ", vbOKOnly, "Deleted"
    95.         'Clear the fields
    96.         txtName.Text = ""
    97.         txtRecord.Text = ""
    98.         txtPrice.Text = ""
    99.        
    100.     End If
    101. End Sub
    102. Private Sub lstResults_Click()
    103. ' check if something is selected in the list
    104.     If lstResults.ListIndex <> -1 Then
    105.        
    106.         Dim myRS As New ADODB.Recordset
    107.        
    108.         ' match the selected name to one in the database
    109.         myRS.Open "Select * from Project_Database where name='" & lstResults.List(lstResults.ListIndex) & "'", myDB
    110.        
    111.         ' load the record
    112.         'And compare as well as Display from the database
    113.         txtName.Text = myRS.Fields("DVD_name").Value
    114.         txtRecord.Text = myRS.Fields("Record_ID").Value
    115.         txtPrice.Text = myRS.Fields("price").Value
    116.         'Error would occur if first time to append new record
    117.         On Error Resume Next
    118.         ' save the ID in case we want to update
    119.         currentID = myRS.Fields("ID").Value
    120.         'Close the recordset
    121.         myRS.Close
    122.        
    123.     End If
    124. End Sub
    125. Private Sub txtSearch_Change()
    126. 'search for a record
    127. Dim myRS As New ADODB.Recordset
    128.  
    129.     ' match the first few characters that are typed
    130.     [COLOR=MediumTurquoise]myRS.Open "Select * from Project_Database where name Like '" & txtSearch.Text & "%'", myDB[/COLOR](This is the part that it highlights when the error occurs    
    131.     ' clear old results
    132.     lstResults.Clear
    133.  
    134.     ' place results in the list
    135.     While Not myRS.EOF
    136.         lstResults.AddItem myRS.Fields("DVD_name").Value
    137.         ' go to next record in the result
    138.         myRS.MoveNext
    139.     Wend
    140.     myRS.Close
    141. End Sub
    in the module side
    VB Code:
    1. Option Explicit
    2.  
    3. 'Create DVD Record
    4. Public Type DVD
    5.     Name As String
    6.     Record As String
    7.     Price As String
    8.     End Type
    9.    
    10. 'Total number of records + 1 For new record
    11. Public NumberOfRecords As Integer
    12. 'The bracket () means initialise the database to nothing it can be added later
    13. '(i.e. Dynamic Array)
    14. Public myDatabase() As DVD

    the form lay out:
    [IMG]untitled[/IMG]
    Im using Dynamic array to combine to the database
    Please reply soon...Thanks..
    Last edited by 1g0r; Oct 3rd, 2006 at 04:50 AM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,277

    Re: Help on Run-Time Error '-2147217904(80040e10)' in ADOdatabase

    Welcome to VBForums

    The problem is that you are using a field name you should not be using - Name is a reserved word. If possible, I would strongly recommend changing the field name to something else, as it is likely to cause issues for you in other areas too.

    If you cant change the field name, the way to make it work (but may not work for all situations) is to put square brackets around the field name liek this:
    VB Code:
    1. myRS.Open "Select * from Project_Database where [name] Like '" & txtSearch.Text & ...

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Oct 2006
    Posts
    31

    Re: Help on Run-Time Error '-2147217904(80040e10)' in ADOdatabase

    Thanks for the reply, but I had tried it and it doesn't seem working...I change it to

    myRS.Open "Select * from Project_Database where something Like '" & txtSearch.Text & ...

    Is there any other solutions??...Thanks

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,277

    Re: Help on Run-Time Error '-2147217904(80040e10)' in ADOdatabase

    Have you also changed the field name (in the database) to "something"? If not, you will get an error as the field does not exist.

    I would also recommend adding the appropriate parameters to the Open method, eg:
    VB Code:
    1. myRS.Open "Select * from Project_Database where [i]thefieldname[/i] Like '" & txtSearch.Text & "%'", myDB, adOpenForwardOnly, adLockReadOnly, adCmdText

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Oct 2006
    Posts
    31

    Re: Help on Run-Time Error '-2147217904(80040e10)' in ADOdatabase

    It works....Thanks a lot

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.