Compile Error VB6 Code.. HELP

Hi, im working through a guide on using sql and parameters in VB6, im using the BIBLIO.MDB that come with vb6. Ive been orking through the code in the manual but when i enter the follwing bit it comes up with error "complile error expected end of statement", ive gone through everything and it all looks exctly as it should according to the instructions. Id be reallky gratefull if anyone coulf have a quick look and point out any mistakes.

Code:

---

Sub runQuery()
Dim dbf As Database, rectemp As Recordset
Dim qdftemp As QueryDef
Dim strSQL As String
Dim blnfoundquery As Boolean
On Error GoTo queryerror
Set dbftemp = Workspaces(0).OpenDatabase(bibio_path & _
"\biblio.mdb")
For Each qdftemp In dbftemp.QueryDefs
If qdftemp.Name = "publishers titles by author" Then
blnfoundquery = True
Exit For
End If
Next

If blnfoundquery = False Then
strSQL = "PARAMETERS pintPubID Long; " & _
"SELECT Authors.Author, Titles.Title, " & _
"titles.ISBN, " & _
"Titles.[year published], Publishers.name " & _
"From (publishers inner join titles on "
strSQL = strSQL & "publishers.PubID = " _
"Titles.PubID) " & _
"INNER JOIN " & _
"(Authors INNER JOIN [Title Author] ON " & _
"Authors.au_ID = [Title Author].Au.ID) on " & _
"Titles.ISBN = [Title Author].ISBN WHERE " & _
"publishers.pubID " & _
"= pintPubID ORDER by Authors.Author;"
Set qdbftemp = dbftemp.CreateQueryDef( _
"publisher's titles by " & _
"author", strSQL)
Else
Set qdftemp = dbftemp.QueryDefs( _
"publisher's titles by author")
End If
qdftemp.Parameters![pintpubid] = selectedpubid
Set dtadata.Recordset = gdftemp.OpenRecordset()
If dtadata.Recordset.RecordCount > 0 Then
frmmain.Caption = "chapter 5.7 example - " & _
Str$(dtadata.Recordset.RecordCount) & _
IIf(dtadata.Recordset.RecordCount = 1, _
" Title", " Titles")
Else
    frmmain.Caption = frmmain.Caption = "chapter 5.7 _ example - no titles"
End If
On Error GoTo 0
Exit Sub
queryerror:
MsgBox Err.Description, vbExclamation
Exit Sub
End Sub

---

The erro comes up at lines

Code:

---

strSQL = strSQL & "publishers.PubID = " _
"Titles.PubID) " & _
"INNER JOIN " & _
"(Authors INNER JOIN [Title Author] ON " & _
"Authors.au_ID = [Title Author].Au.ID) on " & _
"Titles.ISBN = [Title Author].ISBN WHERE " & _
"publishers.pubID " & _
"= pintPubID ORDER by Authors.Author;"

---

Thanks for your time

I think it is just a missing line continuation character...

VB Code:

strSQL = strSQL & "publishers.PubID = " [B][COLOR=Red]&[/COLOR][/B] _
"Titles.PubID) " & _
"INNER JOIN " & _
"(Authors INNER JOIN [Title Author] ON " & _
"Authors.au_ID = [Title Author].Au.ID) on " & _
"Titles.ISBN = [Title Author].ISBN WHERE " & _
"publishers.pubID " & _
"= pintPubID ORDER by Authors.Author;"

quality, thank you, ive got another problem, iv got a second form which ive used the code below as the guide insturucted but whe n i try to run it it say "compile error, variable not definedW, ive highlighted the line the error coems up at. looking at it the instructions said to only use the full option explicit staement on the frmmain on the frmselectpublisher its says to simply use option explicit, could this be wher the problem is??

Code:

---

Option Explicit

Private Sub Form_Load()
Dim dbftemp As Database, rectemp As Recordset
Dim strSQL As String
On Error GoTo loaderror

Set dbftemp = Workspaces(0).OpenDatabase(BIBLIO_PATH)
strSQL = "SELECT [PUBID], [company name] from " & _
"[publishers]"
Set rectemp = dbftemp.OpenRecordset(strSQL)
If rectemp.RecordCount Then
rectemp.MoveFirst
Do Until rectemp.EOF
If Not IsNull(rectemp![company name]) Then
LstPublishers.AddItem rectemp![company name]
Else
LstPublishers.AddItem ""
End If
LstPublishers.ItemData(LstPublishers.NewIndex) = _
rectemp![PubID]
rectemp.MoveNext
Loop
Else
MsgBox "there are no publishers in the database.", _
vbCritical
End
End If
On Error GoTo 0
Exit Sub
loaderror:
MsgBox Err.Description, vbCritical
End
End Sub

---

there line concerned is:

Code:

---

Set dbftemp = Workspaces(0).OpenDatabase(BIBLIO_PATH)

---

thanks again for your time,

Yes, with Option Explicit turned on (as should always be), your variable dbfTemp is only valid in the Form Load event of whatever the form is. If you need this variable across forms, place its declaration in a module.

ive just tried the code as shown below but i get an eror saying the db is already open,

Code:

---

Option Explicit
Const BIBLIO_PATH = "C:\program files\microsoft visual studio\vb98"
Public selectedpubid As Integer
Private Sub Form_Load()
Dim dbftemp As Database, rectemp As Recordset
Dim strSQL As String
On Error GoTo loaderror

Set dbftemp = Workspaces(0).OpenDatabase(BIBLIO_PATH)
strSQL = "SELECT [PUBID], [company name] from " & _
"[publishers]"
Set rectemp = dbftemp.OpenRecordset(strSQL)
If rectemp.RecordCount Then
rectemp.MoveFirst
Do Until rectemp.EOF
If Not IsNull(rectemp![company name]) Then
LstPublishers.AddItem rectemp![company name]
Else
LstPublishers.AddItem ""
End If
LstPublishers.ItemData(LstPublishers.NewIndex) = _
rectemp![PubID]
rectemp.MoveNext
Loop
Else
MsgBox "there are no publishers in the database.", _
vbCritical
End
End If
On Error GoTo 0
Exit Sub
loaderror:
MsgBox Err.Description, vbCritical
End

---

That is probably because before your code closed it, it blew up on the variable not being defined, so there was no graceful closure.

If you can find the database running in Task Manager, end it, and then run your code again.

Thing is im working from a tutorial guide which is obviously wrong, ive followed the instructions exactly but they dont work, is there any chance i could post the 2 forms and someone could point me to what needs to be changed as i really am stumped?

Quote:

---

Originally Posted by ricodstar

Thing is im working from a tutorial guide which is obviously wrong, ive followed the instructions exactly but they dont work, is there any chance i could post the 2 forms and someone could point me to what needs to be changed as i really am stumped?

---

Sure, go ahead (and not that it will make any difference, but yours is not the first tutorial guide to send someone just starting out down the garden path of confusion.)

Ok, in the attached folder is the forms as creted follwing the instructions,
cheers
sean

Before I get too carried away and rewrite the whole thing, is the purpose of this little project just an excerise in database connectivity? I'm afraid if I rewrite everything then you will be lost for your next chapter.

PS: One thing I would do now, regardless of what the books says, is to add a module to your project (that was my first step). Take your database and recordset objects, move them to the module, and make them public. Also, the Const for BIBLIO_MDB should be moved to the module and made public.

Im using it just to get an idea of the way to do queries from vb, theer isnt another chapter after this so any changes you can make would be greatly appriciated, then i can look it over and get to grips with it from there, its a bit of a nightmare trying to learn from code thats faulty.

Don't give up the ship.

I haven't worked with data controls or DAO in quite a few years, so I'm having to look up stuff I used to know by heart. I'd like to keep this as close to your book's example as possible.

thankyou so much for doing this, your an absolute lifesaver :)

I had an MDAC issue combined with using DAO against what turned out to be an Access 97 database, but I think I got those corrected. Give this a shot a see if it works for you.

Also, I would strongly suggest moving from DAO, regardless of what the books you have are telling you, to ADO. In the 'real' world, that is what you are going to be using in all likelyhood.

If this doesn't work on your machine, let me know.

Hi, ive just tried it and the frmselect publishers comes up fine but when i select a publisher then the ok it comes up withy error "run time 3061, to few parameters expected 1"???

sorry shiuld have added the line the error comes up on

Code:

---

  Set rectemp = dbftemp.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)

---

Ok. I stuipdly upgraded my copy of biblio to 2000 (which is when I ran into the MDAC problems using DAO. Since you are still using an Access 97 copy, then you probably don't need to make is a passthrough query. Play around with that line, and see if you can get it to work. I no longer have an Access 97 copy of biblio...

im using access 2002-2003, the file biblio is in 97 format but i can convert it to both 200 and 2003, ive just tried both and it says "unrecognized database format" for both

Quote:

---

Originally Posted by ricodstar

im using access 2002-2003, the file biblio is in 97 format but i can convert it to both 200 and 2003, ive just tried both and it says "unrecognized database format" for both

---

Don't convert it....leave it as a 97 format. Converting it is what messed me up.

ok i will do, would learning to use ado be a whole lot better for me, im really new to vb and although i was pritty good at access i dont know wheer to start with vb and its quite frustrating, you said " Play around with that line, and see if you can get it to work. I no longer have an Access 97 copy of biblio", im sorry to seem dumb but by play around what do you mean??
sorry

Quote:

---

Originally Posted by ricodstar

ok i will do, would learning to use ado be a whole lot better for me, im really new to vb and although i was pritty good at access i dont know wheer to start with vb and its quite frustrating, you said " Play around with that line, and see if you can get it to work. I no longer have an Access 97 copy of biblio", im sorry to seem dumb but by play around what do you mean??
sorry

---

A: You are NOT dumb and no one thinks you are.
B: Yes, it would be much better to learn ADO.
C: I meant play around with

Code:

---

Set rectemp = dbftemp.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)

---

Only the first parameter is required. The other two are optional, so see what happens if you remove one or both.

There are some pretty decent ADO Tutorials out there, and a lot of ADO examples on this site.

Here's a couple of ADO tuts I've written. Ignore the fact that it still says SQL Server in the description. It's really all Access & ADO.

http://www.developerkb.com/modules/w...hp?category=23

And there are plenty more out there too.

Tg

thankyou both loads, im gonna give those tuts you offered a go, see if i can get somewhere 2moro, I tried variations on that line in the code and got no where so maybe ados best, think im gonna take a break tho...starting to pull my hair out!!!!

Change dbOpenSnapshot to dbOpenDynaset and see if that helps.

no still says to few parameters, ive tried as many combinatiosn as poss.. no worries 2moro im gonna work through the tut recommended so hopefully ill get somewhere with that... thanks for all your help

if you do think of anything, let me know and ill give it a try :rolleyes:

It's a typo in runQuery():

VB Code:

strSQL = "SELECT Authors.Author, Titles.Title, "
   strSQL = strSQL & "titles.ISBN, "
    strSQL = strSQL & "Titles.[year published], Publishers.name "
   strSQL = strSQL & "From (publishers inner join titles on "
    strSQL = strSQL & "publishers.PubID = "
   strSQL = strSQL & "Titles.PubID) "
   strSQL = strSQL & "INNER JOIN "
   strSQL = strSQL & "(Authors INNER JOIN [Title Author] ON "
   strSQL = strSQL & "Authors.au_ID = [Title Author].Au[COLOR=Red]_[/COLOR]ID) on "     '<---- ON THIS LINE
   strSQL = strSQL & "Titles.ISBN = [Title Author].ISBN WHERE "
   strSQL = strSQL & "publishers.pubID = " & selectedpubid
   strSQL = strSQL & " ORDER by Authors.Author"