RE: VB6.0 / ADO / Parameterized Query / SQLServer 2000 -- help...

Ok --- did one more test after I posted this thread and found something
interesting:

If I add another string value to the listbox, it will include the first 2
values in the list and assign these to the @spec_type parameter!

The problem with that is that the last value in the list is exluded.

Suggestions on how to include that last value in the listbox?


Thanks,

j

"jamesfreddyc" wrote:

1. Original problem: Create and send a parameter (@spec_type) of string
values that come from a listbox loaded with user-defined strings (species
types). Include each of these into a larger SProc's WHERE clause.

2. Aaron over on the sqlserver.programming forum was a HUGE help in getting
the SProc setup for me. Basically, a #temp table is built and populated with
the @spec_type parameter values (from the listbox). The rest of the SProc
performs a query with other parameters (not a "list" of strings, but straight
forward things, like a date1=@date1 for ex.).

3. In QueryAnalyzer, I get the results perfectly! So I know the SProc is
working perfectly when the parameters are hardcoded into the SProc and
running an EXEC command on the SProc verifies that all is working correctly.

4. Moving back to the VB environment, I get different results when
populating a DataGrid with the ADOrs. When run from the VBApp, the DataGrid
gets populated with records but they are incorrect ---- only the first value
in the listbox is showing up.

Here are some test results I have done:

QueryAnalyzer: Results in correct rows
EXEC dbo.AO_RT_Analysis_TotCollByMonth NULL, 'Ma dyari,Cx nigripalpus,';
GO

VBApp: Results in correct list ('Ma dyari,Cx nigripalpus,') which is EXACTLY
how it looks in the QueryAnalyzer EXEC statement above!

'This creates a list of species from the lstboxSpeciesListR selection
xCriteria_input = ""
For xCnt = 0 To frm_RT_Analysis.lstboxSpeciesListR.ListCount - 1
If Not frm_RT_Analysis.lstboxSpeciesListR.Selected(xCnt) Then
If Len(xCriteria_input) > 0 Then xCriteria_input =
RTrim(xCriteria_input) & ","
xCriteria_input = xCriteria_input &
frm_RT_Analysis.lstboxSpeciesListR.List(xCnt)
xCriteria = "'" & RTrim(xCriteria_input) & "," & "'"
End If
Next


Then this list of "species" is set to the xCriteria variable, which the
@spec_type parameter's value is set to this xCriteria list.

Any help is appreciated. Any comments/tips on setting up parameters made up
of listbox values would be a big help too....

Thanks!

Take Care,

james
.

Relevant Pages

  • Re: Array oder Listbox?
    ... > zwischen Strings berechnen soll und mir als Beispiel ... > einen Vergleich zwischen Ortsnamen in einer Datenbank ... Z.B. weil man ja einer Listbox ... Du wirst mit einem Array immer besser fahren als mit einer ListBox, ...
    (microsoft.public.de.vb)
  • Re: Intercept messages of an app.
    ... owner-draw listbox it will also require reverse-engineering the ItemData field. ... Is there a way for a separate application to log strings ... Look at MSDN to see what type of hook ...
    (microsoft.public.vc.mfc)
  • Re: Array oder Listbox?
    ... > zwischen Strings berechnen soll und mir als Beispiel ... > einen Vergleich zwischen Ortsnamen in einer Datenbank ... > schon doppelte entfernt, und 16.000 der Telekom. ... Z.B. weil man ja einer Listbox ...
    (microsoft.public.de.vb)
  • RE: VB6.0 / ADO / Parameterized Query / SQLServer2000
    ... only the top 2 values in the ListBox are being added to the ... The rest of the SProc ... performs a query with other parameters (not a "list" of strings, ... 'This creates a list of species from the lstboxSpeciesListR selection ...
    (microsoft.public.vb.database.ado)
  • Re: get text from listbox
    ... The listbox has 7 columns, 1 for an icon and 6 with strings. ... The number of charaters in all these 6 strings together can come up to about 300 characters. ... and I don't know any (common valid) method to get the real text. ...
    (microsoft.public.vb.winapi)