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

If you get no help here, you should try one of the vb groups (not one of
the ones with dotnet or vbnet in their names)

jamesfreddyc wrote:
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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: VB6.0 / ADO / Parameterized Query / SQLServer 2000 -- help...
    ... If I add another string value to the listbox, ... "jamesfreddyc" wrote: ... The rest of the SProc performs a query with other ...
    (microsoft.public.data.ado)
  • Listbox And Combobox Control Buffer Overflow
    ... = Listbox And Combobox Control Buffer Overflow ... As past history has shown us, Windows has many buffer overflow resulting ... Pointer to the null-terminated string that ... After sending a message with a large pathname utilman will cause an ...
    (Bugtraq)
  • [Full-Disclosure] Listbox And Combobox Control Buffer Overflow
    ... = Listbox And Combobox Control Buffer Overflow ... As past history has shown us, Windows has many buffer overflow resulting ... Pointer to the null-terminated string that ... After sending a message with a large pathname utilman will cause an ...
    (Full-Disclosure)
  • Re: Listbox to delimited text file
    ... suggestion. ... Listbox, although that would be okay, I just want the user to fill up maybe ... Public Function PutFile(sFn As String, ... Open sFn For Binary Access Write Lock Read Write As fh ...
    (microsoft.public.vb.controls)
  • Re: ListBox question
    ... split by the second string. ... element of myArray like the the SplitTest subroutine did to set each textbox ... Dim myLong As Long ... And your listbox is named ListBox1 ...
    (microsoft.public.word.vba.general)