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
-
20 3776 @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
it also doesn't look like you've declared the query string variable.
or is this a new function that my old old software doesn't have?
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
-
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
-
@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
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
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.. =)
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
-
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.. =)
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.
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
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.
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
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.
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.
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=)
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.
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.
I think for deleting also u must use RowClicked variable and delete in both MSFlexGrid as well as Songs.mdb
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: David Berry |
last post by:
I'm trying to use a recordset inside of a function but I'm getting an "object required 'adoRS" error.
At the top of the page I create my recordset, ex:
dim strConnection, adoCN, adoRS, strSQL...
|
by: dmiller23462 |
last post by:
My brain is nuked....Can anybody tell me right off the bat what is
wrong with this code? Along with any glaring errors, please let me
know the syntax to display a message (Response.Write would be...
|
by: Alan Silver |
last post by:
Hello,
I have an ASP that takes a connection string and SQL statement in the
querystring and is supposed to return the XML representation of the
recordset to the Response stream (don't worry,...
|
by: Simone |
last post by:
Hello
I hope you guys can help me. I am very new to ADO...
I am creating a ADODB connection in a module and trying to access it
from a command button in a form.
Function fxEIDAssgn(plngEID As...
|
by: Gerry Abbott |
last post by:
Hi all.
I wish to call a recordset from a function.
Ive tried the following approach,
--------------------------------------------------------
Function PassRS() As Recordset
Dim db As...
|
by: ano1optimist |
last post by:
I have a form with a search button. I'm using command parameters to
pass search criteria to a stored procedure. Here is my code:
Stored procedure:
CREATE PROCEDURE .
@strCriteria varchar(200)...
|
by: Nathan Bloomfield |
last post by:
Hi there,
I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.
Basically, it works as follows:
- adds records...
|
by: marmottedodue |
last post by:
Hello,
I'm trying to debug an access project in which two kind of recordset
are used: ADODB.recordset and DAO.recordset. I'm trying to set the
whole project on DAO.recordset, but the following...
|
by: technocraze |
last post by:
Hi guys,
I have encountered this error when updating the values to the MS Acess table.
Error : Update on linked table failed. ODBC sql server error Timeout expired. MS Acess is my front end and...
|
by: wallconor |
last post by:
Hi,
I am having a problem using Dreamweaver CS3 standard recordset paging behavior. It doesn’t seem to work when I pass parameter values from a FORM on my search page, to the recordset on my...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |