RE: This Inner join works in MS Access, but not in VB6?
- From: Lamont <Lamont@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 18 Oct 2008 16:29:00 -0700
My thanks to you both, Ralph and Douglas,
About same provider: VB is using Jet 4.0. I suppose that is the same as
Access 2002.
You identified the solution which was to delimit the embedded long integer
variable lngPerID with single quotes, i.e. Perloc.xPerID= '" & lngPerID & '"
....
I am embarrassed to see an error I should have caught. On debugging, I did
a debug.print of the SQL select string and when I clicked on the lngPerID in
the VB6 immediate window, it displayed the current integer value of this
variable.
I misled myself from this to think that the SQL string was OK, and my mind
was closed to the possibility of error.
Now I realize that the appearance of the variable name in the immediate
window stands alone by virtue of the blanks delimiting it. So the VB IDE
will interpret the variable name into a value by clicking. But when SQL sees
it, it is treated as just some characters in the SQL string and not as a
symbol that must be interpreted.
The single quote delimiters cause the SQL parser to recognize the characters
composing the name as a variable to delimited.
Again, thanks for kind assistance.
--
Lamont Phemister
"Lamont" wrote:
Thank you for helping me to solve this mystery..
I have a select statement that works fine in MS Access, but fails in VB6,
with an error message that says "No value given for one or more required
parameters."
I have checked that the select statement names matches the table names until
I
am blue in the face. Is it possible that the "No value given for one or
more required parameters."
the only variable defined in the table is the temporary variable named
lngPerID, and it is clearly
seen in the code to hold the numeric value for the key of a record observed
in the table.
I have two tables, Loc (location) and Per (person) and the rows are in many
to many correspondence
to each other.
These tables are linke by an intersection table where each record has a
pointer to the primary key of
the Loc table, and a pointer to the primary key of the Per table.
Loc PerLoc Per
====== ===== ===
LocID------------| PerLocID |-------------PerID
Adr |--------------xLocID | LastName
City xPerID--------------| FirstName
State
Salutation
Zip
EnvelopeName
Given a the primary key of a Per record (PerID) in a variable named lngPerID
which is obtained by clicking on a row in a Per grid,
my query needs to select all the locations with
which this person is association. Here is the query that works in Access:
SELECT Loc.LocID, Loc.Adr, Loc.City, Loc.State, Loc.Zip
FROM Loc INNER JOIN PerLoc ON Loc.LocID = PerLoc.xLocID
WHERE (((PerLoc.xPerID)=[lngPerID]));
^
|____This value is given.
This query fails in VB6. (Code below)
Private sub DoRSLocSet()
Dim sSQL as string
sSQL = "SELECT Loc.LocID, Loc.Adr, Loc.City, Loc.State,
Loc.LocType, Loc.Zip, Loc.Country" & _
" FROM Loc INNER JOIN PerLoc ON Loc.LocID = PerLoc.xLocID" & _
" WHERE ((PerLoc.xPerID)=lngPerID);"
On Error Resume Next
rsLoc.Close
Err.Clear
On Error GoTo DoRsLocSetErr
With rsLoc
.ActiveConnection = cnnPer
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Properties("IRowsetIdentity") = True 'remove comment to display data
.Open sSQL, , , , adCmdText
End With
Exit Sub
DoRsLocSetErr:
MsgBox "ABORT DoRSLocSetErr = " & Err.Number & " " & Err.Description
Err.Clear
'End
End Sub
--
Lamont Phemister
- Follow-Ups:
- References:
- This Inner join works in MS Access, but not in VB6?
- From: Lamont
- This Inner join works in MS Access, but not in VB6?
- Prev by Date: Re: Populate Sheridan DataGrid from MSSQL2000 using SP's and ADO
- Next by Date: Re: This Inner join works in MS Access, but not in VB6?
- Previous by thread: Re: This Inner join works in MS Access, but not in VB6?
- Next by thread: Re: This Inner join works in MS Access, but not in VB6?
- Index(es):
Relevant Pages
|
