ADO Recordset Error

[pentahari]

I get the following error:
run-time error '-2147217904 (80040e10)'
Method 'open' of object '_Recordset' field
How to solve the problem?
Please Help me. I am waiting for your response.

Dim CONN As New ADODB.Connection
Dim RS As New ADODB.Recordset
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
RS.Open query, CONN, adOpenKeyset, adLockOptimistic

RS.Close
CONN.Open
CONN.Close

[MikeTheBike]

I get the following error:
run-time error '-2147217904 (80040e10)'
Method 'open' of object '_Recordset' field
How to solve the problem?
Please Help me. I am waiting for your response.

Dim CONN As New ADODB.Connection
Dim RS As New ADODB.Recordset
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
RS.Open query, CONN, adOpenKeyset, adLockOptimistic

RS.Close
CONN.Open
CONN.Close

Hi

Perhaps instead of this

RS.Open query, CONN, adOpenKeyset, adLockOptimistic 
  
RS.Close 
CONN.Open

you could try this

CONN.Open 
RS.Open query, CONN, adOpenKeyset, adLockOptimistic 
RS.Close

???

MTB

[yarbrough40]

it also doesn't look like you've declared the query string variable.

dim query as string

or is this a new function that my old old software doesn't have?

[pentahari]

Correction Carry

I Change my coding to yours reply but that error still come.

Dim CONN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim query as String
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
CONN.Open
RS.Open query, CONN, adOpenKeyset, adLockOptimistic
RS.Close
CONN.Close

[yarbrough40]

try:

Dim CONN As New ADODB.Connection 
Dim RS As New ADODB.Recordset 
Dim query as String 
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;" 
query = "select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort" 
CONN.Open
RS.ActiveConnection = CONN
RS.Open (query)

RS.Close 
CONN.Close

[MikeTheBike]

I Change my coding to yours reply but that error still come.

Dim CONN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim query as String
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;"
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by DrugSort"
CONN.Open
RS.Open query, CONN, adOpenKeyset, adLockOptimistic
RS.Close
CONN.Close

Hi
Perhaps you could try

Dim CONN As New ADODB.Connection 
Dim RS As New ADODB.Recordset 
Dim query as String 
CONN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\db.mdb;Jet OLEDB:Database Password=pentahari;" 
query="select *,IIf([AllergyType]=""Drug Allergy"",1,0) as DrugSort from Allergies order by IIf([AllergyType]=""Drug Allergy"",1,0)" 
CONN.Open 
RS.Open query, CONN, adOpenKeyset, adLockOptimistic 
RS.Close 
CONN.Close

??

I have changed the ORDER BY clause, ie it is explicitly calculated.

MTB

[9815402440]

hi
if datatype of allergyType filed is memo then iif condition will generate error.
because iif dont support memo datatypes.
if this is not the case and all of the aforementioned suggestions failed then run the
query in database and look for #Error in the DataSheet view. if found then remove it first.
regards
manpreet singh dhillon hoshiarpur

[Kyosuke18]

Hi everyone,

I have a program about search engine about music.. I like to add music player also but for now i just focus on searching a correct data on a database.. Since I use to study visual basic, I use practice as well. Below is the code wherein I have to search all the data on a database (THIS IS TO TYPE ALL THE TEXTBOXES AND WHEN ONE TEXTBOX IS NOT FILL, THE MESSAGEBOX WILL SHOW "Search not found". These are Title, Artist, Album and Year. I want to search only one or two textboxes and it will show "search found". For example: I type the title on a textbox and when I click the search button it should be "search found" even if i'm not filling the Artist, the Year and Album textbox.. I try to using IF..THEN(nestin g) on the next statement but i'm confused on getting a "search not found" even I use the rs.movenext.. What are the alternative ways to do this?

Private Sub CommandButton1_ Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connectio n

cn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\Docum ents and Settings\Admini strator\My Documents\datab ases\Songs.mdb; Persist Security Info=False"
rs.Open "Select * from Songs1", cn, adOpenForwardOn ly, adLockReadOnly

Dim a As String
Dim b As String
Dim c As String
Dim d As String

a = txtTitle.Text
b = txtArtist.Text
c = txtYear.Text
d = txtAlbum.Text


If a = "" And b = "" And c = "" And d = "" Then
MsgBox "Please fill the textboxes"
Else
Do While Not rs.EOF
If b <> rs!Artist And a <> rs!Title Then
rs.MoveNext
ElseIf c <> rs!Year And d <> rs!Album Then
rs.MoveNext
Else
MsgBox "Search found"
Set rs = Nothing
cn.Close
Exit Sub
End If
Loop
MsgBox "No search found"
Set rs = Nothing
cn.Close
End If

End Sub


I will appreciated for those who'd rply my message..

Very thanks.. =)

[smartchap]

In place of

If b <> rs!Artist And a <> rs!Title Then
rs.MoveNext
ElseIf c <> rs!Year And d <> rs!Album Then
rs.MoveNext
Else

try using:

If b <> rs!Artist And a <> rs!Title And c <> rs!Year And d <> rs!Album Then
rs.MoveNext
Else

[Kyosuke18]

Hello smartchap,

Thanks for the reply and I really appreciated it.. it works and i pass it to flexgrid.. and I try also using a procedure and I seperate the Title, Artist, year and album then calling the procedure.. thanks for the help.. Actually it works in terms of searching a record. my problem is that when I search it, the data will found but when I type another record, it adds to what I've search on the previous one.. I like to search only those records and it will not add another record on the previous one.. I hope u understand.. here is the code on what I did..

Private Sub CommandButton1_Click()

Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection

If txtTitle.Text = "" And txtArtist.Text = "" And txtYear.Text = "" And txtAlbum.Text = "" Then
MsgBox "Please fill the textboxes"
ElseIf Not txtTitle.Text = "" Then
Call TitleSearch
ElseIf Not txtArtist.Text = "" Then
Call ArtistSearch
ElseIf Not txtYear.Text = "" Then
Call YearSearch
ElseIf Not txtAlbum.Text = "" Then
Call AlbumSearch
End If

End Sub

Private Sub Form_Load()
Dim LWidth As Long
Dim i As Integer
Dim j As Integer
Const BarWidth = 320

With MSFlexGrid1

     .Cols = 4
     
     For i = 0 To 3
     MSFlexGrid1.ColAlignment(i) = flexAlignCenterCenter
     Next i

     For j = 0 To 3
     .ColWidth(j) = 1445
     Next j
     
     LWidth = .Width - BarWidth
    .FixedCols = 0
    .Rows = 0

    .AddItem "Title" & vbTab & "Artist" & vbTab & "Album" & vbTab & "Year"
    .Rows = 2
    .FixedRows = 1
    .WordWrap = True
    .RowHeight(0) = .RowHeight(0) * 2
  
End With
End Sub

Public Sub ArtistSearch()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim b As String

b = txtArtist.Text

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
    If b <> rs!Artist Then
    rs.MoveNext
    Else
    MsgBox "Search found"
    MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
    Set rs = Nothing
    cn.Close
    Exit Sub
    End If
    Loop
    MsgBox "No search found"
    Set rs = Nothing
    cn.Close
End Sub

Public Sub TitleSearch()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim a As String

a = txtTitle.Text

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
    If a <> rs!Title Then
    rs.MoveNext
    Else
    MsgBox "Search found"
    MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
    Set rs = Nothing
    cn.Close
    Exit Sub
    End If
    Loop
    MsgBox "No search found"
    Set rs = Nothing
    cn.Close
End Sub

Public Sub YearSearch()

Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim c As String

c = txtYear.Text

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
    If c <> rs!Year Then
    rs.MoveNext
    Else
    MsgBox "Search found"
    MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
    Set rs = Nothing
    cn.Close
    Exit Sub
    End If
    Loop
    MsgBox "No search found"
    Set rs = Nothing
    cn.Close
End Sub

Public Sub AlbumSearch()
Dim rs As New ADODB.Connection
Dim cn As New ADODB.Connection
Dim d As String

d = txtAlbum.Text

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\databases\Songs.mdb;Persist Security Info=False"
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
    If d <> rs!Album Then
    rs.MoveNext
    Else
    MsgBox "Search found"
    MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
    Set rs = Nothing
    cn.Close
    Exit Sub
    End If
    Loop
    MsgBox "No search found"
    Set rs = Nothing
    cn.Close
    
End Sub

I'll be wait for the reply.. thanks a lot.. =)

[smartchap]

OK you do one thing. In the command1_Click event modify code as below:

Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection

Me.MSFlexGrid1.Clear

Also, write code for clearing the textboxes as soon as any one of them is clicked, so that previous data is cleared otherwise it will always search first for Title then for Artist and so on. So if u search for a title and next if you search for an artist, it will again search for previous Title only.
Hope it clears your query.

[Kyosuke18]

Hi smartchap..

Thanks for the rply.. Now I have another problem with my program again which is searching music and since it is ok i guess in searching a correct data... now I have 2 problems.. problem no. 1 in searching a data again, what if i have the same title when I search but different artist and same artist but different titles? for example, I type in titlebox Because of you by Ne-yo and Because of you by 98 Degrees.. Also regarding its year.. what if some song year was the same and genre? there some songs that are 2005 and 2006. Should I change my sql syntax in recordset: "Select * from Songs1 where artist = "" "something like that?.. my problem no.2 is previewing the data with the use of media player.. I add a form for a preview so that it will play.. now i know the components to insert a media player.. I add a path column on a database so that I call it and play.. Now my problem is that, how should I call those mp3s and its path in a database in order to play the song?.. Sorry for asking again a question.. because i'm really new in vb and trying hard to get some concepts in order to understand the logic of my program on what i'm doing.. and in the next program that i will do.. I will again wait for the rply.. The code is above from my previous post but i change the .Cols = 4 into Textmatrix which is the column and row..

Thanks

Kyosuke18

[smartchap]

Dear Kyosuke

For question 1, modify all 4 subroutines as per code given below:

Public Sub ArtistSearch()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim b As String
Dim bFound As Boolean

b = txtArtist.Text
bFound = False

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(full path here)\Songs.mdb;Persist Security Info=False"
rs.Open "Select * from Songs1", cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
    If LCase(b) <> LCase(rs!Artist) Then
        rs.MoveNext
    Else
        'MsgBox "Search found"
        MSFlexGrid1.AddItem rs!Title & vbTab & rs!Artist & vbTab & rs!Album & vbTab & rs!Year
        bFound = True
        rs.MoveNext
    End If
Loop
If bFound = False Then MsgBox "No search found"
Set rs = Nothing
cn.Close
End Sub

For question 2, better u post the code (or project) what u have done so far so that I will try to modify / correct that itself. You can post a zip file. Also next time post ur code using code tags.

[Kyosuke18]

Music Search

Hi Smartchap..

Thanks for the reply.. I test it and it works, thanks for that. Attached herewith is my program that I did so far.. I'm thinking of something to the table in database that I created, do I have to create another table for artist table, album table something like that instead of storing those records into one table? In the 3rd form that I created, I just tested on how to open a music and play it in a media player.. It is not connected on the database yet.. But rather I declared rs as adodb.recordset and cn as adodb.connectio n.. Is just that I don't know what should i go first.. And another question, Is it possible that when I double click the data on the flexgrid when I search it, follows on playing the music? Please check if there are some possible solution.. Sorry if I didn't design yet my program but my aim is to how it works.. Thank you very very much for the help.. I learn some solutions from and I think I should work harder and practice more on this.. Thanks again..

Regards,

Kyosuke18

[smartchap]

Dear Kyosuke18
Have a full working (& corrected) project for WindowsMedia Player. See all the * marked lines. I think u don't require Form3 as MediaPlayer can be played in Form2 itself so that if u need to search next file u can.