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]

@pentahari
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]

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]

@pentahari
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(nesting) 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.Connection

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

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]

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.connection.. 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.

[smartchap]

Dear Kyosuke
I hope it solved your queries. If any other query, ask & I will try to help.
One thing, file vbskfr2.ocx was not in the zip file provided by you and I don't know what is the use of this file in your program.

[Kyosuke18]

hi smartchap,

I read your message thanks for the help.. regarding about vbskfr2.ocx is a vb skinner.. I change the design of the form instead of using a classic form. I just follow ur instructions on the green or asterisk.. I have problems again regarding flexgrid.. since you've entered the

RowClicked = Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.RowSel, 4)

in the MSFlexGrid1_Click event. When I clicked this flexgrid without doing a search the error pop ups that "Subscript out of range".. I try this solution just to avoid the error but when I search it, Example "Because of you", since there are 2 titles but different artist, when I clicked the Because of you by Ne-yo which is in the first row(assuming) it will play.. But when I clicked the Because of you by 98 degrees, only the 1st because of you by Ne-yo is still playing and not from 98 degress.. Here is the code below:

If Rowclicked = "" Then
Msgbox "No selected fields", vbCritical + vbOk, "Flexgrid"
MSFlexgrid1.Enabled = False
ElseIf Rowclicked = Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.RowSel, 4) Then
MSFlexgrid1.Enabled = True
End If

I add another form for adding, deleting and updating a record.. In adding a record, I don't have problems regarding that because it works on a adding.. but I have problem in deleting a record or deleting an entire row.. The code below is deletion of selected row that I added.. Now the problem is when the selected row is deleted in the flexgrid it deletes, but when I open the database name "songs1" in microsoft access the selected row that I deleted didn't.. I've tried using the sql syntax "DELETE * FROM SONGS" in the recordset that I declared and the whole record is deleted.. so I think I should use the WHERE clause next to SONGS, but I can't type the record one by one just to delete, for example "DELETE * FROM SONGS WHERE Title = "xdhsd", Artist = "dhjd", Genre = "Pop", Album = "Popfest", Year = "1954" " what should I do?

Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim ab As String
Dim d As String

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Songsdb.mdb;Persist Security Info=False" '*
rs.Open "Delete * from Songs1", cn, adOpenDynamic, adLockPessimistic

d = MsgBox("Do you want to delete a record?", vbQuestion + vbYesNo, "Delete Record")

If MSFlexGrid1.Rows > MSFlexGrid1.FixedRows + 1 Then
MSFlexGrid1.RemoveItem (MSFlexGrid1.Row)
Else
MSFlexGrid1.Rows = MSFlexGrid1.FixedRows
End If

And again, Thank you very much for the help and some guides that you've gave to me everytime I have problems regarding vb programming.. I really appreciate it.. Attach here with is the zip file regarding the program.. I just modify the codes and add some.. I also try my best to find a solution so that my program will work.. Thanks

Kyosuke18=)

[smartchap]

Dear Kyosuke18
I thought u must have found the results, but today when I checked my subscriptions came to know ur problems. Sorry for the delay.
Regarding ur problems of 'Subscript out of range' and not playing second or other selected record, make changes in Click event of MSFlexGrid as follows:

Private Sub MSFlexGrid1_Click() '*
    'RowClicked = Me.MSFlexGrid1.RowSel'*
    'If RowClicked = 0 Then RowClicked = 1'*
    'MsgBox RowClicked'*
    If Me.MSFlexGrid1.Rows < 2 Then 'i.e.if clicked MSF without any entry in MSF then not reqd error "Subscript out of range".
        MsgBox "Please search some data first."
        Exit Sub
    End If
    RowClicked = Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.RowSel, 4) '*
End Sub
 
 

I am sure ur problems will be solved. Regarding deletion & addition plz give me some time and I will give u solution.

[smartchap]

Because when u click on Grid without doing a search, i.e. when there is no filled row, variable RowClicked remains blank and in ur subroutine it is blank so gives error 'Subscript out of range' and also new selected item (i.e. row) is not entered in RowClicked so the errors.

[smartchap]

I think for deleting also u must use RowClicked variable and delete in both MSFlexGrid as well as Songs.mdb

[smartchap]

Dear Kyosuke
I analysed ur Form4. In delete subroutine Command2_Click u have used
rs.Open "Delete * from Songs1", cn, adOpenDynamic, adLockPessimistic

I think it will delete all records at a time. Second in If...EndIf loop how will it know which record to delete?
Please modify the event. remove
rs.Open "Delete * from Songs1", cn, adOpenDynamic, adLockPessimistic

line and also loop
use a Do..While or for..next loop. Search from first record till last record. As soon as record No is equal to RowClicked (check if +1 is required) delete that record in both MSFlexGrid as well as in Songs1.mdb after confirming from user by displaying details of record to be deleted. I think this will help u.