Parameters giving me problems...

[Frank123]

What I'm trying to do is something like a master/detail page. One databound listbox opens up another listbox with the corresponding data. I put the following SQL in my recordset properties:
SELECT main.System FROM main, Sites WHERE main.Site_Number = Sites.Site_Number AND (Sites.Site_Name = '?')

I tested the SQL statement by putting in something in place of the ? and it works. So I'm guessing my error is else where. In my source code I have the following:
Sub
Listbox1_onchange()
Name = listbox1.getValue
Recordset2.setParameter 0, name
Recordset2.open
End Sub

When I try displaying the Name variable I get some long sentence so I'm thinking that might be my problem. How would I change the listbox1.getValue values of a bound listbox? Thanks alot.

 

[Frank123]

I also wanted to add that under my recordset properties, nothing is popping up under the parameters tab even though I do have a question mark in my sql statement. Hopefully I can get this resolved. Thanks

 

[Metka]

if you posted your code correctly you probably should correct the following:

Sub Listbox1_onchange()
Name = listbox1.getValue()
Recordset2.setParameter 0, Name
Recordset2.open
End Sub

About the parameter in the recordset not showing, try putting your SQL statement like this:
SELECT main.System FROM main, Sites WHERE main.Site_Number = Sites.Site_Number AND (Sites.Site_Name = ?)

If the parameter is still not showing you can try some name instead of the ?, like:
SELECT main.System FROM main, Sites WHERE main.Site_Number = Sites.Site_Number AND (Sites.Site_Name = par).

if that doesn't work then, i don't know what.

 

[Frank123]

Thanks for the info Metka but I still doesn't work. For some reason my parameters tab is blank even though I put a ? in the sql statement. I think that's where my problem lies. Is this a problem with my software?

 

[Metka]

I'm not sure. since your SQL statement is correct, maybe it really is a software problem. I had problems with parameters not showing only once. the data i was using was in Access 97 database and i thought it was only logical to use Microsoft Jet 3.51 OLE DB Provider when building a connection. but then the parameters didn't show untill i changed the provider to Microsoft Jet 4.0.
what database are you using?

 

[Frank123]

Hi Metka, I too am using Access 97. I'm using the Access 97 driver 3.51 (not sure if that's the same as Jet 3.51?). I heard of Microsoft Jet but never found out what it was for. I'll try looking on the web for Jet 4.0. Thanks alot.

 

[MerlinB]

The suggestions by metka are correct.

The parameter marker (?) should not be in quotes - it is just a string literal at the moment. ODBC/ADO figures out that a parameter is required because the target database responds when the SQL is submitted at design time with this information - but not all databases do this.
Assuming Access or SQL server, then the ? will resolve as a parameter.

And yes
Name = listbox1.getValue
would return a long string - the code of the getValue function in fact! You MUST have parentheses () to get it to work as expected.

Other than that, it looks hunky dory.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)