ADO Help

**Harrild** · Jul 18th, 2000, 01:05 AM

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** · Jul 18th, 2000, 03:32 AM

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** · Jul 18th, 2000, 03:53 AM

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

**Ianpbaker** · Jul 18th, 2000, 04:02 AM

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** · Jul 18th, 2000, 04:13 AM

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

adodc1.refresh <- refresh failed.

**Ianpbaker** · Jul 18th, 2000, 04:20 AM

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** · Jul 18th, 2000, 04:31 AM

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** · Jul 18th, 2000, 04:51 AM

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** · Jul 18th, 2000, 05:04 AM

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

**Ianpbaker** · Jul 18th, 2000, 05:12 AM

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** · Jul 18th, 2000, 05:19 AM

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** · Jul 18th, 2000, 06:40 AM

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** · Jul 19th, 2000, 02:28 AM

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** · Jul 19th, 2000, 03:13 AM

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** · Jul 19th, 2000, 03:44 AM

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** · Jul 19th, 2000, 04:05 AM

No.

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

Code:

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.

Code:

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** · Jul 19th, 2000, 04:14 AM

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** · Jul 19th, 2000, 05:16 AM

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** · Jul 19th, 2000, 05:46 AM

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** · Jul 19th, 2000, 06:12 AM

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

Code:

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** · Jul 19th, 2000, 06:38 AM

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

Code:

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** · Jul 19th, 2000, 06:44 AM

Thanks Stevie.

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

Ian

**Harrild** · Jul 19th, 2000, 07:21 AM

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** · Jul 19th, 2000, 07:24 AM

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

**Ianpbaker** · Jul 19th, 2000, 07:26 AM

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

Ian

**Harrild** · Jul 19th, 2000, 07:28 AM

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** · Jul 19th, 2000, 07:30 AM

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** · Jul 19th, 2000, 07:34 AM

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

Code:

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

**Harrild** · Jul 19th, 2000, 07:39 AM

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** · Jul 19th, 2000, 07:50 AM

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** · Jul 20th, 2000, 11:38 AM

thanks guys the search now works perfectly.

**mellish** · Aug 4th, 2000, 12:51 PM

How do I connect my Oracle database to PowerPoint?

**moosepapi** · Sep 9th, 2001, 09:37 PM

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

Thread: ADO Help

Thread Tools

Display

Connecting Data to the Desktop

Posting Permissions