ADO Help

[Harrild]

ok hello
this is my code for a search in a prog i am creating but i keep getting a syntax error with the FROM clause..
help


private sub cmdsearch_Click()
Dim search As String
Dim band As String
Dim album As String
Dim genre As String
Dim cdtype As String
Dim released As String
Dim company As String
Dim serial As String

On Error GoTo damn

band = Trim(txtband.Text)
album = Trim(txtalbum.Text)
genre = Trim(txtgenre.Text)
cdtype = Trim(txttype.Text)
released = Trim(txtreleased.Text)
company = Trim(txtcompany.Text)
serial = Trim(txtserial.Text)

search = "select * from cd_serial_numbers"
'search = search & "from cd_serial_numbers"

If Len(band) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (band_name ='" & txtband.Text & "')"

End If

If Len(album) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (album_name ='" & txtalbum.Text & "')"

End If

If Len(genre) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (genre ='" & txtgenre.Text & "')"

End If

If Len(cdtype) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (single/album ='" & txttype.Text & "')"

End If

If Len(released) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (released ='" & txtreleased.Text & "')"

End If
If Len(company) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & " where (company ='" & txtcompany.Text & "')"

End If

If Len(serial) Then
'If blnsearch Then
' search = search & "or"
'Else
' search = search & "where"
' blnsearch = True
'End If
search = search & "where (serial ='" & txtserial.Text & "')"

End If


Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh

damn:
MsgBox ("Something has gone wrong (duh)!")

End Sub

[Ianpbaker]

Hi harry

From the look of your code, it looks like you need a few spaces and some syntax changes try this.

have a boolean variable called blnFirstWhere. Set it to true when the function is called and when you have the if statement for the length do the following for each of them
like you have commented out

search = "select * from cd_serial_numbers"

If Len(band) Then
If blnFirstWhere Then
blnFirstWhere = False
search = search & " WHERE"
else
search = search & " OR"
End IF
End If

search = search & " (band_name ='" & txtband.Text & "')"

If you do this all the way down, it should sort out your problem. Be careful of the various spaces that are needed when you are building the string.

Hope this helps

Ian

[Harrild]

yeah its alright but i get a syntax error in the 'FROM' clause. Oh do i have to declare the boolean variable?

[Ianpbaker]

You only need to declare if you have option explicit at the top.

As I said I think your problem is your spaces.

You have

search = "select * from cd_serial_numbers"

Then

search = search & "where (band_name ='" & txtband.Text & "')"

Try rplacing it with

search = search & " where (band_name ='" & txtband.Text & "')"

should now work

Ian

[Harrild]

now it says Microsoft Jet database engine connot find the table. run time error '2147217865(80040e37)

adodc1.refresh <- refresh failed.

[Ianpbaker]

Could you do a debug.print search just before you set the recordset and post it here and I will have a good look for you at the complete syntax.

Ian

[Harrild]

ok the first error message that comes up is

The Microsoft Jet database engine cannot find the input tabel or query 'cd_serial_numbers_'. Make sure it exists and that its nameis spelled correctly.

The second one that comes up is

Run-time error '-2147217865 (80040e37)':

Method 'Refresh' of object 'Iadodc' failed.

My code is


Dim search As String
Dim band As String

' band = Trim(txtband.Text) <- do i need this?

search = "select * from cd_serial_numbers"

If Len(band) Then
If blnFirstWhere Then
blnFirstWhere = False
search = search & " WHERE"
Else
search = search & " OR"
End If
End If

search = search & " where (band_name = ' " & txtband.Text & " ' ) "

Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh


What is wrong with it?

[Ianpbaker]

I cant see any thing wrong with that aprt from when the first error message comes up, you wrote can't find 'cd_serial_numbers_ with an extra _ at the end. is that just a typo?.

Ian

[Harrild]

Yeah thats just a typo....
so do i need
band = Trim(txtband.Text) <-????

[Ianpbaker]

That won't affect anything for the error message you are getting. Dont worry about the second error because that's just because the first one is popping up.
Now that you are doing the WHERE and OR routein, wou can get rid of the where in

search = search & " where (band_name = ' " & txtband.Text & " ' ) "

That's a problem

The only other thing I can think of is that your not connecting to your data control properly. If it isn't that and you definatley have a table called cd_serial_numbers (Which I'm sure you have) Then i'm totally stumped.

Ian

[Harrild]

i just got a different error msg. it said Syntax error in FROM clause and then i got the runtime error as before.

my code this time is

Dim search As String
Dim band As String

band = Trim(txtband.Text)

search = "select cd_serial_numbers.band_name, cd_serial_numbers.album_name, cd_serial_numbers.genre, cd_serial_numbers.single/album, cd_serial_numbers.released, cd_serial_numbers.company, cd_serial_numbers.serial from cd_serial_numbers"

If Len(band) Then
If blnFirstWhere Then
blnFirstWhere = False
search = search & " WHERE "
Else
search = search & " OR "
End If
End If

search = search & " (band_name = '" & band & "')"

Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh

When the runtime error comes up and i go debug it highlights the last line of code
adodc1.refresh i thinking i might have to use a set or let command before this line but i'm not sure.

[Ianpbaker]

Just found some thing new. You haven't initalized blnFirstWhere to true when you start the procedure, so you won't ever get the WHERE to come up.

This could sort out all your problems (Maybee, just Maybee)


Ian

[Edited by Ianpbaker on 07-18-2000 at 11:42 AM]

[Harrild]

This is my code for the search now.
The problem is when i only use 1 of the 'IF' commands it works fine but when i use all of em. i think i should change the the search string after the first 'IF' so it says something like this;

txtband.Text & "')"
If Len(album) Then
blntest = True
search = search & " AND"
End If

search = search & " (CDSERIALNUMBERS.[Album Name] = '" & txtalbum.Text & "')"

would this work??

the current code is below....

Dim search As String
Dim band As String
Dim blntest As Boolean
Dim album As String
Dim genre As String
Dim cdtype As String
Dim released As String
Dim company As String
Dim serial As String

On Error GoTo damn

band = Trim(txtband.Text)
album = Trim(txtalbum.Text)
genre = Trim(txtgenre.Text)
cdtype = Trim(txttype.Text)
released = Trim(txtreleased.Text)
company = Trim(txtcompany.Text)
serial = Trim(txtserial.Text)


search = "SELECT "
search = search & "CDSERIALNUMBERS.[Band Name], CDSERIALNUMBERS.[Album Name], "
search = search & "CDSERIALNUMBERS.Genre, CDSERIALNUMBERS.[SINGLE/ALBUM], "
search = search & "CDSERIALNUMBERS.RELEASED, CDSERIALNUMBERS.COMPANY, CDSERIALNUMBERS.SERIAL"
search = search & " FROM CDSERIALNUMBERS"

If Len(band) Then 'if length >0
blntest = True
search = search & " WHERE"
End If

search = search & " (CDSERIALNUMBERS.[Band Name] = '" & txtband.Text & "')"
If Len(album) Then
blntest = True
search = search & " WHERE"
End If

search = search & " (CDSERIALNUMBERS.[Album Name] = '" & txtalbum.Text & "')"
If Len(genre) Then
blntest = True
search = search & " WHERE"
End If

search = search & " (CDSERIALNUMBERS.Genre = '" & txtgenre.Text & "')"
If Len(cdtype) Then
blntest = True
search = search & " WHERE"
End If

search = search & " (CDSERIALNUMBERS.[SINGLE/ALBUM] = '" & txttype.Text & "')"
If Len(released) Then
blntest = True
search = search & " WHERE"
End If

search = search & " (CDSERIALNUMBERS.RELEASED = '" & txtreleased.Text & "')"

If Len(company) Then
blntest = True
search = search & " WHERE"
End If

search = search & " (CDSERIALNUMBERS.COMPANY = '" & txtcompany.Text & "')"

If Len(serial) Then
blntest = True
search = search & " WHERE"
End If

search = search & " (CDSERIALNUMBERS.SERIAL = '" & txtserial.Text & "')"

search = search & ";"

Debug.Print search

Adodc1.RecordSource = search

Adodc1.Refresh


damn:
MsgBox ("Something has gone wrong (duh)!")

[Ianpbaker]

Hi harry

Changing the Or to an AND would have the following affect.

If you was using The OR in between each part of the cirteria it would return any records that match any part of The WHERE statement, wheras if you was using AND between each part it would only return rows that match every part of the WHERE statement.

Hope this helps

Ian

[Harrild]

so i go

if Len(band) > 0 Then
blntest = True
search = search & " WHERE"
elseif len(band) = 0
blntest = false
search = search & " OR"

End If
____________________
and for the next one;
____________________

if len(album) >0 then
blntest=true
search = search & " AND"
elseif len(album) = 0
blntest = false
search = search & " WHERE"
end if

would this work?

[Ianpbaker]

No.

If you want it so that it only returns records that match all criteria, you need the finished result of


SELECT field1, field2 .. FROM CDSERIALNUMBERS WHERE field1 = 'Blah' AND field2 = 'Blah' AND field3 = 'Blah' etc


So For your If statements, you would need the following for all of them.

Set blntest to true at the top of your sub (this means that there hasn't been a first where statement.

if Len(band) > 0 Then
If blntest = True Then 'First Part of WHERE statement
blntest = False
search = search & " WHERE"
else 'Adding another Criteria to the WHERE statement
search = search & " AND"
End If
End IF
search = search & " (CDSERIALNUMBERS.[Band Name] = '" & txtband.Text & "')"

This will go through, check each of you r different textboxes, if it find's some text, it will see if it is the time some text is found (blnTest), if it is put in a WHERE, otherwise, put in an AND.

Ian

[Harrild]

If Len(band) > 0 Then 'if length >0
blntest = True
search = search & " WHERE"
ElseIf Len(band) = 0 Then
blntest = False
'search = search
End If

search = search & " (CDSERIALNUMBERS.[Band Name] = '" & txtband.Text & "')"
'search the album text box
If blntest = True Then
If Len(album) > 0 Then
'if the length is >0 and the band box has a length then search for band AND album.
blntest = True
search = search & " AND"
ElseIf Len(album) = 0 Then
'if the length is =0 and the band box has a length then search just for band box
blentest = False
search = search
End If
search = search & " (CDSERIALNUMBERS.[Album Name] = '" & txtalbum.Text & "')"
ElseIf blntest = False Then
'if band box empty and album box has length then search for album NOT band.
If Len(album) > 0 Then
blntest = True
search = search & " WHERE"
search = search & " (CDSERIALNUMBERS.[Album Name] = '" & txtalbum.Text & "')"
ElseIf Len(album) = 0 Then
'if band box and album search is empty go onto next input box.
blntest = False


End If

do you see what i am trying to do?

[Ianpbaker]

Right, lets go back to basics, no code as I am getting confused here.

You have table called CDSERIALNUMBERS with the following columns band_name, album_name, genre, single/album, released, company And serial.

You need a form to Search this Table by any column and any number of multiple columns. Eg. Search by band_name and genre,search by album_name and company and serial ... etc.

Is this what you need or have I lost the Plot so completly that my brain has given up and gone down the pub.

Ian

[Harrild]

yeah i have a table called CDSERIALNUMBERS and i want to be able to search any field or fields at the same time under one button

[Ianpbaker]

In that case use the following code below and it will do what you ask of. (You might need to change the name of some of the columns


Private Sub cmdsearch_Click()
Dim search As String
Dim band As String
Dim album As String
Dim genre As String
Dim cdtype As String
Dim released As String
Dim company As String
Dim serial As String
Dim blnFound As Boolean


blnFound = False
On Error GoTo damn

band = Trim(txtband.Text)
album = Trim(txtalbum.Text)
genre = Trim(txtgenre.Text)
cdtype = Trim(txttype.Text)
released = Trim(txtreleased.Text)
company = Trim(txtcompany.Text)
serial = Trim(txtserial.Text)

search = "select * from cd_serial_numbers" 'Select every column from cd_serial_numbers


If Len(band) > 0 Then 'Check to see if the band has been entered
If blnFound = False Then 'Check to see if this is the first record to be used
blnFound = True ' it is so put in a WHERE
search = search & " WHERE"
Else
search = search & " AND" 'Already had a search criteria so put in an AND
End If

search = search & " (band_name ='" & txtband.Text & "')"
End If

If Len(album) > 0 Then 'Check to see if the Albumn has been entered
If blnFound = False Then 'Check to see if this is the first record to be used
blnFound = True ' it is so put in a WHERE
search = search & " WHERE"
Else
search = search & " AND" 'Already had a search criteria so put in an AND
End If
search = search & " (album_name ='" & txtalbum.Text & "')"
End If

If Len(genre) > 0 Then 'Check to see if the Genre has been entered
If blnFound = False Then 'Check to see if this is the first record to be used
blnFound = True ' it is so put in a WHERE
search = search & " WHERE"
Else
search = search & " AND" 'Already had a search criteria so put in an AND
End If
search = search & " (genre ='" & txtgenre.Text & "')"
End If

If Len(cdtype) Then 'Check to see if the CD type has been entered
If blnFound = False Then 'Check to see if this is the first record to be used
blnFound = True ' it is so put in a WHERE
search = search & " WHERE"
Else
search = search & " AND" 'Already had a search criteria so put in an AND
End If
search = search & " (single/album ='" & txttype.Text & "')"
End If

If Len(released) Then 'Check to see if the Released has been entered
If blnFound = False Then 'Check to see if this is the first record to be used
blnFound = True ' it is so put in a WHERE
search = search & " WHERE"
Else
search = search & " AND" 'Already had a search criteria so put in an AND
End If
search = search & " (released ='" & txtreleased.Text & "')"
End If
If Len(company) Then 'Check to see if the Company has been entered
If blnFound = False Then 'Check to see if this is the first record to be used
blnFound = True ' it is so put in a WHERE
search = search & " WHERE"
Else
search = search & " AND" 'Already had a search criteria so put in an AND
End If
search = search & " (company ='" & txtcompany.Text & "')"
End If

If Len(serial) Then 'Check to see if the Serial has been entered
If blnFound = False Then 'Check to see if this is the first record to be used
blnFound = True ' it is so put in a WHERE
search = search & " WHERE"
Else
search = search & " AND" 'Already had a search criteria so put in an AND
End If
search = search & " (serial ='" & txtserial.Text & "')"
End If


Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh

damn:
MsgBox ("Something has gone wrong (duh)!")

End Sub


Hope this solves it

Ian

[Stevie]

Just thought I'd post this as it saves all the extra Ifs and Elses. :)


Private Sub cmdsearch_Click()
On Error GoTo damn

Dim search As String
Dim band As String
Dim album As String
Dim genre As String
Dim cdtype As String
Dim released As String
Dim company As String
Dim serial As String
Dim blnFound As Boolean

blnFound = False

band = Trim(txtband.Text)
album = Trim(txtalbum.Text)
genre = Trim(txtgenre.Text)
cdtype = Trim(txttype.Text)
released = Trim(txtreleased.Text)
company = Trim(txtcompany.Text)
serial = Trim(txtserial.Text)

'Select every column from cd_serial_numbers
search = "select * from cd_serial_numbers"

'Check to see if the band has been entered
If Len(band) > 0 Then
AddWhereOrAnd blnFound, search
search = search & " (band_name ='" & txtband.Text & "')"
End If

'Check to see if the Albumn has been entered
If Len(album) > 0 Then
AddWhereOrAnd blnFound, search
search = search & " (album_name ='" & txtalbum.Text & "')"
End If

'Check to see if the Genre has been entered
If Len(genre) > 0 Then
AddWhereOrAnd blnFound, search
search = search & " (genre ='" & txtgenre.Text & "')"
End If

'Check to see if the CD type has been entered
If Len(cdtype) Then
AddWhereOrAnd blnFound, search
search = search & " (single/album ='" & txttype.Text & "')"
End If

'Check to see if the Released has been entered
If Len(released) Then
AddWhereOrAnd blnFound, search
search = search & " (released ='" & txtreleased.Text & "')"
End If

'Check to see if the Company has been entered
If Len(company) Then
AddWhereOrAnd blnFound, search
search = search & " (company ='" & txtcompany.Text & "')"
End If

'Check to see if the Serial has been entered
If Len(serial) Then
AddWhereOrAnd blnFound, search
search = search & " (serial ='" & txtserial.Text & "')"
End If


Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh

damn:
MsgBox ("Something has gone wrong (duh)!")

End Sub

Private Sub AddWhereOrAnd(blnFound As Boolean, search As String)

'Check to see if this is the first record to be used
If not blnFound Then

' it is so put in a WHERE
blnFound = True
search = search & " WHERE"

Else

'Already had a search criteria so put in an AND
search = search & " AND"

End If

End Sub

[Ianpbaker]

Thanks Stevie.

I did think about putting it in but nether got round to it

Ian

[Harrild]

i used your solution Ianpbaker (cos it was here first) and it worked but why when i want to search for a field and the released field i do not get any results, just the field names and thats it.

[Stevie]

Is there any data in the database which matches your search criteria?

[Ianpbaker]

Ok, Is released a date field and if it is what Database are you using?

Ian

[Harrild]

Yes there is data that matches the search criteria. Would it have something to do with the data type of the textbox and the datatype of the field in access?

[Ianpbaker]

Yes, if released is a date in access you need to enclose your date in the search with #'s

search = search & " (released =#" & txtreleased.Text & "#)"


Ian

[Stevie]

Could do.

What data type is it in Access. Sounds to me (by the name of the field) as if it would be Yes/No or Date.

If date then


search = search & "(released = #" & Format(txtreleased.Text, "mm/dd/yyyy") & "#)"

[Harrild]

nope the textbox format is just text and so is the field data type in access but all that is contained in the field is numbers. It should be simply case of text match using SQL but it doesnt like numbers in text data type for some reason.

[Ianpbaker]

Ok then, question time (Ignore if you already know and I appolagise because I do not Know what level you are at)

The Code I wrote earlier, returns records that match all the search criteria)

So If you put in the search box Blur as the band and EMI as the company it will try to return all the ablumns that are by Blur and are distributed by EMI not all albums by blur and all records by EMI. I take it you want the Former option.

Ian

[Harrild]

thanks guys the search now works perfectly.

[mellish]

How do I connect my Oracle database to PowerPoint?

[moosepapi]

Hello.
Could anybody help me. I am populating MSFlexgrid from ADO recordset every 45 sec. I have ActiveX DLL in which I open and fill recordset. My question is:
I need to populate Grid from the recordset only with the rows that don't exist in the Grid
Thanks for any help